From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Example code Re: Singleton SELECT inside cursor loop |
Date: | 2022-10-01 07:22:22 |
Message-ID: | d0f2c884-db0f-826f-a4e2-24a5e31130d8@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I extracted the singleton into a function, and that helped me isolate the
problem.
Note how quickly it runs the first five times, but takes 780x longer the
sixth time I run it. Exiting psql and entering again causes the same
slowness the sixth time it's run.
sides=> select strans.TASK001785639(1, 507650, '2022-08-01', '2022-08-31');
task001785639
---------------
498
(1 row)
sides=> select strans.TASK001785639(1, 507650, '2022-08-01', '2022-08-31');
task001785639
---------------
498
(1 row)
sides=> \timing
Timing is on.
sides=> select strans.TASK001785639(1, 507650, '2022-08-01', '2022-08-31');
task001785639
---------------
498
(1 row)
Time: 457.325 ms
sides=> select strans.TASK001785639(1, 507650, '2022-08-01', '2022-08-31');
task001785639
---------------
498
(1 row)
Time: 459.724 ms
sides=> select strans.TASK001785639(1, 507650, '2022-08-01', '2022-08-31');
task001785639
---------------
498
(1 row)
Time: 461.155 ms
sides=> select strans.TASK001785639(1, 507650, '2022-08-01', '2022-08-31');
task001785639
---------------
498
(1 row)
Time: 358426.159 ms (05:58.426)
On 10/1/22 01:29, Ron wrote:
> AWS RDS Postgresql 12.11
>
> It runs quickly, no matter what the fetched cursor values are, as long as
> the cursor returns less than 8 rows. After that, the singleton SELECT's
> performance tanks.
>
> We've got plenty of other procedures which have a similar "Singleton
> SELECT inside a cursor" design (many even query the same table that the
> problematic query does), but only this procedure's singleton SELECT tanks
> if the cursor returns more than 8 rows.
>
> It only runs once a month, so we've only just noticed the problem.
>
> Why would that be? (We recently updated from 12.10 to 12.11, if that's
> relevant.)
>
--
Angular momentum makes the world go 'round.
Attachment | Content-Type | Size |
---|---|---|
TASK001785639_function.sql | application/sql | 2.3 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Peter J. Holzer | 2022-10-01 11:22:54 | Re: Limiting the operations that client-side code can perform upon its database backend's artifacts |
Previous Message | Ron | 2022-10-01 06:29:46 | Singleton SELECT inside cursor loop |