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.
