Re: Let an ordering index scan hand its ORDER BY value to the target list

From: Greg Burd <greg(at)burd(dot)me>
To: Oleg Bartunov <obartunov(at)postgrespro(dot)ru>
Cc: Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Heikki Linnakangas <hlinnaka(at)iki(dot)fi>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Chris Cleveland <ccleveland(at)dieselpoint(dot)com>
Subject: Re: Let an ordering index scan hand its ORDER BY value to the target list
Date: 2026-11-17 19:53:31
Message-ID: avywarzc3SWzJW6i@werk
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

v15 attached so I can fix two correctness issues I found.

0001 computed a point-to-box distance that could come out NaN. The
rewrite takes the closest point by clamping the point to the box, then
subtracted that from the point; when a coordinate sits inside the
box's range, the clamp keeps the point's own value, and for an
infinite coordinate that made Infinity minus Infinity, which is NaN.
GiST and SP-GiST now use this function for their inner-node bounds, so
a query point with an infinite coordinate turned a lower bound into
NaN. NaN sorts after everything, so the bound stopped being a lower
bound, and a kNN scan could return rows out of order. The fix takes
the distance along each axis on its own, the way the old SP-GiST code
did: a coordinate within the box's range contributes zero, infinite or
not. box.sql now covers the infinite cases and checks that the
SP-GiST kNN scan puts a NaN-distance row last.

0004 could hand a parallel-restricted value to a worker. An
expression index can make its key, and an ORDER BY over that key,
parallel-restricted; the earlier code appended such a value to a
parallel-safe scan's target without checking. The gist test's
expression-index case hit it. It now skips any ORDER BY value that is
not parallel-safe on a rel the planner may parallelize, and the scan
recomputes it as before.

best.

-greg

Attachment Content-Type Size
v15-0003-Don-t-charge-an-ordering-index-scan-for-ORDER-BY.patch application/octet-stream 23.5 KB
v15-0004-Let-an-ordering-index-scan-below-a-join-emit-its.patch application/octet-stream 20.2 KB
v15-0005-Don-t-charge-an-index-only-scan-for-index-expres.patch application/octet-stream 8.4 KB
v15-0006-Let-an-index-only-scan-win-on-the-index-expressi.patch application/octet-stream 14.4 KB
v15-0008-Carry-index-computed-values-through-outer-joins-.patch application/octet-stream 53.9 KB
v15-0002-Let-an-ordering-index-scan-hand-its-ORDER-BY-val.patch application/octet-stream 67.8 KB
v15-0001-Compute-the-distance-between-a-point-and-a-box-e.patch application/octet-stream 25.1 KB
v15-0007-Carry-index-computed-values-up-through-joins-and.patch application/octet-stream 87.4 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Greg Burd 2026-11-17 20:02:23 Re: Tepid: selective index updates for heap relations
Previous Message Greg Burd 2026-11-17 19:36:43 Re: Expanding HOT updates for expression and partial indexes