MIME-Version: 1.0
References: 
 <CAPLVUMDKVU7YmZwUYM=oeXypj-r42++LCqW8aaWXnwPqpSmt6g@mail.gmail.com>
In-Reply-To: 
 <CAPLVUMDKVU7YmZwUYM=oeXypj-r42++LCqW8aaWXnwPqpSmt6g@mail.gmail.com>
From: Erik Brandsberg <erik@heimdalldata.com>
Date: Fri, 22 Mar 2024 13:39:39 -0400
Message-ID: 
 <CAFcck8HEJa8-53SNWxMd_8D732skiihM4S7YEsEW63swywFosg@mail.gmail.com>
Subject: Re: Cursor with hold for select takes too long
To: Bindra Bambharoliya <bindra.bambharoliya@gmail.com>
Cc: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000009054ab0614434f33"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFcck8HEJa8-53SNWxMd_8D732skiihM4S7YEsEW63swywFosg%40mail.gmail.com>
Precedence: bulk

--0000000000009054ab0614434f33
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

What is the actual query being done, and how many rows are being returned?
Depending on the query, it could change the execution path, so this is
critical to understand.  If you are doing an order by on a large
result-set, it may be doing a huge amount of processing to get your first
100 rows.

On Fri, Mar 22, 2024 at 12:50=E2=80=AFPM Bindra Bambharoliya <
bindra.bambharoliya@gmail.com> wrote:

> Hi team,
> I am facing issues with cursor with hold for select.
>
> If I execute select quaery without cursor it takes 13 seconds,
> If execute it like
> Begin;
> Cursor "sql1" with hold for select.... ;fetch 100 to "sql1"
> It takes more than 3 minutes.
> Even sometimes it takes more than 10 minutes.
>
> What could be the problem?
> Request to help on this ASAP.
> I am facing this issue on production.
> I checked locks etc. But nothing like that.
>
>
> Thanks & Regards
> Bindra
>

--0000000000009054ab0614434f33
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">What is the actual query being done, and how many rows are=
 being returned?=C2=A0 Depending on the query, it could change the executio=
n path, so this is critical to understand.=C2=A0 If you are doing an order =
by on a large result-set, it may be doing a huge amount of processing to ge=
t your first 100 rows.</div><br><div class=3D"gmail_quote"><div dir=3D"ltr"=
 class=3D"gmail_attr">On Fri, Mar 22, 2024 at 12:50=E2=80=AFPM Bindra Bambh=
aroliya &lt;<a href=3D"mailto:bindra.bambharoliya@gmail.com">bindra.bambhar=
oliya@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pad=
ding-left:1ex"><div dir=3D"auto">Hi team,<div dir=3D"auto">I am facing issu=
es with cursor with hold for select.</div><div dir=3D"auto"><br></div><div =
dir=3D"auto">If I execute select quaery without cursor it takes 13 seconds,=
</div><div dir=3D"auto">If execute it like=C2=A0</div><div dir=3D"auto">Beg=
in;</div><div dir=3D"auto">Cursor &quot;sql1&quot; with hold for select....=
 ;fetch 100 to &quot;sql1&quot;</div><div dir=3D"auto">It takes more than 3=
 minutes.</div><div dir=3D"auto">Even sometimes it takes more than 10 minut=
es.</div><div dir=3D"auto"><br></div><div dir=3D"auto">What could be the pr=
oblem?</div><div dir=3D"auto">Request to help on this ASAP.</div><div dir=
=3D"auto">I am facing this issue on production.=C2=A0</div><div dir=3D"auto=
">I checked locks etc. But nothing like that.</div><div dir=3D"auto"><br></=
div><div dir=3D"auto"><br></div><div dir=3D"auto">Thanks=C2=A0&amp; Regards=
</div><div dir=3D"auto">Bindra</div></div>
</blockquote></div>

--0000000000009054ab0614434f33--