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.
