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
