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

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

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

To: Peter Geoghegan <pg@bowt.ie>

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>, Alexander Korotkov <aekorotkov@gmail.com>, Heikki Linnakangas <hlinnaka@iki.fi>

Date: 2024-03-06T21:51:18Z

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

On Wed, 6 Mar 2024 at 22:46, Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
>
> On Wed, 6 Mar 2024 at 01:50, Peter Geoghegan <pg@bowt.ie> wrote:
> > At one point Heikki suggested that I just get rid of
> > BTScanOpaqueData.arrayKeyData (which has been there for as long as
> > nbtree had native support for SAOPs), and use
> > BTScanOpaqueData.keyData exclusively instead. I've finally got around
> > to doing that now.
>
> I'm not sure if it was worth the reduced churn when the changes for
> the primary optimization are already 150+kB in size; every "small"
> addition increases the context needed to review the patch, and it's
> already quite complex.

To clarify, what I mean here is that merging the changes of both the
SAOPs changes and the removal of arrayKeyData seems to increase the
patch size and increases the maximum complexity of each component
patch's review.
Separate patches may make this more reviewable, or not, but no comment
was given on why it is better to merge the changes into a single
patch.

- Matthias