Horton

Re: index prefetching

Tomas Vondra <tomas@vondra.me>

View thread
From: Tomas Vondra <tomas@vondra.me>
To: Nazir Bilal Yavuz <byavuz81@gmail.com>, Thomas Munro <thomas.munro@gmail.com>
Cc: Peter Geoghegan <pg@bowt.ie>, Andres Freund <andres@anarazel.de>, Robert Haas <robertmhaas@gmail.com>, Melanie Plageman <melanieplageman@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Georgios <gkokolatos@protonmail.com>, Konstantin Knizhnik <knizhnik@garret.ru>, Dilip Kumar <dilipbalaut@gmail.com>
Date: 2025-08-12T17:51:15Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. aio: io_uring: Trigger async processing for large IOs

  2. read stream: Split decision about look ahead for AIO and combining

  3. read_stream: Only increase read-ahead distance when waiting for IO

  4. read_stream: Prevent distance from decaying too quickly

  5. Reduce ExecSeqScan* code size using pg_assume()

  6. Fix rare bug in read_stream.c's split IO handling.

  7. Fix multiranges to behave more like dependent types.

  8. Add EXPLAIN (MEMORY) to report planner memory consumption

  9. Optimize nbtree backward scan boundary cases.

  10. Increment xactCompletionCount during subtransaction abort.

  11. Add nbtree Valgrind buffer lock checks.

  12. Add nbtree high key "continuescan" optimization.

  13. Reduce pinning and buffer content locking for btree scans.

  14. Teach btree to handle ScalarArrayOpExpr quals natively.

On 8/12/25 18:53, Tomas Vondra wrote:
> ...
> 
> EXPLAIN (ANALYZE, COSTS OFF)
> SELECT * FROM t WHERE a BETWEEN 16336 AND 49103 ORDER BY a ASC;
> 
>                                 QUERY PLAN
> ------------------------------------------------------------------------
>  Index Scan using idx on t
>    (actual time=0.584..433.208 rows=1048576.00 loops=1)
>    Index Cond: ((a >= 16336) AND (a <= 49103))
>    Index Searches: 1
>    Buffers: shared hit=7435 read=50872
>    I/O Timings: shared read=332.270
>  Planning:
>    Buffers: shared hit=78 read=23
>    I/O Timings: shared read=2.254
>  Planning Time: 3.364 ms
>  Execution Time: 463.516 ms
> (10 rows)
> 
> EXPLAIN (ANALYZE, COSTS OFF)
> SELECT * FROM t WHERE a BETWEEN 16336 AND 49103 ORDER BY a DESC;
> 
>                                 QUERY PLAN
> ------------------------------------------------------------------------
>  Index Scan Backward using idx on t
>    (actual time=0.566..22002.780 rows=1048576.00 loops=1)
>    Index Cond: ((a >= 16336) AND (a <= 49103))
>    Index Searches: 1
>    Buffers: shared hit=36131 read=50872
>    I/O Timings: shared read=21217.995
>  Planning:
>    Buffers: shared hit=82 read=23
>    I/O Timings: shared read=2.375
>  Planning Time: 3.478 ms
>  Execution Time: 22231.755 ms
> (10 rows)
> 
> That's a pretty massive difference ... this is on my laptop, and the
> timing changes quite a bit, but it's always a multiple of the first
> query with forward scan.
> 
> I did look into pg_aios, but there's only 8kB requests in both cases. I
> didn't have time to look closer yet.
> 

One more detail I just noticed - the DESC scan apparently needs more
buffers (~87k vs. 57k). That probably shouldn't cause such massive
regression, though.


regards

-- 
Tomas Vondra