Qlik Using "Cursors" with Postgres Queries by default - Is Qlik responsible & how do I turn it off?

[peterkunhardt]

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:

• fetch 200 in "SQL_CUR4"
• fetch 200 in "SQL_CUR8"
• fetch 200 in "SQL_CUR6"

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:

• Is my inference that something in Qlik is actually planning this execution this way correct, or is Qlik passing the query as I write it and Postgres is somehow deciding to push the query behind the Cursor abstraction?
• If this is a Qlik behavior, under what conditions does Qlik elect to follow this procedure? (I notice that certain queries executed by Qlik appear normally on pg_stat_statements, so it's clearly possible for Qlik to just execute the query directly as "normal")
• If this is a Qlik behavior, is it possible for me to force  Qlik to not use it? 

Please let me know if you spot any other possible errors in my thinking or if I can further clarify anything!

[peterkunhardt]

Update for interested readers: I can now answer a few of these questions:

• Qlik is definitely the entity electing to utilize the cursor system, and further it's Qlik's native postgres connector doing it; verified by installing a system DSN connection on my Qlik server and switching over a query that generates the cursor behavior when using the native connection to the DSN connection. With the system DSN Qlik does not make use of the cursor system.
• I can't figure out whether there's any situation in which Qlik does not use the cursor when using the native connection, and I now believe the records of queries I saw executed were executed by individual users from local machines and not Qlik.
• While I feared that Qlik was utilizing an unnecessary amount of temp disk space just by using this strategy, further testing on my database with pg_stat_statements confirms that the db writes the exact same number of temp_blks_written regardless of whether the query is executed behind a cursor or not. If the query generates 100 temp_blks_written natively, it will generate 100 when executed under a cursor. 
• I still don't know whether there's a way to turn this off other than staying away from the native connector.