Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tuBef-004lSY-KU for pgsql-performance@arkaria.postgresql.org; Mon, 17 Mar 2025 14:41:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tuBed-00DjSw-TG for pgsql-performance@arkaria.postgresql.org; Mon, 17 Mar 2025 14:41:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tuBed-00DjSo-Gi for pgsql-performance@lists.postgresql.org; Mon, 17 Mar 2025 14:41:23 +0000 Received: from mail-pj1-x1030.google.com ([2607:f8b0:4864:20::1030]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tuBeZ-003O3z-1L for pgsql-performance@postgresql.org; Mon, 17 Mar 2025 14:41:23 +0000 Received: by mail-pj1-x1030.google.com with SMTP id 98e67ed59e1d1-30185d00446so1281472a91.0 for ; Mon, 17 Mar 2025 07:41:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1742222479; x=1742827279; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=FT2+fPruO7M1G8lfgU/rJ7tEKpW3DqynveXUoAP/d1w=; b=neDPNE4lBFA9mSXQMff1V9C74o8JsNyIQXNmaOmLBFwaw9bWI1+5CWr3wsBpYW3uI+ EK3txdzFhKCqp2TiA3RgSu7nTYSquDWxSqDnGq8HgdHXlr08oRzMU/iIMiObrLKaOqVz LQvTlsz2a4huiuwL5EKw7H8qxW9ptZhpXyF+rDEOIx4MybPfx5xaZXwFdZWcawnKlJLE TCSdZaiKSPS4dRl2yYOwFfYCMqZP2O9TNmHbXmdE7dzW5ivdVYYsIbfybft5TgNXLuZA xnRZfvNqdAhYnIXAGiSEktdPRStjwmYRYlQL1uIlh0JSNQnp65r7sS4hWz7+/82Oxikp gmkA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1742222479; x=1742827279; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=FT2+fPruO7M1G8lfgU/rJ7tEKpW3DqynveXUoAP/d1w=; b=XjPeYOrs89a5sImHoMXCPAISLFLQn2jvUg1TgaAORNk6D2c2cHhdndPX5CNZj7h/5t WccWng4j93q2rx3rszDdjg0pDuk66lmchomqstHXqSCABfIGL2Y5JbCNPlFzYeROdNEY jo8ux1Qw6neliDYr444NDwQ9ULOC6ADd3YX3k1ZfwKp/hJk+J0LHGn/q8+kvvSWxDj4X gb0/wGSM0PdmspzSjWGydPitvNRE3nD7UnpZ/v3owMu91TwPY32a9JPGdJVRfM4ICpAC 8KEWzar8Z4O8bkFG6dN0Ud9MgB0NbbGzGhB3tOHKw9zUTCEyHEAVK33MclzA+eGDvKeG XRTQ== X-Gm-Message-State: AOJu0YxGyeA0NrU4fkv6nxIKYNYiTgrScIzRmVDpaY0rJKUtmRtBo0xo aoPTPT3CtqkgrEEBzjitF00xEokn4ETzneG9uZGTfC3Va1I9IUgcZ6J377SQzC10Fr7bh2eOhwq ClphEGWNgr8EJFomuehsZZxfD1Tj+gAY1 X-Gm-Gg: ASbGncuH25xP7JJJQEv5SYMy8X9bo1Oc8lfOpzLevOv6CPbkb4SjZzgCOX4p3/N/plp ePLPXpXgARmTgefG6DlpY0r7LfhSc29M09kwm6L+j3hOzyvyzJ+JkjrKxz+wk0Djt1rd1O6pLPE oEJPpYzCJgtdXAs4yuikthuUUIvXao X-Google-Smtp-Source: AGHT+IEeOUBSj2sNFP4AMH5N4ngzAJUFw7s9a3XDS/3KzYp8HiN61kXqqNVX1eZxYmQ8nQdDQWzT35u3b9zZMH+X+P0= X-Received: by 2002:a17:90b:53ce:b0:2ff:4a8d:74f9 with SMTP id 98e67ed59e1d1-3015214a7d9mr15735991a91.10.1742222478535; Mon, 17 Mar 2025 07:41:18 -0700 (PDT) MIME-Version: 1.0 From: kimaidou Date: Mon, 17 Mar 2025 15:41:07 +0100 X-Gm-Features: AQ5f1JoCOy_IdUTRj5biWxMQFXNFcp8R5lA1kyMsvwVmbYsy1T_ywXzGjhIVBIk Message-ID: Subject: Bad perf when using DECLARE CURSOR on big table To: postgres performance list Content-Type: multipart/alternative; boundary="000000000000f3f61e06308ac716" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f3f61e06308ac716 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 =3D=3D=3D=3D=3D https://explain.dalibo.com/plan/042bc4dc2449adfe 96ms DECLARE CURSOR for the same query =3D=3D=3D=3D=3D 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=C3=ABl --000000000000f3f61e06308ac716 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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 b= y 2000.
<= br>
I hav= e seen that this DECLARE has bad perf compared to a simple SQL query :

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

DECLARE CURSOR for the same query
=3D=3D=3D=3D=3D
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=C3=ABl

--000000000000f3f61e06308ac716--