Re: POC, WIP: OR-clause support for indexes

On 07.08.2024 04:11, Alexander Korotkov wrote:
> On Mon, Aug 5, 2024 at 11:24 PM Alena Rybakina
> <a(dot)rybakina(at)postgrespro(dot)ru> wrote:
>> Ok, thank you for your work)
>>
>> I think we can leave only the two added libraries in the first patch,
>> others are superfluous.
> Thank you.
> I also have fixed some grammar issues.

Thank you)

I added some tests to test the functionality of queries using strange
operator classes, type mismatches, and a small number of joins.
At the same time, I faced an assertion when a request with an unusual
operator was processed:

EXPLAIN (COSTS OFF)
SELECT COUNT(*) FROM guid1 WHERE guid_field <>
'11111111111111111111111111111111' OR
                            guid_field <>
'3f3e3c3b-3a30-3938-3736-353433a2313e';

Coredump:

#0  __pthread_kill_implementation (no_tid=0, signo=6,
threadid=138035230913472)
    at ./nptl/pthread_kill.c:44
#1  __pthread_kill_internal (signo=6, threadid=138035230913472) at
./nptl/pthread_kill.c:78
#2  __GI___pthread_kill (threadid=138035230913472, signo=signo(at)entry=6)
at ./nptl/pthread_kill.c:89
#3  0x00007d8ad3e42476 in __GI_raise (sig=sig(at)entry=6) at
../sysdeps/posix/raise.c:26
#4  0x00007d8ad3e287f3 in __GI_abort () at ./stdlib/abort.c:79
#5  0x000060ceb55be02f in ExceptionalCondition
(conditionName=0x60ceb58058af "op_strategy != 0",
    fileName=0x60ceb58053e6 "selfuncs.c", lineNumber=6900) at assert.c:66
#6  0x000060ceb553ed48 in btcostestimate (root=0x60ceb6f9d2a8,
path=0x60ceb6fbd2a8, loop_count=1,
--Type <RET> for more, q to quit, c to continue without paging--
    indexStartupCost=0x7fff7ea15380, indexTotalCost=0x7fff7ea15388,
    indexSelectivity=0x7fff7ea15390, indexCorrelation=0x7fff7ea15398,
indexPages=0x7fff7ea153b0)
    at selfuncs.c:6900
#7  0x000060ceb521afca in cost_index (path=0x60ceb6fbd2a8,
root=0x60ceb6f9d2a8, loop_count=1,
    partial_path=false) at costsize.c:618
#8  0x000060ceb5290c99 in create_index_path (root=0x60ceb6f9d2a8,
index=0x60ceb6fbd5e8,
    indexclauses=0x60ceb6fbe4c8, indexorderbys=0x0,
indexorderbycols=0x0, pathkeys=0x0,
    indexscandir=ForwardScanDirection, indexonly=true,
required_outer=0x0, loop_count=1,
    partial_path=false) at pathnode.c:1024
--Type <RET> for more, q to quit, c to continue without paging--
#9  0x000060ceb522df4d in build_index_paths (root=0x60ceb6f9d2a8,
rel=0x60ceb70716c8, index=0x60ceb6fbd5e8,
    clauses=0x7fff7ea15790, useful_predicate=false,
scantype=ST_ANYSCAN, skip_nonnative_saop=0x7fff7ea15607)
    at indxpath.c:970
#10 0x000060ceb522d905 in get_index_paths (root=0x60ceb6f9d2a8,
rel=0x60ceb70716c8, index=0x60ceb6fbd5e8,
    clauses=0x7fff7ea15790, bitindexpaths=0x7fff7ea15678) at indxpath.c:729
#11 0x000060ceb522c846 in create_index_paths (root=0x60ceb6f9d2a8,
rel=0x60ceb70716c8) at indxpath.c:286
#12 0x000060ceb5212d29 in set_plain_rel_pathlist (root=0x60ceb6f9d2a8,
rel=0x60ceb70716c8, rte=0x60ceb6f63768)
    at allpaths.c:794
#13 0x000060ceb5212852 in set_rel_pathlist (root=0x60ceb6f9d2a8,
rel=0x60ceb70716c8, rti=1, rte=0x60ceb6f63768)
    at allpaths.c:499
#14 0x000060ceb521248c in set_base_rel_pathlists (root=0x60ceb6f9d2a8)
at allpaths.c:351
#15 0x000060ceb52121af in make_one_rel (root=0x60ceb6f9d2a8,
joinlist=0x60ceb6fbdea8) at allpaths.c:221
#16 0x000060ceb5257a8d in query_planner (root=0x60ceb6f9d2a8,
qp_callback=0x60ceb525e2e6 <standard_qp_callback>,
    qp_extra=0x7fff7ea15d90) at planmain.c:280
#17 0x000060ceb525a4f0 in grouping_planner (root=0x60ceb6f9d2a8,
tuple_fraction=0, setops=0x0) at planner.c:1520
#18 0x000060ceb5259b8f in subquery_planner (glob=0x60ceb70715b8,
parse=0x60ceb6f63558, parent_root=0x0,
    hasRecursion=false, tuple_fraction=0, setops=0x0) at planner.c:1089
#19 0x000060ceb52581f2 in standard_planner (parse=0x60ceb6f63558,
    query_string=0x60ceb6f62020 "EXPLAIN (COSTS OFF)\nSELECT COUNT(*)
FROM guid1 WHERE guid_field <> '", '1' <repeats 32 --Type <RET> for
more, q to quit, c to continue without paging--
times>, "' OR\n\t\t\t\t\t\t\tguid_field <>
'3f3e3c3b-3a30-3938-3736-353433a2313e';", cursorOptions=2048,
    boundParams=0x0) at planner.c:415
#20 0x000060ceb5257f1c in planner (parse=0x60ceb6f63558,
    query_string=0x60ceb6f62020 "EXPLAIN (COSTS OFF)\nSELECT COUNT(*)
FROM guid1 WHERE guid_field <> '", '1' <repeats 32 times>, "'
OR\n\t\t\t\t\t\t\tguid_field <>
'3f3e3c3b-3a30-3938-3736-353433a2313e';", cursorOptions=2048,
    boundParams=0x0) at planner.c:282
#21 0x000060ceb53b89d9 in pg_plan_query (querytree=0x60ceb6f63558,
    query_string=0x60ceb6f62020 "EXPLAIN (COSTS OFF)\nSELECT COUNT(*)
FROM guid1 WHERE guid_field <> '", '1' <repeats 32 times>, "'
OR\n\t\t\t\t\t\t\tguid_field <>
'3f3e3c3b-3a30-3938-3736-353433a2313e';", cursorOptions=2048,
    boundParams=0x0) at postgres.c:912
#22 0x000060ceb501feeb in standard_ExplainOneQuery
(query=0x60ceb6f63558, cursorOptions=2048, into=0x0,
    es=0x60ceb703acc8,
    queryString=0x60ceb6f62020 "EXPLAIN (COSTS OFF)\nSELECT COUNT(*)
FROM guid1 WHERE guid_field <> '", '1' <repeats 32 times>, "'
OR\n\t\t\t\t\t\t\tguid_field <>
'3f3e3c3b-3a30-3938-3736-353433a2313e';", params=0x0, queryEnv=0x0)
    at explain.c:491
#23 0x000060ceb501fd09 in ExplainOneQuery (query=0x60ceb6f63558,
cursorOptions=2048, into=0x0, es=0x60ceb703acc8,
    queryString=0x60ceb6f62020 "EXPLAIN (COSTS OFF)\nSELECT COUNT(*)
FROM guid1 WHERE guid_field <> '", '1' <repeats 32 times>, "'
OR\n\t\t\t\t\t\t\tguid_field <>
'3f3e3c3b-3a30-3938-3736-353433a2313e';", params=0x0, queryEnv=0x0)
    at explain.c:447
--Type <RET> for more, q to quit, c to continue without paging--
#24 0x000060ceb501f939 in ExplainQuery (pstate=0x60ceb703abb8,
stmt=0x60ceb6f63398, params=0x0, dest=0x60ceb703ab28)
    at explain.c:343
#25 0x000060ceb53c32e0 in standard_ProcessUtility (pstmt=0x60ceb6f63448,
    queryString=0x60ceb6f62020 "EXPLAIN (COSTS OFF)\nSELECT COUNT(*)
FROM guid1 WHERE guid_field <> '", '1' <repeats 32 times>, "'
OR\n\t\t\t\t\t\t\tguid_field <>
'3f3e3c3b-3a30-3938-3736-353433a2313e';", readOnlyTree=false,
    context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0,
dest=0x60ceb703ab28, qc=0x7fff7ea16530) at utility.c:863
#26 0x000060ceb53c2852 in ProcessUtility (pstmt=0x60ceb6f63448,
    queryString=0x60ceb6f62020 "EXPLAIN (COSTS OFF)\nSELECT COUNT(*)
FROM guid1 WHERE guid_field <> '", '1' <repeats 32 times>, "'
OR\n\t\t\t\t\t\t\tguid_field <>
'3f3e3c3b-3a30-3938-3736-353433a2313e';", readOnlyTree=false,
    context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0,
dest=0x60ceb703ab28, qc=0x7fff7ea16530) at utility.c:523
#27 0x000060ceb53c10cd in PortalRunUtility (portal=0x60ceb6fe6c50,
pstmt=0x60ceb6f63448, isTopLevel=true,
    setHoldSnapshot=true, dest=0x60ceb703ab28, qc=0x7fff7ea16530) at
pquery.c:1158
#28 0x000060ceb53c0e0a in FillPortalStore (portal=0x60ceb6fe6c50,
isTopLevel=true) at pquery.c:1031
#29 0x000060ceb53c06bb in PortalRun (portal=0x60ceb6fe6c50,
count=9223372036854775807, isTopLevel=true, run_once=true,
    dest=0x60ceb6f63be8, altdest=0x60ceb6f63be8, qc=0x7fff7ea16780) at
pquery.c:763
#30 0x000060ceb53b911f in exec_simple_query (
    query_string=0x60ceb6f62020 "EXPLAIN (COSTS OFF)\nSELECT COUNT(*)
FROM guid1 WHERE guid_field <> '", '1' <repeats 32 times>, "'
OR\n\t\t\t\t\t\t\tguid_field <>
'3f3e3c3b-3a30-3938-3736-353433a2313e';") at postgres.c:1284
#31 0x000060ceb53be4ef in PostgresMain (dbname=0x60ceb6fa0c00
"regression", username=0x60ceb6fa0be8 "alena")
--Type <RET> for more, q to quit, c to continue without paging--
    at postgres.c:4766
#32 0x000060ceb53b4c2a in BackendMain (startup_data=0x7fff7ea16a04 "",
startup_data_len=4) at backend_startup.c:107
#33 0x000060ceb52c9b80 in postmaster_child_launch (child_type=B_BACKEND,
startup_data=0x7fff7ea16a04 "",
    startup_data_len=4, client_sock=0x7fff7ea16a50) at launch_backend.c:274
#34 0x000060ceb52cfe87 in BackendStartup (client_sock=0x7fff7ea16a50) at
postmaster.c:3495
#35 0x000060ceb52cd0df in ServerLoop () at postmaster.c:1662
#36 0x000060ceb52cc9a6 in PostmasterMain (argc=3, argv=0x60ceb6ec6d10)
at postmaster.c:1360
#37 0x000060ceb517671c in main (argc=3, argv=0x60ceb6ec6d10) at main.c:197

I have fixed it by adding the condition that the opno of the clause must
be a member of the opfamily of the index.

tp = SearchSysCache3(AMOPOPID,
            ObjectIdGetDatum(opno),
            CharGetDatum(AMOP_SEARCH),
            ObjectIdGetDatum(index->opfamily[indexcol]));
if (!HeapTupleIsValid(tp))
return NULL;

ReleaseSysCache(tp);

I attached the diff file and new versions of patches.

--
Regards,
Alena Rybakina
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company