On Tue, May 18, 2021 at 6:22 PM Franck Routier (perso) <alci@xxxxxxxxxx> wrote:
Hi,
I am using postgresql 12.7 on Ubunut as a datawarehouse, that is then
queried by QlikSense to produce business analytics.
One of my dataloaders, that runs multiple queries, sometimes takes about
3 hours to feed Qlik with the relevant records (about 10M records), but
sometimes goes crazy and times out (as Qlik stops it when it takes more
than 480 minutes).
The point is that Qlik is using a CURSOR to retrive the data. I'm not
familiar with CURSOR and postgresql documentation mainly cites functions
as use case. I don't really know how Qlik creates these cursors when
executing my queries...
I tried load_min_duration to pinpoint the problem, but only shows things
like that:
...
LOG: duration : 294774.600 ms, instruction : fetch 100000 in "SQL_CUR4"
LOG: duration : 282867.279 ms, instruction : fetch 100000 in "SQL_CUR4"
...
So I don't know exactly which of my queries is hiding behind
"SQL_CUR4"...
Is there a way to log the actual query ?
Is using a CURSOR a best practice to retrieve big datasets ? (it seems
Qlik is using it for every connection on Postgresql)
Does each FETCH re-run the query, or is the result somehow cached (on
disk ?) ?
Thanks for any insight on CURSOR and/or Qlik queries on Postgresql !
Best regards,
Franck
Have you tried setting the parameter below?
log_statement = 'all'
you will get all queries logged into log files.
Regards,
Ganesh Korde.
