2

I am a newbie in PostgreSQL. Please help me to find this out.

I have a table

company (
  id bigint primary key,
  name text,
  age integer,
  address character(50),
  salary real,
  gender character(1))

then i do simple query :

select * from company where address='texas' and salary=10000
select * from company where address='texas' and salary=15000
select * from company where address='houston' and salary=10000
select * from company where address='texas' and salary=85000
select * from company where address='norway' and salary=100

how can i get details of the executed query, example the query, variable of input WHERE and the total executing time. so i can compare each executed query to know which type of input make long running.

Thank you,

Abelisto
  • 14,826
  • 2
  • 33
  • 41
enjelyna pardede
  • 21
  • 1
  • 2
  • It is not so easy to setup it but look at the [pg_stat_statements](https://www.postgresql.org/docs/current/static/pgstatstatements.html) module. – Abelisto Nov 01 '16 at 09:12
  • thank you @Abelisto, i have used pg_stat_statement to do it, but my mentor said without that module. maybe there is another way? Thank you – enjelyna pardede Nov 01 '16 at 09:59
  • Ok, try to use [Error Reporting and Logging](https://www.postgresql.org/docs/current/static/runtime-config-logging.html), look at the parameters `log_statement` (Sets the type of statements logged) `log_duration` (Logs the duration of each completed SQL statement), `log_min_duration_statement` (Sets the minimum execution time above which statements will be logged) and so on. – Abelisto Nov 01 '16 at 11:42
  • thank you for helping me @Abelisto – enjelyna pardede Nov 02 '16 at 01:45

1 Answers1

1

As I read the question it sounds like you are looking for the query execution plan and execution details, correct?

If so, you want to look at the docs[1] for EXPLAIN and familiarize yourself with them.

explain with no options provides the query plan. It does not actually run the query.

explain analyze gives you expected vs actual timings (note it does not detoast output data but this is usually not a problem. It actually runs the query as it will any time you have an analyze in your options.

explain analyze verbose gives you additional information.

Occasionally specifying you want buffer statistics can be helpful but that's a rare corner case. Start with those three.

Also note you can configure PostgreSQL to log slow queries and this will include parameters or input. The combination of the two are usually important to resolve these sorts of problems.

[1]https://www.postgresql.org/docs/9.3/static/sql-explain.html

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
  • Thanks a lot.. I have read about EXPLAIN. by using EXPLAIN i can see the planning on my query. But it's not my problem. I'm sorry if my question is not clear. after i do simple select like the post above, how i get the detail of each select transaction, such as query, transaction time without using pg_stat_statement?? thank you-- – enjelyna pardede Nov 01 '16 at 10:17
  • There isn't a real way to do that in PostgreSQL. It sounds to me like you are having trouble with some queries being slow. The best option is to log slow queries above a certain threshold and then use explain to explore why those are slow. I suppose you could track client-side time and do some client-side logging though. – Chris Travers Nov 01 '16 at 10:37
  • well then, thank you so much for your explanation. :) – enjelyna pardede Nov 01 '16 at 10:48