Get details executed query in Postgresql

Question

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,

It is not so easy to setup it but look at the pg_stat_statements module. — Abelisto
– Abelisto, Commented Nov 1, 2016 at 9: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
– enjelyna pardede, Commented Nov 1, 2016 at 9:59
Ok, try to use Error Reporting and Logging, 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
– Abelisto, Commented Nov 1, 2016 at 11:42

Chris Travers · Accepted Answer · 2016-11-01 08:59:51Z

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

answered Nov 1, 2016 at 8:59

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

enjelyna pardede Over a year ago

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--

Chris Travers Over a year ago

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.

enjelyna pardede Over a year ago

well then, thank you so much for your explanation. :)

Collectives™ on Stack Overflow

Get details executed query in Postgresql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related