On Monday, October 5th, 2026 at 12:47 PM, Greg Burd <[email protected]> wrote: > > On Monday, October 5th, 2026 at 10:39 AM, Matthias van de Meent > <[email protected]> wrote: > > > On Thu, 1 Oct 2026, 21:10 Greg Burd, <[email protected]> wrote: > > > > > > Hello hackers, > > > > > > I'd like to see core improve the full text search index provided in > > > core at some point which these days means including BM25 (and likely > > > other) algorithm(s). To learn about that space I've been working on > > > an extension [1]. > > > > > > While working on that I stumbled onto something that seemed odd and > > > worth investing time in. It turns out that an index scan followed by > > > an order by doesn't do what I'd expected, it doesn't use the > > > orderbyvals returned by the index when ordering results. > > > > > > SELECT id, body <=> 'search terms' AS score > > > FROM docs > > > ORDER BY body <=> 'search terms' > > > LIMIT 10; > > > > > > In that query the executor will get the ranked set of results from > > > the index scan and ignore it and then turn around and re-compute a > > > rank value for each document in isolation. > > > > > > BM25 ranking is over a corpus, not a single document so this was a > > > head scratcher because to me this looked like doing double the work > > > only to get incorrect results. > > > > If it's getting incorrect results then that's probably an issue in > > your extension's definitions. > > > > > I did some digging [2] and I find [3] I'm not alone [4]. And that I > > > agreed with Chris Cleveland's [5] statement: > > > > > > > It would also be nice if the orderbyval could be made available in > > > > the projection. That way we could report the score() in the > > > > result set. > > > > > > Only to find later that Tom explained that this was a more subtle > > > issue that it seemed to be on the surface. > > > > > > > An index ordering operator is an optimization that the planner may > > > > or may not choose to employ. If you've designed your code on the > > > > assumption that that's the only possible plan, it will break for > > > > any but the most trivial queries. [6] > > > > > > Reading into Tom's objection what I take away is that one should not > > > make the operator's own value index-dependent, so that the same SQL > > > expression means different things under different plans, stop me if > > > I'm wrong. > > > > Correct. An index's presence (or being selected in planning) must not > > affect queries' row-data output, unless the query has sufficiently > > incomplete ordering, or the query introspects the catalogs that > > describe that index's existence (but that is besides the point). If > > your operator relies on the whole table's corpus for weights, then > > you'll have to figure out how the operator gets access to the whole > > table's corpus' weights even outside index scan plans -- post-filter > > ordering is a valid plan approach and sometimes the best plan option > > available, even when there is a bm25/vector/etc. index available. > > I get your point and I led with the wrong motivation. The BM25 case is > an extension problem: the operator has to find the corpus statistics > itself so that Seq Scan + Sort and Index Scan agree. I shouldn't have > framed any part of this as a way to display an index-derived number that > the operator can't reproduce. > > Please read the patch as the thing you agreed with at the end of your > mail: avoiding the double (triple, with xs_recheckorderby) evaluation > of an ORDER BY operator whose value the index already computed. > > > > I propose that when the executor chooses an Index Scan and the AM > > > reports its value as exact (`xs_recheckorderby = false`), the > > > executor may substitute that value for a target-list occurrence of > > > the identical ORDER BY expression. It may not substitute anything > > > else. > > > > What "value" are you talking about? Table and Index scans produce > > their results in TupleSlots, and these don't contain the projected > > order-by's expression. > > IndexScanDesc.xs_orderbyvals / xs_orderbynulls, which the AM fills via > index_store_float8_orderby_distances() and which IndexNextWithReorder > already uses to sort. The patch copies those (or, for a rechecked row, > the executor's recomputed values) into a one-row virtual slot and lets > the projection read them through an INNER_VAR instead of re-evaluating > the expression. > > > Also note that it is not strictly necessary for indexes to know the > > exact output of an order-by expression to know if the ordering it > > outputs needs rechecking: E.g., a query that orders by distance from > > a timestamp, but stores only the week number of the timestamps, can > > still give you a correct ordering that doesn't need rechecking as long > > as the index only has one value per week. The index won't necessarily > > be able to produce the exact ordering value of the output tuple, but > > it will produce data that is ordered in the same order as the > > expression would be. > > > > > - For an AM whose value differs from the operator's (pgvector's > > > squared L2, every BM25 AM), the substitution is observable. > > > > Then isn't every one of those indexes inherently breaking the AM contract? > > > > > The patch mirrors what Index Only Scan already does for indexed > > > columns. With IoS, setrefs rewrites target-list expressions that > > > match an index column into `INDEX_VAR` references. Here, it rewrites > > > target-list expressions that match an ORDER BY expression into > > > references to the scan's ORDER BY values. > > > > It's not quite mirroring the IOS definitions, because it's missing an > > equivalent for amroutine->amcanreturn(). See also my comment above > > about truncated knowledge giving correct results in certain > > situations: I think it's useful to be able to decouple "can return > > tuples in the correct order, with fallback" from "will provide > > complete and correct orderable values for IOS output, with fallback". > > Yes. This is the real hole in v1/v2. gist.sgml says that with > recheck = false the distance function's values need only order the > same as the operator, so xs_recheckorderby = false promises ordering, > not value, and a per-AM bool can't express a per-opclass property. > > > > Okay, that's more than enough to see if anyone agrees that this is an > > > important things to fix and an approach that is making good > > > trade-offs or not. > > > > I do agree that we should try fixing the double (or even triple, when > > xs_recheckorderby) evaluation of order-by-operator expressions. > > For v3 I intend to replace amorderbyvalsexact with a callback shaped > like amcanreturn: amcanreturnorderby(Relation, int attno), NULL by > default. GiST would answer true for an opclass whose distance > function is documented as returning the ordering operator's result > (in core, the point opclass; box/circle/polygon set recheck and never > reach this), false otherwise; SP-GiST likewise for the quad/kd point > opclasses. plancat.c already collects canreturn[] per column, and > indexorderbycols tells setrefs which column each ORDER BY key is on, > so the rewrite is gated per key, and an opclass that orders by a > truncated value is never asked to display one. That also decouples > the two things you listed: "can order correctly, with fallback" stays > xs_recheckorderby; "will return the exact ordering value" becomes the > new callback. > > Does that shape look right to you before I write it?
Attached v3 does what I described: amorderbyvalsexact is gone, replaced by an optional amcanreturnorderby(Relation, attno) callback alongside amcanreturn. plancat.c collects it per column into IndexOptInfo.canreturnorderby[], create_indexscan_plan() turns that into one bool per ORDER BY key via indexorderbycols, and setrefs.c gates each key separately. GiST says yes only for point_ops and box_ops (their leaf distance is the operator's own code; I verified 0 differences over 80k rows with boxes of real area), SP-GiST for quad_point_ops, kd_point_ops and box_ops. Circle and polygon report lower bounds and are left alone. I added circle_ops as a negative control in the regression test, and polygon.out reverts to master. I've not yet measured performance or memory difference due to this change, net should be better but I should quantify that at some point. Oops, in the last email I'd claimed the box opclass was lossy; it isn't (gist_box_distance's recheck is under #ifdef NOT_USED). The rechecked path is still covered by the existing polygon tests, now without a rewrite. indexam.sgml documents the callback and says explicitly that xs_recheckorderby = false promises ordering, not value, which was your point. best. -greg > > Kind regards, > > > > Matthias van de Meent > > Databricks (https://www.databricks.com) > > > > > > PS. > > > > > [5] > > > https://pg.ddx.io/m/pgsql-hackers/cabsn6vflk5msedsr8wpemh_h3xnyr2xs5njk8+uozp0bvxw...@mail.gmail.com/ > > > > Was this mail not available in the project's own archives? > > It is; sorry, that was my own mirror site https://pg.ddx.io and I have a > Tampermonkey script that converts links to it and this time I copy/pasted > the link and didn't normalize it to the one more likely to be available > in the future. > > [5] > https://postgr.es/m/cabsn6vflk5msedsr8wpemh_h3xnyr2xs5njk8+uozp0bvxw...@mail.gmail.com > > PS: Have you tried the search at https://pg.ddx.io/search or used the MCP > server at https://pg.ddx.io/mcp ?
From 70dbcbd803fac18642acee766b4a475b010a74ff Mon Sep 17 00:00:00 2001 From: Greg Burd <[email protected]> Date: Thu, 1 Oct 2026 10:19:05 -0400 Subject: [PATCH v3] Let an ordering index scan hand its ORDER BY values to the target list An Index Scan that orders by an operator (ORDER BY col <-> const) computes each returned row's ORDER BY value inside the index AM and reports it in xs_orderbyvals. If the same expression also appears in the target list, the executor then evaluates it a second time in the scan's projection (a third time if the AM asked for a recheck). For an expensive distance operator that is wasted work. The AM contract only requires a non-rechecked distance to sort the same way as the operator's result, not to equal it, so the executor cannot substitute it blindly. Add an optional IndexAmRoutine callback, amcanreturnorderby(Relation, attno), shaped like amcanreturn: it asks the AM whether the given column's opclass reports, for a non-rechecked leaf entry, exactly the value the ordering operator would return. plancat.c collects the answer per column alongside canreturn[], and create_indexscan_plan() carries one bool per ORDER BY key into the IndexScan via indexorderbycols. For each qualifying key, setrefs.c replaces a top-level target list expression that is equal() to that indexorderbyorig expression with Var(INNER_VAR, i), and nodeIndexscan.c stores the returned row's ORDER BY values in a virtual slot it installs as the projection's inner tuple (a scan node has no inner plan, so INNER_VAR is otherwise unused there). The values stored are the AM's for a row it reported exact, the recomputed ones for a row it asked to recheck, a reorder queue entry's copy for a queued row, and freshly recomputed ones for an EvalPlanQual test tuple. Only float4/float8 ORDER BY expressions qualify, as those are the only types index_store_float8_orderby_distances() can deliver. GiST answers yes for the core point and box opclasses, whose leaf distance is computed by the operator's own code; SP-GiST likewise for quad_point_ops, kd_point_ops and box_ops. The polygon and circle opclasses report lower bounds and are not affected, nor is any other AM or any out-of-core opclass, which all default to no. EXPLAIN VERBOSE deparses the INNER_VAR back to the ORDER BY expression and reports "Order By Values Used: N". Above such a scan, an upper node's reference to the value now prints with one more pair of parentheses, the same way any reference to a computed scan column does (box expected output). Author: Greg Burd <[email protected]> Discussion: https://postgr.es/m/8s5lT8erXzBMugXJQ6Wginbp_gc2B4hmCYhF0Q0GpnuK87eCAOcKs5K31AWCwraCNFIQt42wwkow_MPPubHg485MWv-zDBY4NL_JX9yJEEg=@burd.me --- doc/src/sgml/indexam.sgml | 31 +++++ src/backend/access/gist/gist.c | 1 + src/backend/access/gist/gistget.c | 25 ++++ src/backend/access/index/indexam.c | 22 ++++ src/backend/access/spgist/spgscan.c | 24 ++++ src/backend/access/spgist/spgutils.c | 1 + src/backend/commands/explain.c | 4 + src/backend/executor/nodeIndexscan.c | 91 +++++++++++++- src/backend/optimizer/plan/createplan.c | 14 +++ src/backend/optimizer/plan/setrefs.c | 60 +++++++++ src/backend/optimizer/util/plancat.c | 3 + src/backend/utils/adt/ruleutils.c | 31 +++++ src/include/access/amapi.h | 8 ++ src/include/access/genam.h | 1 + src/include/access/gist_private.h | 1 + src/include/access/spgist.h | 1 + src/include/nodes/execnodes.h | 7 ++ src/include/nodes/pathnodes.h | 5 +- src/include/nodes/plannodes.h | 11 ++ src/test/regress/expected/box.out | 12 +- src/test/regress/expected/gist.out | 160 ++++++++++++++++++++++++ src/test/regress/sql/gist.sql | 80 ++++++++++++ src/tools/pgindent/typedefs.list | 1 + 23 files changed, 585 insertions(+), 9 deletions(-) diff --git a/doc/src/sgml/indexam.sgml b/doc/src/sgml/indexam.sgml index f48da318530..0f1ccd9ffa3 100644 --- a/doc/src/sgml/indexam.sgml +++ b/doc/src/sgml/indexam.sgml @@ -157,6 +157,7 @@ typedef struct IndexAmRoutine ambulkdelete_function ambulkdelete; amvacuumcleanup_function amvacuumcleanup; amcanreturn_function amcanreturn; /* can be NULL */ + amcanreturnorderby_function amcanreturnorderby; /* can be NULL */ amcostestimate_function amcostestimate; amgettreeheight_function amgettreeheight; /* can be NULL */ amoptions_function amoptions; @@ -481,6 +482,25 @@ amcanreturn (Relation indexRelation, int attno); <para> <programlisting> +bool +amcanreturnorderby (Relation indexRelation, int attno); +</programlisting> + Check whether, for an ordering scan on the given column, the values the + access method reports in <literal>scan->xs_orderbyvals</literal> with + <literal>xs_recheckorderby</literal> false are exactly the values the + ordering operator would return for the heap tuple. If so, the executor + may return them for a target list entry that is the same expression as + the <literal>ORDER BY</literal> key, instead of evaluating the operator + again. The function must answer for the operator class bound to the + column, not for the access method as a whole: an operator class whose + distances merely sort like the operator's result, or depend on anything + other than the indexed value, must answer false. If the + <structfield>amcanreturnorderby</structfield> field is set to NULL, no + substitution is made. + </para> + + <para> +<programlisting> void amcostestimate (PlannerInfo *root, IndexPath *path, @@ -495,6 +515,7 @@ amcostestimate (PlannerInfo *root, in <xref linkend="index-cost-estimation"/>, below. </para> + <para> <programlisting> int @@ -989,6 +1010,16 @@ amtranslatecmptype (CompareType cmptype, Oid opfamily, Oid opcintype); of that form can be passed to <function>amrescan</function> as described previously. </para> + <para> + Such an access method returns each entry's ordering values in + <literal>scan->xs_orderbyvals</literal>, and sets + <literal>scan->xs_recheckorderby</literal> to true when those values are + only lower bounds that the executor must recompute from the heap + tuple. When <literal>xs_recheckorderby</literal> is false the values + need only sort in the same order as the ordering operator's results; + they need not equal them. See <function>amcanreturnorderby</function> + below for how an access method can promise that they do. + </para> </listitem> </itemizedlist> </para> diff --git a/src/backend/access/gist/gist.c b/src/backend/access/gist/gist.c index 8565e225be7..f6c4592a1f5 100644 --- a/src/backend/access/gist/gist.c +++ b/src/backend/access/gist/gist.c @@ -93,6 +93,7 @@ gisthandler(PG_FUNCTION_ARGS) .ambulkdelete = gistbulkdelete, .amvacuumcleanup = gistvacuumcleanup, .amcanreturn = gistcanreturn, + .amcanreturnorderby = gistcanreturnorderby, .amcostestimate = gistcostestimate, .amgettreeheight = NULL, .amoptions = gistoptions, diff --git a/src/backend/access/gist/gistget.c b/src/backend/access/gist/gistget.c index 111c310f9df..f71578a3aed 100644 --- a/src/backend/access/gist/gistget.c +++ b/src/backend/access/gist/gistget.c @@ -23,6 +23,7 @@ #include "pgstat.h" #include "storage/predicate.h" #include "utils/float.h" +#include "utils/fmgroids.h" #include "utils/memutils.h" #include "utils/rel.h" @@ -813,3 +814,27 @@ gistcanreturn(Relation index, int attno) else return false; } + +/* + * Are the distances the given column's opclass reports for a leaf entry, + * when it does not set *recheck, the ordering operator's own result? + * + * The GiST contract only requires a non-rechecked distance to *sort* like + * the operator's result, so we cannot assume this of an arbitrary opclass. + * The core point and box opclasses compute the leaf distance with the same + * code as the operator, so the executor may return their values in place of + * re-evaluating the operator. Other opclasses say no, as before. + */ +bool +gistcanreturnorderby(Relation index, int attno) +{ + Oid distproc; + + if (attno > IndexRelationGetNumberOfKeyAttributes(index)) + return false; + + distproc = index_getprocid(index, attno, GIST_DISTANCE_PROC); + + return (distproc == F_GIST_POINT_DISTANCE || + distproc == F_GIST_BOX_DISTANCE); +} diff --git a/src/backend/access/index/indexam.c b/src/backend/access/index/indexam.c index db811056991..0ff85161d3d 100644 --- a/src/backend/access/index/indexam.c +++ b/src/backend/access/index/indexam.c @@ -716,6 +716,28 @@ index_can_return(Relation indexRelation, int attno) return indexRelation->rd_indam->amcanreturn(indexRelation, attno); } +/* ---------------- + * index_can_return_orderby + * + * For an ordering scan on the given column, are the distances the AM + * reports with xs_recheckorderby = false the ordering operator's own + * result, so that the executor may return them in place of + * re-evaluating the operator? (xs_recheckorderby = false alone only + * promises that they sort the same way.) + * ---------------- + */ +bool +index_can_return_orderby(Relation indexRelation, int attno) +{ + RELATION_CHECKS; + + /* amcanreturnorderby is optional; assume false if not provided by AM */ + if (indexRelation->rd_indam->amcanreturnorderby == NULL) + return false; + + return indexRelation->rd_indam->amcanreturnorderby(indexRelation, attno); +} + /* ---------------- * index_getprocid * diff --git a/src/backend/access/spgist/spgscan.c b/src/backend/access/spgist/spgscan.c index 2cc5f06f5d7..3896985c5fa 100644 --- a/src/backend/access/spgist/spgscan.c +++ b/src/backend/access/spgist/spgscan.c @@ -24,6 +24,7 @@ #include "storage/bufmgr.h" #include "utils/datum.h" #include "utils/float.h" +#include "utils/fmgroids.h" #include "utils/lsyscache.h" #include "utils/memutils.h" #include "utils/rel.h" @@ -1088,3 +1089,26 @@ spgcanreturn(Relation index, int attno) return cache->config.canReturnData; } + +/* + * Are the distances the opclass reports for a leaf tuple, when it does not + * set recheckDistances, the ordering operator's own result? + * + * As for GiST, the contract only requires them to sort like the operator's + * result. The core point and box opclasses compute leaf distances with the + * operator's own code; polygon sets recheckDistances and never qualifies. + */ +bool +spgcanreturnorderby(Relation index, int attno) +{ + Oid leafproc; + + if (attno != 1) + return false; + + leafproc = index_getprocid(index, attno, SPGIST_LEAF_CONSISTENT_PROC); + + return (leafproc == F_SPG_QUAD_LEAF_CONSISTENT || + (leafproc == F_SPG_BOX_QUAD_LEAF_CONSISTENT && + index->rd_opcintype[0] == BOXOID)); +} diff --git a/src/backend/access/spgist/spgutils.c b/src/backend/access/spgist/spgutils.c index f2ee333f60d..d07e197a594 100644 --- a/src/backend/access/spgist/spgutils.c +++ b/src/backend/access/spgist/spgutils.c @@ -78,6 +78,7 @@ spghandler(PG_FUNCTION_ARGS) .ambulkdelete = spgbulkdelete, .amvacuumcleanup = spgvacuumcleanup, .amcanreturn = spgcanreturn, + .amcanreturnorderby = spgcanreturnorderby, .amcostestimate = spgcostestimate, .amgettreeheight = NULL, .amoptions = spgoptions, diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 96f2f0e2e74..924f70af2e4 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -1974,6 +1974,10 @@ ExplainNode(PlanState *planstate, List *ancestors, planstate, es); show_scan_qual(((IndexScan *) plan)->indexorderbyorig, "Order By", planstate, ancestors, es); + if (es->verbose && ((IndexScan *) plan)->indexorderbytlist > 0) + ExplainPropertyInteger("Order By Values Used", NULL, + ((IndexScan *) plan)->indexorderbytlist, + es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); if (plan->qual) show_instrumentation_count("Rows Removed by Filter", 1, diff --git a/src/backend/executor/nodeIndexscan.c b/src/backend/executor/nodeIndexscan.c index 129d005f187..f1dbf7c1b25 100644 --- a/src/backend/executor/nodeIndexscan.c +++ b/src/backend/executor/nodeIndexscan.c @@ -163,6 +163,33 @@ IndexNext(IndexScanState *node) return ExecClearTuple(slot); } +/* + * Hand the ORDER BY values of the row about to be returned to the projection, + * when the targetlist asks for them (indexorderbytlist > 0, set by setrefs.c + * only for keys whose opclass passes amcanreturnorderby). vals and nulls + * are what the caller established as this row's ordering values: the AM's + * when it reported them exact, the recomputed ones when it asked for a + * recheck, or a reorder queue entry's copy. Never a lossy AM's lower bound. + */ +static inline void +IndexStoreOrderByValues(IndexScanState *node, const Datum *vals, + const bool *nulls) +{ + TupleTableSlot *oslot = node->iss_OrderBySlot; + int i; + + if (oslot == NULL) + return; + ExecClearTuple(oslot); + for (i = 0; i < node->iss_NumOrderByKeys; i++) + { + oslot->tts_values[i] = vals[i]; + oslot->tts_isnull[i] = nulls[i]; + } + ExecStoreVirtualTuple(oslot); + node->ss.ps.ps_ExprContext->ecxt_innertuple = oslot; +} + /* ---------------------------------------------------------------- * IndexNextWithReorder * @@ -251,6 +278,9 @@ IndexNextWithReorder(IndexScanState *node) { HeapTuple tuple; + /* before the pop frees the entry's copies */ + IndexStoreOrderByValues(node, topmost->orderbyvals, + topmost->orderbynulls); tuple = reorderqueue_pop(node); /* Pass 'true', as the tuple in the queue is a palloc'd copy */ @@ -352,6 +382,7 @@ next_indextuple: else { /* Can return this tuple immediately. */ + IndexStoreOrderByValues(node, lastfetched_vals, lastfetched_nulls); return slot; } } @@ -404,7 +435,22 @@ IndexRecheck(IndexScanState *node, TupleTableSlot *slot) /* Does the tuple meet the indexqual condition? */ econtext->ecxt_scantuple = slot; - return ExecQualAndReset(node->indexqualorig, econtext); + if (!ExecQualAndReset(node->indexqualorig, econtext)) + return false; + + /* + * The EPQ test tuple never went through IndexNext, so the ORDER BY values + * the targetlist reads (if any) are not those of this row. Recompute + * them from the tuple, as we would for a rechecked row. + */ + if (node->iss_OrderBySlot != NULL) + { + EvalOrderByExpressions(node, econtext); + IndexStoreOrderByValues(node, node->iss_OrderByValues, + node->iss_OrderByNulls); + } + + return true; } @@ -954,7 +1000,24 @@ ExecInitIndexScan(IndexScan *node, EState *estate, int eflags) * Initialize result type and projection. */ ExecInitResultTypeTL(&indexstate->ss.ps); - ExecAssignScanProjectionInfo(&indexstate->ss); + + /* + * A targetlist that reads the ORDER BY values (INNER_VAR Vars, see + * setrefs.c) is never the scan tuple's physical layout, so it always + * projects; ExecConditionalAssignProjectionInfo would assert on the + * INNER_VAR while testing whether it can skip projection. + */ + if (node->indexorderbytlist > 0) + { + ExecInitResultSlot(&indexstate->ss.ps, &TTSOpsVirtual); + indexstate->ss.ps.resultops = &TTSOpsVirtual; + indexstate->ss.ps.resultopsfixed = true; + indexstate->ss.ps.resultopsset = true; + ExecAssignProjectionInfo(&indexstate->ss.ps, + indexstate->ss.ss_ScanTupleSlot->tts_tupleDescriptor); + } + else + ExecAssignScanProjectionInfo(&indexstate->ss); /* * initialize child expressions @@ -1024,6 +1087,30 @@ ExecInitIndexScan(IndexScan *node, EState *estate, int eflags) NULL, /* no ArrayKeys */ NULL); + /* + * A targetlist that reads the ORDER BY values needs a slot to read them + * from, typed as the ORDER BY expressions (setrefs.c built it so). + */ + if (node->indexorderbytlist > 0) + { + TupleDesc odesc; + ListCell *lc; + AttrNumber attno = 0; + + odesc = CreateTemplateTupleDesc(list_length(node->indexorderbyorig)); + foreach(lc, node->indexorderbyorig) + { + Node *expr = (Node *) lfirst(lc); + + attno++; + TupleDescInitEntry(odesc, attno, NULL, exprType(expr), + exprTypmod(expr), 0); + } + TupleDescFinalize(odesc); + indexstate->iss_OrderBySlot = + ExecAllocTableSlot(&estate->es_tupleTable, odesc, &TTSOpsVirtual, 0); + } + /* Initialize sort support, if we need to re-check ORDER BY exprs */ if (indexstate->iss_NumOrderByKeys > 0) { diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 2f8a08ad78e..c0f22b278c3 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -3033,6 +3033,20 @@ create_indexscan_plan(PlannerInfo *root, indexorderbyops, best_path->indexscandir); + if (!indexonly && indexorderbys != NIL) + { + List *exact = NIL; + ListCell *lc; + + foreach(lc, best_path->indexorderbycols) + { + int indexcol = lfirst_int(lc); + + exact = lappend_int(exact, indexinfo->canreturnorderby[indexcol]); + } + ((IndexScan *) scan_plan)->indexorderbyexact = exact; + } + copy_generic_path_info(&scan_plan->plan, &best_path->path); return scan_plan; diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index 8a641402a96..16a4f59a518 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -134,6 +134,7 @@ static bool flatten_rtes_walker(Node *node, flatten_rtes_walker_context *cxt); static void add_rte_to_flat_rtable(PlannerGlobal *glob, List *rteperminfos, RangeTblEntry *rte); static Plan *set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset); +static void replace_orderby_tlist_refs(IndexScan *splan); static Plan *set_indexonlyscan_references(PlannerInfo *root, IndexOnlyScan *plan, int rtoffset); @@ -701,6 +702,8 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) splan->indexorderbyorig = fix_scan_list(root, splan->indexorderbyorig, rtoffset, NUM_EXEC_QUAL(plan)); + /* after both are fixed, so equal() compares like with like */ + replace_orderby_tlist_refs(splan); } break; case T_IndexOnlyScan: @@ -1360,6 +1363,63 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) return plan; } +/* + * replace_orderby_tlist_refs + * Let an ordering IndexScan hand its ORDER BY values to its targetlist. + * + * For each ORDER BY key whose index column's opclass promises that the + * distances it reports for an exact (non-rechecked) row are the ordering + * operator's own result (indexorderbyexact[i], from amcanreturnorderby), + * replace each top-level targetlist expression that is equal() to the i'th + * indexorderbyorig expression with Var(INNER_VAR, i + 1). nodeIndexscan.c + * then fills a virtual slot with the ORDER BY values of every returned row -- + * the AM's for an exact row, the recomputed ones for a rechecked row -- and + * installs it as the projection's inner tuple. A scan node has no inner + * plan, so INNER_VAR is otherwise unused here. + * + * Only float8/float4 expressions qualify: those are the only types + * index_store_float8_orderby_distances() can deliver. + */ +static void +replace_orderby_tlist_refs(IndexScan *splan) +{ + ListCell *lc; + int nrepl = 0; + + if (splan->indexorderbyexact == NIL) + return; + Assert(list_length(splan->indexorderbyexact) == + list_length(splan->indexorderbyorig)); + + foreach(lc, splan->scan.plan.targetlist) + { + TargetEntry *tle = lfirst_node(TargetEntry, lc); + ListCell *lo, + *le; + int i = 0; + + forboth(lo, splan->indexorderbyorig, le, splan->indexorderbyexact) + { + Expr *orig = (Expr *) lfirst(lo); + Oid typ = exprType((Node *) orig); + + i++; + if (lfirst_int(le) && + (typ == FLOAT8OID || typ == FLOAT4OID) && + equal(tle->expr, orig)) + { + tle->expr = (Expr *) makeVar(INNER_VAR, i, typ, + exprTypmod((Node *) orig), + exprCollation((Node *) orig), + 0); + nrepl++; + break; + } + } + } + splan->indexorderbytlist = nrepl; +} + /* * set_indexonlyscan_references * Do set_plan_references processing on an IndexOnlyScan diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index d067368cfc1..c6a2d0e9f6e 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -280,11 +280,14 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent, info->opfamily = palloc_array(Oid, nkeycolumns); info->opcintype = palloc_array(Oid, nkeycolumns); info->canreturn = palloc_array(bool, ncolumns); + info->canreturnorderby = palloc_array(bool, ncolumns); for (i = 0; i < ncolumns; i++) { info->indexkeys[i] = index->indkey.values[i]; info->canreturn[i] = index_can_return(indexRelation, i + 1); + info->canreturnorderby[i] = + index_can_return_orderby(indexRelation, i + 1); } for (i = 0; i < nkeycolumns; i++) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 5e8e1683db0..dca0d43e9ce 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -180,6 +180,8 @@ typedef struct List *outer_tlist; /* referent for OUTER_VAR Vars */ List *inner_tlist; /* referent for INNER_VAR Vars */ List *index_tlist; /* referent for INDEX_VAR Vars */ + List *orderby_tlist; /* IndexScan: referent for INNER_VAR Vars that + * stand for its ORDER BY values */ /* Special namespace representing a function signature: */ char *funcname; int numargs; @@ -5227,6 +5229,23 @@ set_deparse_plan(deparse_namespace *dpns, Plan *plan) dpns->index_tlist = ((CustomScan *) plan)->custom_scan_tlist; else dpns->index_tlist = NIL; + + /* + * An IndexScan whose targetlist reads its own ORDER BY values (see + * replace_orderby_tlist_refs in setrefs.c) uses INNER_VAR Vars numbered + * by ORDER BY key; they deparse as the ORDER BY expressions. + */ + dpns->orderby_tlist = NIL; + if (IsA(plan, IndexScan) && ((IndexScan *) plan)->indexorderbytlist > 0) + { + ListCell *lc; + AttrNumber i = 0; + + foreach(lc, ((IndexScan *) plan)->indexorderbyorig) + dpns->orderby_tlist = lappend(dpns->orderby_tlist, + makeTargetEntry((Expr *) lfirst(lc), + ++i, NULL, false)); + } } /* @@ -7988,6 +8007,18 @@ resolve_special_varno(Node *node, deparse_context *context, context->appendparents = save_appendparents; return; } + else if (var->varno == INNER_VAR && dpns->orderby_tlist) + { + TargetEntry *tle; + + tle = get_tle_by_resno(dpns->orderby_tlist, var->varattno); + if (!tle) + elog(ERROR, "bogus varattno for ORDER BY value var: %d", var->varattno); + + resolve_special_varno((Node *) tle->expr, context, + callback, callback_arg); + return; + } else if (var->varno == INNER_VAR && dpns->inner_tlist) { TargetEntry *tle; diff --git a/src/include/access/amapi.h b/src/include/access/amapi.h index 79240333530..ba535175d61 100644 --- a/src/include/access/amapi.h +++ b/src/include/access/amapi.h @@ -144,6 +144,13 @@ typedef IndexBulkDeleteResult *(*amvacuumcleanup_function) (IndexVacuumInfo *inf /* can indexscan return IndexTuples? */ typedef bool (*amcanreturn_function) (Relation indexRelation, int attno); +/* + * Are the ORDER BY distances this column's opclass reports for an exact + * (xs_recheckorderby = false) leaf entry the ordering operator's own result? + */ +typedef bool (*amcanreturnorderby_function) (Relation indexRelation, + int attno); + /* estimate cost of an indexscan */ typedef void (*amcostestimate_function) (PlannerInfo *root, IndexPath *path, @@ -300,6 +307,7 @@ typedef struct IndexAmRoutine ambulkdelete_function ambulkdelete; amvacuumcleanup_function amvacuumcleanup; amcanreturn_function amcanreturn; /* can be NULL */ + amcanreturnorderby_function amcanreturnorderby; /* can be NULL */ amcostestimate_function amcostestimate; amgettreeheight_function amgettreeheight; /* can be NULL */ amoptions_function amoptions; diff --git a/src/include/access/genam.h b/src/include/access/genam.h index 1bfdc1ef6b1..717e04d3f2b 100644 --- a/src/include/access/genam.h +++ b/src/include/access/genam.h @@ -194,6 +194,7 @@ extern IndexBulkDeleteResult *index_bulk_delete(IndexVacuumInfo *info, extern IndexBulkDeleteResult *index_vacuum_cleanup(IndexVacuumInfo *info, IndexBulkDeleteResult *istat); extern bool index_can_return(Relation indexRelation, int attno); +extern bool index_can_return_orderby(Relation indexRelation, int attno); extern RegProcedure index_getprocid(Relation irel, AttrNumber attnum, uint16 procnum); extern FmgrInfo *index_getprocinfo(Relation irel, AttrNumber attnum, diff --git a/src/include/access/gist_private.h b/src/include/access/gist_private.h index 39fdd27f5b9..e0fbdb043af 100644 --- a/src/include/access/gist_private.h +++ b/src/include/access/gist_private.h @@ -462,6 +462,7 @@ extern void gistkillitems(IndexScanDesc scan); extern bool gistgettuple(IndexScanDesc scan, ScanDirection dir); extern int64 gistgetbitmap(IndexScanDesc scan, TIDBitmap *tbm); extern bool gistcanreturn(Relation index, int attno); +extern bool gistcanreturnorderby(Relation index, int attno); /* gistvalidate.c */ extern bool gistvalidate(Oid opclassoid); diff --git a/src/include/access/spgist.h b/src/include/access/spgist.h index 083d93f8ffd..cd5ddbc63d2 100644 --- a/src/include/access/spgist.h +++ b/src/include/access/spgist.h @@ -210,6 +210,7 @@ extern void spgrescan(IndexScanDesc scan, ScanKey scankey, int nscankeys, extern int64 spggetbitmap(IndexScanDesc scan, TIDBitmap *tbm); extern bool spggettuple(IndexScanDesc scan, ScanDirection dir); extern bool spgcanreturn(Relation index, int attno); +extern bool spgcanreturnorderby(Relation index, int attno); /* spgvacuum.c */ extern IndexBulkDeleteResult *spgbulkdelete(IndexVacuumInfo *info, diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 91bb0bd2e13..07a404e665b 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -1751,6 +1751,13 @@ typedef struct IndexScanState bool *iss_OrderByTypByVals; int16 *iss_OrderByTypLens; Size iss_PscanLen; + + /* + * When the plan's targetlist reads the ORDER BY values (indexorderbytlist + * > 0), the values of the row being returned, as a virtual slot installed + * as the projection's inner tuple. + */ + TupleTableSlot *iss_OrderBySlot; } IndexScanState; /* ---------------- diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index 1c6d1fe3d04..e7d447e0889 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -1312,7 +1312,8 @@ typedef struct RelAggInfo * IndexOptInfo * Per-index information for planning/optimization * - * indexkeys[] and canreturn[] each have ncolumns entries. + * indexkeys[], canreturn[] and canreturnorderby[] each have ncolumns + * entries. * * indexcollations[], opfamily[], and opcintype[] each have nkeycolumns * entries. These don't contain any information about INCLUDE columns. @@ -1395,6 +1396,8 @@ typedef struct IndexOptInfo bytea **opclassoptions pg_node_attr(read_write_ignore); /* which index cols can be returned in an index-only scan? */ bool *canreturn pg_node_attr(array_size(ncolumns)); + /* which index cols' exact ORDER BY distances are the operator's result? */ + bool *canreturnorderby pg_node_attr(array_size(ncolumns)); /* OID of the access method (in pg_am) */ Oid relam; diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 09a1ec73180..5d462f284fb 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -616,6 +616,17 @@ typedef struct IndexScan List *indexorderbyops; /* forward or backward or don't care */ ScanDirection indexorderdir; + + /* + * Per indexorderbyorig entry: may setrefs.c replace a target-list + * occurrence of that expression with an INNER_VAR reference to the scan's + * ORDER BY value? True when the index column's opclass reports exact + * distances that are the operator's result (amcanreturnorderby). + * indexorderbytlist counts the replacements actually made (0 if none), + * for EXPLAIN. + */ + List *indexorderbyexact; + int indexorderbytlist; } IndexScan; /* ---------------- diff --git a/src/test/regress/expected/box.out b/src/test/regress/expected/box.out index 10760870ce7..60d92a26e1e 100644 --- a/src/test/regress/expected/box.out +++ b/src/test/regress/expected/box.out @@ -594,10 +594,10 @@ SET enable_bitmapscan = OFF; EXPLAIN (COSTS OFF) SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id FROM quad_box_tbl; - QUERY PLAN --------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------- WindowAgg - Window: w1 AS (ORDER BY (b <-> '(123,456)'::point) ROWS UNBOUNDED PRECEDING) + Window: w1 AS (ORDER BY ((b <-> '(123,456)'::point)) ROWS UNBOUNDED PRECEDING) -> Index Scan using quad_box_tbl_idx on quad_box_tbl Order By: (b <-> '(123,456)'::point) (4 rows) @@ -617,10 +617,10 @@ WHERE seq.id IS NULL OR idx.id IS NULL; EXPLAIN (COSTS OFF) SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))'; - QUERY PLAN --------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------- WindowAgg - Window: w1 AS (ORDER BY (b <-> '(123,456)'::point) ROWS UNBOUNDED PRECEDING) + Window: w1 AS (ORDER BY ((b <-> '(123,456)'::point)) ROWS UNBOUNDED PRECEDING) -> Index Scan using quad_box_tbl_idx on quad_box_tbl Index Cond: (b <@ '(500,600),(200,300)'::box) Order By: (b <-> '(123,456)'::point) diff --git a/src/test/regress/expected/gist.out b/src/test/regress/expected/gist.out index ac79f94aa80..42396fdf5ef 100644 --- a/src/test/regress/expected/gist.out +++ b/src/test/regress/expected/gist.out @@ -387,6 +387,166 @@ select p from gist_tbl order by circle(p,1) <-> point(0,0) limit 1; select p from gist_tbl order by circle(p,1) <-> point(0,0) limit 1; ERROR: lossy distance functions are not supported in index-only scans +drop index gist_tbl_multi_index; +-- Test that an ordering Index Scan returns the AM's ORDER BY values to a +-- targetlist entry equal to the ORDER BY expression, instead of evaluating +-- the expression again, for an opclass that passes amcanreturnorderby. +set enable_indexonlyscan = off; +-- An expression index over a counting function proves the operator is not +-- re-evaluated: the count stays at zero for the rows returned. +create sequence gist_cnt_seq; +create function gist_cnt_pt(point) returns point language plpgsql immutable + as $$ begin perform nextval('public.gist_cnt_seq'); return $1; end $$; +create index gist_tbl_cnt_index on gist_tbl using gist (gist_cnt_pt(p)); +explain (verbose, costs off) +select p, gist_cnt_pt(p) <-> point(0.201, 0.201) as dist + from gist_tbl order by gist_cnt_pt(p) <-> point(0.201, 0.201) limit 3; + QUERY PLAN +------------------------------------------------------------------------ + Limit + Output: p, ((gist_cnt_pt(p) <-> '(0.201,0.201)'::point)) + -> Index Scan using gist_tbl_cnt_index on public.gist_tbl + Output: p, ((gist_cnt_pt(p) <-> '(0.201,0.201)'::point)) + Order By: (gist_cnt_pt(gist_tbl.p) <-> '(0.201,0.201)'::point) + Order By Values Used: 1 +(6 rows) + +select setval('gist_cnt_seq', 1, false); + setval +-------- + 1 +(1 row) + +select p, gist_cnt_pt(p) <-> point(0.201, 0.201) as dist + from gist_tbl order by gist_cnt_pt(p) <-> point(0.201, 0.201) limit 3; + p | dist +-------------+----------------------- + (0.2,0.2) | 0.0014142135623730963 + (0.25,0.25) | 0.06929646455628163 + (0.15,0.15) | 0.07212489168102787 +(3 rows) + +select nextval('gist_cnt_seq') - 1 as calls_during_scan; + calls_during_scan +------------------- + 0 +(1 row) + +-- Every copy of the ORDER BY expression is served from the index; an +-- expression that merely contains it is not. +explain (verbose, costs off) +select gist_cnt_pt(p) <-> point(0,0) as d1, + (gist_cnt_pt(p) <-> point(0,0)) * 2 as d2, + gist_cnt_pt(p) <-> point(0,0) as d3 + from gist_tbl order by gist_cnt_pt(p) <-> point(0,0) limit 2; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------- + Limit + Output: ((gist_cnt_pt(p) <-> '(0,0)'::point)), (((gist_cnt_pt(p) <-> '(0,0)'::point) * '2'::double precision)), ((gist_cnt_pt(p) <-> '(0,0)'::point)) + -> Index Scan using gist_tbl_cnt_index on public.gist_tbl + Output: ((gist_cnt_pt(p) <-> '(0,0)'::point)), ((gist_cnt_pt(p) <-> '(0,0)'::point) * '2'::double precision), ((gist_cnt_pt(p) <-> '(0,0)'::point)) + Order By: (gist_cnt_pt(gist_tbl.p) <-> '(0,0)'::point) + Order By Values Used: 2 +(6 rows) + +drop index gist_tbl_cnt_index; +drop function gist_cnt_pt(point); +drop sequence gist_cnt_seq; +-- The box opclass computes its exact leaf distance with the operator's own +-- code, so it qualifies too. The values must equal the operator's. +create index gist_tbl_box_index on gist_tbl using gist (b); +explain (verbose, costs off) +select b, b <-> point(5.2, 5.91) as dist from gist_tbl + where b <@ box(point(5,5), point(6,6)) order by b <-> point(5.2, 5.91); + QUERY PLAN +-------------------------------------------------------- + Index Scan using gist_tbl_box_index on public.gist_tbl + Output: b, ((b <-> '(5.2,5.91)'::point)) + Index Cond: (gist_tbl.b <@ '(6,6),(5,5)'::box) + Order By: (gist_tbl.b <-> '(5.2,5.91)'::point) + Order By Values Used: 1 +(5 rows) + +-- the distance numbers are not exactly the same across platforms +set extra_float_digits = 0; +select b, b <-> point(5.2, 5.91) as dist from gist_tbl + where b <@ box(point(5,5), point(6,6)) order by b <-> point(5.2, 5.91); + b | dist +-------------------------+------------------- + (5.55,5.55),(5.55,5.55) | 0.502095608425328 + (5.6,5.6),(5.6,5.6) | 0.5060632371552 + (5.5,5.5),(5.5,5.5) | 0.508035431835221 + (5.65,5.65),(5.65,5.65) | 0.519711458407451 + (5.45,5.45),(5.45,5.45) | 0.523545604508337 + (5.7,5.7),(5.7,5.7) | 0.54230987451825 + (5.4,5.4),(5.4,5.4) | 0.547813836992093 + (5.75,5.75),(5.75,5.75) | 0.572800139664787 + (5.35,5.35),(5.35,5.35) | 0.57974132162543 + (5.8,5.8),(5.8,5.8) | 0.61 + (5.3,5.3),(5.3,5.3) | 0.618142378421024 + (5.85,5.85),(5.85,5.85) | 0.652763356814703 + (5.25,5.25),(5.25,5.25) | 0.661891229734917 + (5.9,5.9),(5.9,5.9) | 0.700071424927486 + (5.2,5.2),(5.2,5.2) | 0.71 + (5.95,5.95),(5.95,5.95) | 0.751065909225016 + (5.15,5.15),(5.15,5.15) | 0.761642960973184 + (6,6),(6,6) | 0.805046582503149 + (5.1,5.1),(5.1,5.1) | 0.816149496109629 + (5.05,5.05),(5.05,5.05) | 0.872983390449097 + (5,5),(5,5) | 0.931718841711383 +(21 rows) + +reset extra_float_digits; +select count(*) filter (where dist = b <-> point(5.2, 5.91)) as same, + count(*) as total + from (select b, b <-> point(5.2, 5.91) as dist from gist_tbl + order by b <-> point(5.2, 5.91) limit 200) ss; + same | total +------+------- + 200 | 200 +(1 row) + +-- Same, under row locking (the tuple is re-fetched by LockRows). +set extra_float_digits = 0; +select b, b <-> point(5.2, 5.91) as dist from gist_tbl + where b <@ box(point(5,5), point(6,6)) order by b <-> point(5.2, 5.91) + limit 3 for update; + b | dist +-------------------------+------------------- + (5.55,5.55),(5.55,5.55) | 0.502095608425328 + (5.6,5.6),(5.6,5.6) | 0.5060632371552 + (5.5,5.5),(5.5,5.5) | 0.508035431835221 +(3 rows) + +reset extra_float_digits; +drop index gist_tbl_box_index; +-- The circle opclass's distance is a lower bound that is always rechecked, +-- and its opclass does not pass amcanreturnorderby: the targetlist is left +-- alone and the operator is evaluated as before. +create index gist_tbl_circle_index on gist_tbl using gist (c); +explain (verbose, costs off) +select c <-> point(5.2, 5.91) as dist from gist_tbl + order by c <-> point(5.2, 5.91) limit 3; + QUERY PLAN +----------------------------------------------------------------- + Limit + Output: ((c <-> '(5.2,5.91)'::point)) + -> Index Scan using gist_tbl_circle_index on public.gist_tbl + Output: (c <-> '(5.2,5.91)'::point) + Order By: (gist_tbl.c <-> '(5.2,5.91)'::point) +(5 rows) + +select count(*) filter (where dist = c <-> point(5.2, 5.91)) as same, + count(*) as total + from (select c, c <-> point(5.2, 5.91) as dist from gist_tbl + order by c <-> point(5.2, 5.91) limit 200) ss; + same | total +------+------- + 200 | 200 +(1 row) + +drop index gist_tbl_circle_index; +reset enable_indexonlyscan; -- Test that an index-only scan deforms the tuple it reconstructs with the -- descriptor the AM formed it with, not the scan slot's descriptor. create temp table gist_ios_tupdesc (a inet, r numrange); diff --git a/src/test/regress/sql/gist.sql b/src/test/regress/sql/gist.sql index 57dcc082450..4d775f1d6e7 100644 --- a/src/test/regress/sql/gist.sql +++ b/src/test/regress/sql/gist.sql @@ -169,6 +169,86 @@ explain (verbose, costs off) select p from gist_tbl order by circle(p,1) <-> point(0,0) limit 1; select p from gist_tbl order by circle(p,1) <-> point(0,0) limit 1; +drop index gist_tbl_multi_index; + +-- Test that an ordering Index Scan returns the AM's ORDER BY values to a +-- targetlist entry equal to the ORDER BY expression, instead of evaluating +-- the expression again, for an opclass that passes amcanreturnorderby. +set enable_indexonlyscan = off; + +-- An expression index over a counting function proves the operator is not +-- re-evaluated: the count stays at zero for the rows returned. +create sequence gist_cnt_seq; +create function gist_cnt_pt(point) returns point language plpgsql immutable + as $$ begin perform nextval('public.gist_cnt_seq'); return $1; end $$; +create index gist_tbl_cnt_index on gist_tbl using gist (gist_cnt_pt(p)); + +explain (verbose, costs off) +select p, gist_cnt_pt(p) <-> point(0.201, 0.201) as dist + from gist_tbl order by gist_cnt_pt(p) <-> point(0.201, 0.201) limit 3; + +select setval('gist_cnt_seq', 1, false); +select p, gist_cnt_pt(p) <-> point(0.201, 0.201) as dist + from gist_tbl order by gist_cnt_pt(p) <-> point(0.201, 0.201) limit 3; +select nextval('gist_cnt_seq') - 1 as calls_during_scan; + +-- Every copy of the ORDER BY expression is served from the index; an +-- expression that merely contains it is not. +explain (verbose, costs off) +select gist_cnt_pt(p) <-> point(0,0) as d1, + (gist_cnt_pt(p) <-> point(0,0)) * 2 as d2, + gist_cnt_pt(p) <-> point(0,0) as d3 + from gist_tbl order by gist_cnt_pt(p) <-> point(0,0) limit 2; + +drop index gist_tbl_cnt_index; +drop function gist_cnt_pt(point); +drop sequence gist_cnt_seq; + +-- The box opclass computes its exact leaf distance with the operator's own +-- code, so it qualifies too. The values must equal the operator's. +create index gist_tbl_box_index on gist_tbl using gist (b); + +explain (verbose, costs off) +select b, b <-> point(5.2, 5.91) as dist from gist_tbl + where b <@ box(point(5,5), point(6,6)) order by b <-> point(5.2, 5.91); + +-- the distance numbers are not exactly the same across platforms +set extra_float_digits = 0; +select b, b <-> point(5.2, 5.91) as dist from gist_tbl + where b <@ box(point(5,5), point(6,6)) order by b <-> point(5.2, 5.91); +reset extra_float_digits; + +select count(*) filter (where dist = b <-> point(5.2, 5.91)) as same, + count(*) as total + from (select b, b <-> point(5.2, 5.91) as dist from gist_tbl + order by b <-> point(5.2, 5.91) limit 200) ss; + +-- Same, under row locking (the tuple is re-fetched by LockRows). +set extra_float_digits = 0; +select b, b <-> point(5.2, 5.91) as dist from gist_tbl + where b <@ box(point(5,5), point(6,6)) order by b <-> point(5.2, 5.91) + limit 3 for update; +reset extra_float_digits; + +drop index gist_tbl_box_index; + +-- The circle opclass's distance is a lower bound that is always rechecked, +-- and its opclass does not pass amcanreturnorderby: the targetlist is left +-- alone and the operator is evaluated as before. +create index gist_tbl_circle_index on gist_tbl using gist (c); + +explain (verbose, costs off) +select c <-> point(5.2, 5.91) as dist from gist_tbl + order by c <-> point(5.2, 5.91) limit 3; + +select count(*) filter (where dist = c <-> point(5.2, 5.91)) as same, + count(*) as total + from (select c, c <-> point(5.2, 5.91) as dist from gist_tbl + order by c <-> point(5.2, 5.91) limit 200) ss; + +drop index gist_tbl_circle_index; +reset enable_indexonlyscan; + -- Test that an index-only scan deforms the tuple it reconstructs with the -- descriptor the AM formed it with, not the scan slot's descriptor. create temp table gist_ios_tupdesc (a inet, r numrange); diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 656f1f60862..3544430002a 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -3569,6 +3569,7 @@ ambuildempty_function ambuildphasename_function ambulkdelete_function amcanreturn_function +amcanreturnorderby_function amcostestimate_function amendscan_function amestimateparallelscan_function -- 2.50.1
