On Tue, 23 May 2023 at 07:40, MARK CALLAGHAN <mdcallag@gmail.com> wrote:
(pg15)
> --- Q2.10k : explain analyze SELECT c FROM sbtest1 WHERE id BETWEEN 10000000 AND 10010000 order by c;
> QUERY PLAN
>
---------------------------------------------------------------------------------------------------------------------------------------
> Sort (cost=1114.85..1137.28 rows=8971 width=121) (actual time=36.561..37.429 rows=10001 loops=1)
> Sort Key: c
> Sort Method: quicksort Memory: 2025kB
> -> Index Scan using sbtest1_pkey on sbtest1 (cost=0.44..525.86 rows=8971 width=121) (actual time=0.022..3.776
rows=10001loops=1)
> Index Cond: ((id >= 10000000) AND (id <= 10010000))
> Planning Time: 0.059 ms
> Execution Time: 38.224 ms
(pg16 b1)
> QUERY PLAN
>
----------------------------------------------------------------------------------------------------------------------------------------
> Sort (cost=1259.19..1284.36 rows=10068 width=121) (actual time=14.419..15.042 rows=10001 loops=1)
> Sort Key: c
> Sort Method: quicksort Memory: 1713kB
> -> Index Scan using sbtest1_pkey on sbtest1 (cost=0.44..589.80 rows=10068 width=121) (actual time=0.023..3.473
rows=10001loops=1)
> Index Cond: ((id >= 10000000) AND (id <= 10010000))
> Planning Time: 0.049 ms
> Execution Time: 15.700 ms
It looks like the improvements here are due to qsort being faster on
v16. To get an idea of the time taken to perform the actual qsort,
you can't use the first row time vs last row time in the Sort node, as
we must (obviously) have performed the sort before outputting the
first row. I think you could get a decent idea of the time taken to
perform the qsort by subtracting the actual time for the final Index
scan row from the actual time for the Sort's first row. That's 36.561
- 3.776 = 32.785 ms for pg15's plan, and 14.419 - 3.473 = 10.946 ms
pg16 b1's
c6e0fe1f2 might have helped improve some of that performance, but I
suspect there must be something else as ~3x seems much more than I'd
expect from reducing the memory overheads. Testing versions before
and after that commit might give a better indication.
David