Horton

Re: PG 18 release notes draft committed

Bruce Momjian <bruce@momjian.us>

View thread
From: Bruce Momjian <bruce@momjian.us>
To: Peter Geoghegan <pg@bowt.ie>
Cc: PostgreSQL-development <pgsql-hackers@lists.postgresql.org>
Date: 2025-09-17T10:58:33Z
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 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.