Re: Any insights on Qlik Sense using CURSOR ?

[Franck Routier (perso)]

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 10 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)
>  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 10 in
> > "SQL_CUR4"
> > LOG: duration : 282867.279 ms, instruction : fetch 10 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.

Re: Any insights on Qlik Sense using CURSOR ?

[Ganesh Korde]

Hi,
On Tue, May 18, 2021 at 6:22 PM Franck Routier (perso) 
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 10 in "SQL_CUR4"
> LOG: duration : 282867.279 ms, instruction : fetch 10 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.