Index access method not receiving an orderbys ScanKey

From: Chris Cleveland <ccleveland(at)dieselpoint(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Index access method not receiving an orderbys ScanKey
Date: 2024-04-25 23:36:58
Message-ID: CABSN6Ve=u426YFyjJDyfDFAjxvYtLYG4q5eWi-pXixy_zDthZw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Can anyone tell me why my index access method isn't seeing an order_by
ScanKey when there is a query with an ORDER BY clause that uses an operator
that the access method supports?

(Apologies for the volume of code below. I just don't know what is
important. This is all in Rust.)

CREATE OPERATOR pg_catalog.<===> (
FUNCTION = rdb.userquery_match,
LEFTARG = record,
RIGHTARG = rdb.userqueryspec
);

CREATE OPERATOR pg_catalog.<<=>> (
FUNCTION = rdb.test_sort_match,
LEFTARG = record,
RIGHTARG = rdb.TestSort
);

CREATE OPERATOR CLASS rdb_ops DEFAULT FOR TYPE record USING rdb AS
OPERATOR 1 pg_catalog.<===> (record, rdb.userqueryspec),
OPERATOR 2 pg_catalog.<<=>> (record, rdb.testsort) FOR ORDER BY
pg_catalog.float_ops;

#[derive(Serialize, Deserialize, PostgresType, Debug)]
pub struct TestSort {
foo: f32,
}

#[pg_extern(
sql = "CREATE FUNCTION rdb.test_sort_match(rec record, testsort
rdb.TestSort) RETURNS bool IMMUTABLE STRICT PARALLEL SAFE LANGUAGE c AS
'MODULE_PATHNAME', 'dummysort_match_wrapper';",
requires = [DummySortSpec]
)]
fn test_sort_match(_fcinfo: pg_sys::FunctionCallInfo, testsort: TestSort)
-> f32 {
testsort.foo + 2.0
}

#[pg_extern(immutable, strict, parallel_safe)]
fn get_testsort(foo: f32) -> TestSort {
TestSort { foo }
}

Here's the query:

SELECT title
FROM products
WHERE products <===> rdb.userquery('teddy')
ORDER BY products <<=>> rdb.get_testsort(5.0);

The amhander gets and uses the WHERE clause just fine. The problem is that
ambeginscan() and amrescan() both get a norderbys parameter equal to 0.

Doing an EXPLAIN on the query above yields this:

Sort (cost=1000027.67..1000028.92 rows=500 width=28)
Sort Key: ((products.* <<=>> '{"foo":5.0}'::rdb.testsort))
-> Index Scan using product_idx on products (cost=0.00..1000005.26
rows=500 width=28)
Index Cond: (products.* <===> '{"query_str":"teddy", /* snip */
}'::rdb.userqueryspec)

So, the sort isn't getting passed to the index scan. Weirdly, the
test_sort_match() method never gets invoked, so I'm not sure how the system
thinks it's actually doing the sort.

The fact that the operators work on a RECORD type does not seem to be
significant. If I swap it for a TEXT data type then I get the same behavior.

I'm sure I'm missing something small.

Here's the amhandler definition:

#[pg_extern(sql = "
CREATE OR REPLACE FUNCTION amhandler(internal) RETURNS index_am_handler
PARALLEL SAFE IMMUTABLE STRICT LANGUAGE c AS 'MODULE_PATHNAME',
'@FUNCTION_NAME@';
CREATE ACCESS METHOD rdb TYPE INDEX HANDLER amhandler;
")]
fn amhandler(_fcinfo: pg_sys::FunctionCallInfo) ->
PgBox<pg_sys::IndexAmRoutine> {
let mut amroutine =
unsafe {
PgBox::<pg_sys::IndexAmRoutine>::alloc_node(pg_sys::NodeTag::T_IndexAmRoutine)
};

amroutine.amstrategies = OPERATOR_COUNT;
amroutine.amsupport = PROC_COUNT;
amroutine.amoptsprocnum = OPTIONS_PROC;
amroutine.amcanorder = false;
amroutine.amcanorderbyop = true;
amroutine.amcanbackward = false;
amroutine.amcanunique = false;
amroutine.amcanmulticol = true;
amroutine.amoptionalkey = true;
amroutine.amsearcharray = false;
amroutine.amsearchnulls = false;
amroutine.amstorage = false;
amroutine.amclusterable = false;
amroutine.ampredlocks = false;
amroutine.amcanparallel = false;
amroutine.amcaninclude = true;
amroutine.amusemaintenanceworkmem = false;
amroutine.amparallelvacuumoptions = 0;
amroutine.amkeytype = pg_sys::InvalidOid;

/* interface functions */
amroutine.ambuild = Some(build::ambuild);
amroutine.ambuildempty = Some(build::ambuildempty);
amroutine.aminsert = Some(insert::aminsert);
amroutine.ambulkdelete = Some(delete::ambulkdelete);
amroutine.amvacuumcleanup = Some(delete::amvacuumcleanup);
amroutine.amcanreturn = Some(scan::amcanreturn); /* test if can return
a particular col in index-only scan */
amroutine.amcostestimate = Some(amcostestimate);
amroutine.amoptions = Some(index_options::amoptions);

amroutine.amproperty = None;
amroutine.ambuildphasename = None;
amroutine.amvalidate = Some(amvalidate);
// amroutine.amadjustmembers = None; omit so we can compile earlier
versions

amroutine.ambeginscan = Some(scan::ambeginscan);
amroutine.amrescan = Some(scan::amrescan);
amroutine.amgettuple = Some(scan::amgettuple);
amroutine.amgetbitmap = None; // Some(scan::ambitmapscan);
amroutine.amendscan = Some(scan::amendscan);

amroutine.ammarkpos = None;
amroutine.amrestrpos = None;

/* interface functions to support parallel index scans */
amroutine.amestimateparallelscan = None;
amroutine.aminitparallelscan = None;
amroutine.amparallelrescan = None;

amroutine.into_pg_boxed()
}

--
Chris Cleveland
312-339-2677 mobile

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2024-04-25 23:57:19 Re: BitmapHeapScan streaming read user and prelim refactoring
Previous Message Tom Lane 2024-04-25 23:28:45 Re: BitmapHeapScan streaming read user and prelim refactoring