Thread: Bad perf when using DECLARE CURSOR on big table
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
=====
96ms
DECLARE CURSOR for the same query
=====
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
kimaidou <kimaidou@gmail.com> 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
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 <tgl@sss.pgh.pa.us> a écrit :
kimaidou <kimaidou@gmail.com> 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