Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan

Peter Geoghegan <pg@bowt.ie>

From: Peter Geoghegan <pg@bowt.ie>

To: Matthias van de Meent <boekewurm+postgres@gmail.com>

Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>, Tomas Vondra <tomas.vondra@enterprisedb.com>, Jeff Davis <pgsql@j-davis.com>, benoit <benoit@hopsandfork.com>

Date: 2023-09-29T00:32:19Z

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 →

Move nbtree preprocessing into new .c file.
- 597b1ffbf123 18.0 landed
Fix nbtree lookahead overflow bug.
- 09a8407dbfd8 18.0 landed
- 6749d4aabe74 17.0 landed
Remove unneeded nbtree array preprocessing assert.
- 480bc6e3ed3a 17.0 landed
Don't try to fix eliminated nbtree array scan keys.
- f22e17f76cf5 17.0 landed
Remove redundant nbtree preprocessing assertions.
- 3b08133cd13c 17.0 landed
Avoid extra lookups with nbtree array inequalities.
- 473411fc5115 17.0 landed
Enhance nbtree ScalarArrayOp execution.
- 5bf748b86bc6 17.0 landed
Improvements and fixes for e0b1ee17dc
- 7e6fb5da41d8 17.0 cited
Skip checking of scan keys required for directional scan in B-tree
- e0b1ee17dc3a 17.0 cited
Fix btmarkpos/btrestrpos array key wraparound bug.
- 714780dcddf0 17.0 cited
Add nbtree high key "continuescan" optimization.
- 29b64d1de7c7 12.0 cited
Consider secondary factors during nbtree splits.
- fab250243387 12.0 cited
Make heap TID a tiebreaker nbtree index column.
- dd299df8189b 12.0 cited
Fix planning of btree index scans using ScalarArrayOpExpr quals.
- 807a40c551dd 9.3.0 cited
Fix btree stop-at-nulls logic properly.
- 882368e854b6 9.2.0 cited
Teach btree to handle ScalarArrayOpExpr quals natively.
- 9e8da0f75731 9.2.0 cited

Attachments

v3-0001-Enhance-nbtree-ScalarArrayOp-execution.patch (application/x-patch) patch v3-0001

On Sun, Sep 17, 2023 at 4:47 PM Peter Geoghegan <pg@bowt.ie> wrote:
> Attached is v2, which makes all array key advancement take place using
> the "next index tuple" approach (using binary searches to find array
> keys using index tuple values).

Attached is v3, which fixes bitrot caused by today's bugfix commit 714780dc.

No notable changes here compared to v2.

--
Peter Geoghegan