How can I log query parameters using pg_stat_statements?

Question

I'm trying to identify the heaviest queries in my DB. In the pg_stat_statements, I found a lot of Hibernate-generated queries like:

SELECT this_.ID as ID4_1_, this_.version as version4_1_ [...] where cat1_.name in ($1, $2, $3)

Why there are parameters like $1, $2... $n instead of actual values? Is it possible to log actual values using pg_stat_statements? $n parameters aren't very useful in this case.

I'm using PostgreSQL 9.0.2

Upgrade to the latest point release, your version has a major security hole and some serious known bugs. — Craig Ringer
– Craig Ringer, Commented Sep 11, 2014 at 1:57
Unfortunately it's up to the client, I can only suggest an upgrade. — Grzegorz Olszewski
– Grzegorz Olszewski, Commented Sep 11, 2014 at 10:28

Craig Ringer · Accepted Answer · 2014-09-11 01:58:30Z

13

pg_stat_statements doesn't log query parameters. That's a large part of the point.

You can use log_statement = all in postgresql.conf if you want full parameter logging.

Another option is to use pg_stat_plans, which collects query plans rather than individual statements.

answered Sep 11, 2014 at 1:58

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

2 Comments

Simon D Over a year ago

"Large part of the point": for clarity, pg_stat_statements summarizes query performance based on normalized, non-parameterized versions of the actual queries, so each row in pg_stat_statements potentially represents multiple queries with different parameters. Also pg_stat_plans hasn't been updated since 2013 (github.com/2ndQuadrant/pg_stat_plans/issues/40), so logging or auto_explain may be better approaches.

Craig Ringer Over a year ago

Yeah, pg_stat_plans was meant to be merged into core to replace/enhance pg_stat_statements but got stuck IIRC.

Collectives™ on Stack Overflow

How can I log query parameters using pg_stat_statements?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related