Show Log of the Sql statements that QGIS generates to retrieve Data from Postgis

Question

Is there a ways to - make QGis log its generated SQL Statement or to - force POSTGIS to log all interaction with QGIS?

There is something strange happening to the WFS layer I retrieve in QGIS, since it does not select all data available in the respective database table. I need to find out, what criteria QGIS chooses in order to narrow down the select set.

Answer 1

You should be able to obtain logs from Postgresql once you configure it. I typically will set log_statement = 'all' in my postgresql.conf, even on production databases (with a separate cleanup routine to clear them out every 2 weeks). Also change the log_line_prefix to something that includes at least the process id, something like: log_line_prefix = '%t %a %u %p '.

And while you are in there, you might as well set auto explain:

shared_preload_libraries = 'auto_explain'       # (change requires restart)
auto_explain.log_min_duration = '3s'

Then you can search the logs for QGIS or the pid:

2015-08-19 12:30:40 GMT QGIS postgres 11916 LOG:  statement: DECLARE qgis_1 BINARY CURSOR FOR SELECT st_asbinary("geom",'NDR'),ctid FROM "fe"."xx_test_5" WHERE "geom" && st_makeenvelope(-10940458.55397558398544788,5799047.5
2015-08-19 12:30:40 GMT QGIS postgres 11916 LOG:  duration: 15.000 ms
2015-08-19 12:30:40 GMT QGIS postgres 11916 LOG:  statement: FETCH FORWARD 2000 FROM qgis_1
2015-08-19 12:30:40 GMT QGIS postgres 11916 LOG:  duration: 0.000 ms
2015-08-19 12:30:40 GMT QGIS postgres 11916 LOG:  statement: FETCH FORWARD 2000 FROM qgis_1
2015-08-19 12:30:40 GMT QGIS postgres 11916 LOG:  duration: 0.000 ms
2015-08-19 12:30:40 GMT QGIS postgres 11916 LOG:  statement: CLOSE qgis_1
2015-08-19 12:30:40 GMT QGIS postgres 11916 LOG:  duration: 0.000 ms
2015-08-19 12:30:40 GMT QGIS postgres 11916 LOG:  statement: COMMIT
2015-08-19 12:30:40 GMT QGIS postgres 11916 LOG:  duration: 0.000 ms