rjuju
commented
3 years ago Hi,
pg_qualstats_example_query will only save the query text as provided by the client. It's likely that the various frameworks you're using send either prepared statement and/or use extended protocol, meaning that the server never sees the constant inside the query string (apart from the true/false constant).
If I understand correctly, what you want is something that tries to "de-normalize" the query. powa-web can do that and reinject some constant using values retrieved from pg_qualstats. See e.g. https://demo-powa.anayrat.info/server/1/database/obvious/query/-8635138191820200248/overview/ in the "Predicates" tab for a basic use case (no credential required, just click connect). Note that it unfortunately has some limitation:
1) it won't work for parameters in prepared statement / extended protocol, as the position is lost during the processing. See https://github.com/powa-team/pg_qualstats/pull/12 for more details. No one answered to my bug report on the postgres mailing list. I bumped it recently hoping that it could finally be fixed in postgres 15 and ideally back patched in earlier version, but until now it was still ignored 2) it can only work for values in the WHERE/JOIN clauses, as pg_qualstats only handle those. So a query of the form "SELECT some_value FROM..." won't be handled.
There's nothing more I can do about 1), as it's broken in postgres. 2) could technically be handled, but it's outside this extension's scope and I don't think I ever had a report about it until now.
There seems to be an issue where the predicate values are not filling in when either pg_qualstats_example_query() or pg_qualstats_example_queries() is executed. What conditions must be met for the values to populate?
Example: _predicate as appears in pg_statstatements
where mem2_.mem_id=$1 and ($2 is null or compute1_.code is null or compute1_.code=$3) and ($6=$4 and $7=mempro0_.active and $8=compute1_.active and $9=mem2_.active or $10=$5 and ($11<>mempro0_.active or $12<>compute1_.active or $13<>mem2_.active))_predicate as returned from pg_qualstats_examplequery
where mem2_.mem_id=$1 and ($2 is null or compute1_.code is null or compute1_.code=$3) and (true=$4 and true=mempro0_.active and true=compute1_.active and true=mem2_.active or false=$5 and (true<>mempro0_.active or true<>compute1_.active or true<>mem2_.active))It appears that it gets the values for only 'true' or 'false'. These queries are all coming from either Hibernate / Spring JPA. It works fine if I do a query by hand instead of the application executing the queries. Is there is known issue with Hibernate/Spring JPA queries?