Example code Re: Singleton SELECT inside cursor loop

[Ron <ronljohnsonjr@xxxxxxxxx>]

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: TASK001785639_function.sql
Description: application/sql