Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
We have a Qlik Sense Server 13.21.1 installation connected to a Postgres database managed in Heroku. This Postgres database is a replica of our own company's production database.
Recently Heroku has been complaining of large amounts of temporary disk space being used by our queries. (Our persistent disk usage remains in normal ranges) In order to resolve this issue, they suggest consulting the Postgres add-in module pg_stats, which collects data on executions by "query", which is to say all info is grouped on the basis of unique query statement.
In investigating this issue via the pg_stat_statements view, I noticed that our 1st, 2nd and 5th most "expensive" queries by total temporary blocks written are the following:
These appear to be related to Postgres' "Cursor" functionality, which to my knowledge there is no internal PostgreSQL function which automatically translates simple SELECT statements into Cursors inside the execution engine, which seems to imply that my Postgres instance is receiving these commands explicitly from some external source.
While I can't conclusively know that nothing else is issuing these statements, I can tell that Qlik is by virtue of testing one of our apps: when I set off a reload task, the counter for the number of calls for each of these statements in pg_stat_statements starts ticking up by approximately 1/200th of the number of rows ingested by Qlik, and then stops ticking up once the reload task is complete in Qlik. Sometimes by using pg_stat_activity when the reload task is kicked off, I can catch the instantiation of the actual query as "SQL_CUR4" etc, but it drops off that view almost instantaneously to be replaced by the "fetch 200 in" statements.
This behavior is making it very difficult to evaluate which of the queries being executed by Qlik is responsible for the large amount of disk space used: for other tools in our system, they always register the actual SQL statement being executed and I can therefore identify the specific statements that I need to reform. Looking at these three categories all I know is that a huge portion of our temp disk usage is "from Qlik", and I have no where to turn to pick out from the dozens of queries being executed in Qlik which ones might be the culprit, so I'm stuck essentially going through every single app, pulling the SQL out of it and running it locally with EXPLAIN ANALYZE to determine it's likely temp disk usage.
I also fear, but have no method of verifying as I can't tell which queries are being represented here, that this process of committing the query and fetching out of its result set is actually part of the problem: it's conceivable to me that queries which do not normally require temp disk space to execute may require such disk space if the
My explicit questions for the community:
Please let me know if you spot any other possible errors in my thinking or if I can further clarify anything!
Update for interested readers: I can now answer a few of these questions: