Horton

Re: PG 18 release notes draft committed

Peter Geoghegan <pg@bowt.ie>

View thread
From: Peter Geoghegan <pg@bowt.ie>
To: Bruce Momjian <bruce@momjian.us>
Cc: PostgreSQL-development <pgsql-hackers@lists.postgresql.org>
Date: 2025-09-17T15:46:58Z
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. doc PG 18 relnotes: add AFTER trigger user change item

  2. doc PG 18 relnotes: modify async I/O item for other improvements

  3. doc PG 18 relnotes: split apart log_connections item

  4. doc PG 18 relnotes: move ANALYZE item,split ANALYZE/EXPLAIN item

  5. doc PG 18 relnotes: clarify multiplication item

  6. doc PG 18 relnotes: add removal details to MD5 item

  7. doc PG 18 relnotes: fix markup

  8. doc PG 18 relnotes: clarify btree skip-scan item

  9. doc PG 18 relnotes: update to current

  10. doc PG 18 relnotes: adjust CREATE SUBSCRIPTION attribution

  11. doc PG 18 relnotes: clarify btree skip scan item

  12. doc PG 18 relnotes: mv. hash joins and GROUP BY item to General

  13. Add support for runtime arguments in injection points

  14. doc PG 18 relnotes: fix missing parens for crc32c()

  15. PG 18 relnotes: adjust RETURNING new/old item

  16. doc PG 18 relnotes: adjust pg_log_backend_memory_contexts()

  17. doc PG 18 relnotes: add pg_log_backend_memory_contexts() mention

  18. doc PG 18 relnotes: adjust pgbench per-script reporting item

  19. doc PG 18 relnotes: mention GROUP SET fixes

  20. doc PG 18 relnotes: adjust partition planning item

  21. doc PG 18 relnotes: small adjustments regarding options

  22. doc PG 18 relnotes: move partition locking item to General Perf

  23. doc PG 18 relnotes: adjust partition items

  24. doc PG 18 relnotes: reword OAuth item

  25. doc PG 18 relnotes: add mention of pg_stat_reset_backend_stats()

  26. doc PG 18 relnotes: adjust hash item

  27. doc PG 18 relnotes: split partition optimizer item into two

  28. doc PG 18 relnotes: adjust COPY and REJECT_LIMIT items

  29. doc PG 18 relnotes: move and clarify constraint items

  30. doc PG 18 relnotes: add commit for cancel key and protocol neg.

  31. doc PG 18 relnotes: fix libpq wording

  32. doc PG 18 relnotes: add GROUP BY column elimination item

  33. doc PG 18 relnotes: move protocol version item to "server"

  34. doc PG 18 relnotes: adjust libpq trace & potocol version items

  35. doc PG 18 relnotes: reword and reorder items

  36. doc: Fix memory context level in pg_log_backend_memory_contexts() example.

  37. Make levels 1-based in pg_log_backend_memory_contexts()

  38. Introduce file_copy_method setting.

  39. libpq: Handle NegotiateProtocolVersion message differently

  40. Add timingsafe_bcmp(), for constant-time memory comparison

  41. Optimization for lower(), upper(), casefold() functions.

  42. Fix ALTER SUBSCRIPTION ... SET PUBLICATION ... command.

  43. Add connection establishment duration logging

  44. Modularize log_connections output

  45. Ensure that AFTER triggers run as the instigating user.

  46. Detect redundant GROUP BY columns using UNIQUE indexes

  47. Move cancel key generation to after forking the backend

On Wed, Sep 17, 2025 at 6:58 AM Bruce Momjian <bruce@momjian.us> wrote:
> > In light of all this, I propose that we change the current feature
> > description, from:
> >
> > "This allows multi-column btree indexes to be used by queries that
> > only equality-reference the second or later indexed columns."
> >
> > to:
> >
> > "This allows multi-column btree indexes to be used by queries that
> > only specify conditions on the second or later indexed columns."
>
> I think your new text is inaccurate because you state here that the
> first column can be referenced and skip-scan still be used:

It's true that that's also possible. Skip scan will always "fill-in"
*any* index column that lacks a = condition that comes from the query
itself, by adding a skip array for that column during nbtree
preprocessing (note that a column that has a < or a > condition counts
as not having an = condition, and so will get its own skip array). A
skip array is an artificial "= ANY(<all possible column values>)"
condition/constraint that makes up for the fact that the user's query
did not provide us with a conventional = condition/constraint. nbtree
preprocessing always does this to the extent required to enable
repositioning the scan using *all* of the keys that actually come from
the query itself.

So we'll reliably read only those B-Tree leaf pages that might have
matching index tuples, no matter the details -- even with absurdly
complicated/unrealistic index scans. Note that adding a skip array
doesn't necessarily make us skip (skipping only happens when the scan
finds that it actually allows us to skip over something, otherwise we
just step to the next page on the leaf level as before). Skipping is
purely a runtime choice (adding skip arrays merely enables us to make
this choice, but it still has to make sense for us to do it).

To be clear, I don't think that the release notes need to go into
anything like this level of detail. My point is just that there are
just about no limitations. This isn't useful because we actually
expect users to have really complicated index scans, with varied
operators/conditions on only a subset of index columns (though some
may); it's useful because users don't have to think about it at all.

> I think we need to highlight new cases where indexes can now be used by
> skip scan:
>
> *  missing early indexed column references
> *  early indexed column references that use non-equality comparisons and
>    the comparisons are not sufficiently restrictive on their own to use
>    the index.

There isn't that much difference between these 2 things, from an
implementation point of view. They're both cases where a query/scan
initially lacks an = condition on a column where it'd be useful to
have one, so as to be able to use at least one later condition that
comes from the query to reposition the scan.

If there is an inequality condition on a column that gets a skip array
(because it didn't initially have a = condition), then the skip array
will only generate values that satisfy the inequalities. For example,
a skip array on "x" will only generate the values 1 and 2 given a qual
such as "WHERE x BETWEEN 1 AND 2 AND y = 66". The runtime behavior is
very similar to what we'd get in Postgres 17 for a qual "WHERE x =
ANY('{1, 2}') AND y = 66".

> And, at the same time, not fall into the trip of saying the later column
> references must be equality-only.

Right. This later condition could be a simple scalar equality or
inequality condition, it could be an IN() condition, or a row compare
inequality -- it could be anything.

> Here is what I have so far:
>
>         Previously, multi-column btree indexes could only be used by
>         queries that either equality-referenced the first indexed column
>         or referenced that column in a restrictive-enough way for index
>         lookups to be efficient.

It's hard to pin this down, since it has always been possible to make
*some* use of index columns, even in these tricky cases (we at least
didn't have to go to the heap to eliminate non-matching rows). Skip
scan just makes the B-Tree code find the most efficient way of
*navigating through the index*, by skipping over groups of
provably-irrelevant index leaf pages. This makes it much more likely
that the optimizer will actually choose such an index scan in the
first place.

I suggest the following alternative, which has the merit of being a
bit less verbose:

"Skip scan allows B-Tree index scans to find the most efficient way of
navigating through a multicolumn index when one or more of its columns
initially lacks a = condition and comes before a column that is
directly used by the query. Such an index scan can now be broken down
into multiple "index searches" by generating an implementation level =
condition on any underspecified columns. This allows the scan to skip
over irrelevant sections of the index, though only when the generated
= condition is on a column that has relatively few distinct values."

Note that "index searches" is a term that now appears in EXPLAIN ANALYZE output.

> I apologize for people who got the wrong impression of the feature and I
> hope they see this email thread or the updated text.

It's tough to get this right. There are many ways in which this could
be unnecessarily confusing, or misleading.

-- 
Peter Geoghegan