public inbox for [email protected]  

help / color / mirror / Atom feed

Bad perf when using DECLARE CURSOR on big table
3+ messages / 2 participants
[nested] [flat]

* Bad perf when using DECLARE CURSOR on big table
@ 2025-03-17 14:41  kimaidou <[email protected]>
  0 siblings, 1 reply; 3+ messages in thread

From: kimaidou @ 2025-03-17 14:41 UTC (permalink / raw)
  To: pgsql-performance

Hi list !

I have a simple but big spatial table with approx 93 000 000 lines.

I use QGIS, the open-source GIS software to display this data. To fetch the
polygons to draw on QGIS map, QGIS launch a first DECLARE CURSOR query,
then fetch data 2000 by 2000.

I have seen that this DECLARE has bad perf compared to a simple SQL query :

Simple SQL query
=====
https://explain.dalibo.com/plan/042bc4dc2449adfe
96ms

DECLARE CURSOR for the same query
=====
https://explain.dalibo.com/plan/bh83fc0db500a79g#
171 031 ms !!


Do you have any clue about this query plan ? Should I add some table
specific weight, stats, etc. to help the DECLARE clause to use the indexes
as done for the simple SELECT ?

Regards
Michaël


^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Bad perf when using DECLARE CURSOR on big table
@ 2025-03-17 15:22  Tom Lane <[email protected]>
  parent: kimaidou <[email protected]>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2025-03-17 15:22 UTC (permalink / raw)
  To: kimaidou <[email protected]>; +Cc: pgsql-performance

kimaidou <[email protected]> writes:
> I have seen that this DECLARE has bad perf compared to a simple SQL query :

> Simple SQL query
> =====
> https://explain.dalibo.com/plan/042bc4dc2449adfe
> 96ms

> DECLARE CURSOR for the same query
> =====
> https://explain.dalibo.com/plan/bh83fc0db500a79g#
> 171 031 ms !!

Raising cursor_tuple_fraction would probably help this case.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Bad perf when using DECLARE CURSOR on big table
@ 2025-03-18 07:48  kimaidou <[email protected]>
  parent: Tom Lane <[email protected]>
  0 siblings, 0 replies; 3+ messages in thread

From: kimaidou @ 2025-03-18 07:48 UTC (permalink / raw)
  To: Tom Lane <[email protected]>; +Cc: pgsql-performance

Indeed, increasing cursor_tuple_fraction TO 0.2 did the trick.
Thanks for the hint Tom !

Le lun. 17 mars 2025 à 16:22, Tom Lane <[email protected]> a écrit :

> kimaidou <[email protected]> writes:
> > I have seen that this DECLARE has bad perf compared to a simple SQL
> query :
>
> > Simple SQL query
> > =====
> > https://explain.dalibo.com/plan/042bc4dc2449adfe
> > 96ms
>
> > DECLARE CURSOR for the same query
> > =====
> > https://explain.dalibo.com/plan/bh83fc0db500a79g#
> > 171 031 ms !!
>
> Raising cursor_tuple_fraction would probably help this case.
>
>                         regards, tom lane
>


^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2025-03-18 07:48 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-17 14:41 Bad perf when using DECLARE CURSOR on big table kimaidou <[email protected]>
2025-03-17 15:22 ` Tom Lane <[email protected]>
2025-03-18 07:48   ` kimaidou <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox