Re: PG 18 release notes draft committed - Mailing list pgsql-hackers

On Tue, Sep 16, 2025 at 01:59:07PM -0400, Peter Geoghegan wrote:
> On Fri, May 23, 2025 at 5:03 PM Bruce Momjian <bruce@momjian.us> wrote:
> > I was able to squeeze in this detail in the attached, applied patch.
> 
> I noticed that Crunchy Data had a blog post about the skip scan, where
> the author got tripped up by the description of skip scan that current
> appears in the release notes.
> 
> See: https://www.crunchydata.com/blog/get-excited-about-postgres-18
> 
> The blog post incorrectly says "Note that this optimization only works
> for queries which use the = operator, so it will not work with
> inequalities or ranges". This is incorrect; skip scan works perfectly
> fine with inequality operators. I'm sure that this confusion arose
> because of the wording from the release notes.
> 
> Adding to the confusion, Crunchy also had a Tweet about skip scan that
> used an inequality operator (which will work correctly):
> 
> https://x.com/crunchydata/status/1965751871848468499
> 
> I'm sure that this was due to the release note description, since
> there was some discussion of it on a LinkedIn post that promoted the
> blog post.

Yes, clearly we need to fix the description we have now.  However, we
have already updated this item twice, so I think we need to be careful
to get it right this time:

    https://www.postgresql.org/message-id/aC_ccwyZj1ijlM5l%40momjian.us
    https://www.postgresql.org/message-id/aDDiuUv4Zk4IyFR2%40momjian.us

> 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:

    https://www.postgresql.org/message-id/CAH2-Wzko57%2BsT%3DFcxHHo7jnPLhh35up_5aAvogLtj_D9bATsgQ%40mail.gmail.com
    
    I think that your wording is a big improvement. I personally
    would have emphasized the absence of a "=" condition, rather than
    the presence of another condition on a later column, since there
    are cases where the first column is referenced but skip scan can
    still be used (e.g., when there one or more inequalities on the
    first column, plus a "=" condition on the second column). I can
    live with this wording, though.

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.

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

I am coming to the conclusion I am trying to be too clever here, and I
need to be more verbose.  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.  With skip scans, references to the first
    indexed btree column, or multiple early indexed columns, can be
    missing or insufficiently restrictive as long as these columns
    have low cardinality, and later indexed columns are restrictive
    enough for index lookups to be efficient.

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

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.