Thread

1. Cursor with hold for select takes too long

   Bindra Bambharoliya <bindra.bambharoliya@gmail.com> — 2024-03-22T16:50:04Z

   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
   

2. Re: Cursor with hold for select takes too long

   Erik Brandsberg <erik@heimdalldata.com> — 2024-03-22T17:39:39Z

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

3. Re: Cursor with hold for select takes too long

   Bindra Bambharoliya <bindra.bambharoliya@gmail.com> — 2024-03-22T17:47:11Z

   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= b.id join
   Fact.vw_c on a.ida=vw_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 PM 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
   >>
   >
   

4. Re: Cursor with hold for select takes too long

   Tom Lane <tgl@sss.pgh.pa.us> — 2024-03-22T17:57:33Z

   Bindra Bambharoliya <bindra.bambharoliya@gmail.com> writes:
   > Query is like
   > Select a.id, sum(b.amount),
   > Count(c.am_id) ... from fact.a join fact.b on a.id= b.id join
   > Fact.vw_c on a.ida=vw_c.ida group by a.id;
   
   Is the query plan the same for cursor execution as regular?
   Compare EXPLAIN SELECT ... versus EXPLAIN DECLARE x CURSOR FOR SELECT ...
   
   (I'm not sure that WITH HOLD would make a difference, so
   maybe compare with and without that, too.)
   
   			regards, tom lane