Qlik uses the odbc driver with useDeclareFetch=1, hence the use of cursors.
By default, postgresql planner tries to optimize the execution plan for retrieving 10℅ of the records when using a cursor. This can be controlled with cursor_tuple_fraction parameter.
In my case, setting it to 1.0 (instead of the default 0.1) boosted the query from more than 1 hour (sometime going crazy to several hours) to 15 minutes.
In general, I think 1.0 is the correct value when using Qlik, as loaders will read all rows.
Franck
Le 20 mai 2021 21:33:25 GMT+02:00, "Franck Routier (perso)" <alci@xxxxxxxxxx> a écrit :
Thanks Ganesh,
this gave me the select that is slow. It effectively looks like this:
begin; declare "SQL_CUR4" cursor with hold for select ...
then a bunch of:
fetch 100000 in "SQL_CUR4"
then a commit
I also found this
article https://www.cybertec-postgresql.com/en/declare-cursor-in-postgresql-or-how-to-reduce-memory-consumption/
to be interesting as an introduction to CURSOR with Postgresql.
I'll now work on this query to try to understand the problem.
Franck
Le jeudi 20 mai 2021 à 17:59 +0530, Ganesh Korde a écrit :
Hi,
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.
-- Envoyé depuis /e/ Mail.
