From: | Ganesh Korde <ganeshakorde(at)gmail(dot)com> |
---|---|
To: | "Franck Routier (perso)" <alci(at)mecadu(dot)org> |
Cc: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Any insights on Qlik Sense using CURSOR ? |
Date: | 2021-05-20 12:29:21 |
Message-ID: | CAPNyb0Udi+S2j+J7m2SR4zEpZNCE9Rurf6=pCFNLZO8vaP25gQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
On Tue, May 18, 2021 at 6:22 PM Franck Routier (perso) <alci(at)mecadu(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Franck Routier (perso) | 2021-05-20 19:33:25 | Re: Any insights on Qlik Sense using CURSOR ? |
Previous Message | Bryn Llewellyn | 2021-05-20 00:50:07 | Re: The contents of the pg_timezone_names view bring some surprises |