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

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

Hi team,
There is no order by, query returns 5341 rows.
Query is like
Select a.id, sum(b.amount),
Count(c.am_id) ... from fact.a join fact.b on a.id=3D b.id join
Fact.vw_c on a.ida=3Dvw_c.ida group by a.id;


On Fri, 22 Mar 2024, 23:09 Erik Brandsberg, <erik@heimdalldata.com> wrote:

> 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
>>
>

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

<div dir=3D"auto"><div>Hi team,</div><div dir=3D"auto">There is no order by=
, query returns 5341 rows.</div><div dir=3D"auto">Query is like=C2=A0</div>=
<div dir=3D"auto">Select <a href=3D"http://a.id">a.id</a>, sum(b.amount),</=
div><div dir=3D"auto">Count(c.am_id) ... from fact.a join fact.b on <a href=
=3D"http://a.id">a.id</a>=3D <a href=3D"http://b.id">b.id</a> join</div><di=
v dir=3D"auto">Fact.vw_c on a.ida=3Dvw_c.ida group by <a href=3D"http://a.i=
d">a.id</a>;</div><div dir=3D"auto"><div dir=3D"auto">=C2=A0</div><br><br><=
div class=3D"gmail_quote" dir=3D"auto"><div dir=3D"ltr" class=3D"gmail_attr=
">On Fri, 22 Mar 2024, 23:09 Erik Brandsberg, &lt;<a href=3D"mailto:erik@he=
imdalldata.com">erik@heimdalldata.com</a>&gt; wrote:<br></div><blockquote c=
lass=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;=
padding-left:1ex"><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 c=
hange the execution path, so this is critical to understand.=C2=A0 If you a=
re doing an order by on a large result-set, it may be doing a huge amount o=
f processing to get 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 Bambharoliya &lt;<a href=3D"mailto:bindra.bambharoliya@gmail.c=
om" target=3D"_blank" rel=3D"noreferrer">bindra.bambharoliya@gmail.com</a>&=
gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0=
px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div =
dir=3D"auto">Hi team,<div dir=3D"auto">I am facing issues with cursor with =
hold for select.</div><div dir=3D"auto"><br></div><div dir=3D"auto">If I ex=
ecute select quaery without cursor it takes 13 seconds,</div><div dir=3D"au=
to">If execute it like=C2=A0</div><div dir=3D"auto">Begin;</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 minutes.</div><div dir=3D=
"auto"><br></div><div dir=3D"auto">What could be the problem?</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>
</blockquote></div></div></div>

--0000000000008469830614436a94--