For the record, 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)" <a...@mecadu.org> 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) >> <a...@mecadu.org> 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.
