Re: POC, WIP: OR-clause support for indexes
Andreas Karlsson <andreas@proxel.se>
Commits
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Make group_similar_or_args() reorder clause list as little as possible
- 775a06d44c04 18.0 landed
-
Allow usage of match_orclause_to_indexcol() for joins
- 627d63419e22 18.0 landed
-
Skip not SOAP-supported indexes while transforming an OR clause into SAOP
- 5bba0546eecb 18.0 landed
-
Remove the wrong assertion from match_orclause_to_indexcol()
- d4d11940df94 18.0 landed
-
Teach bitmap path generation about transforming OR-clauses to SAOP's
- ae4569161a27 18.0 landed
-
Transform OR-clauses to SAOP's during index matching
- d4378c0005e6 18.0 landed
-
Fix the value of or_to_any_transform_limit in postgresql.conf.sample
- 2af75e117478 17.0 landed
-
Transform OR clauses to ANY expression
- 72bd38cc99a1 17.0 landed
-
MergeAttributes code deduplication
- 64444ce071f6 17.0 cited
-
SEARCH and CYCLE clauses
- 3696a600e229 14.0 cited
-
Improve estimation of OR clauses using extended statistics.
- 25a9e54d2db3 14.0 cited
-
Teach btree to handle ScalarArrayOpExpr quals natively.
- 9e8da0f75731 9.2.0 cited
-
Revise collation derivation method and expression-tree representation.
- b310b6e31ce5 9.1.0 cited
-
Instead of trying to force WHERE clauses into CNF or DNF normal form,
- 9888192fb773 8.0.0 cited
I gave this patch a quick spin and noticed a strange query plan.
CREATE TABLE test (a int, b int, c int);
CREATE INDEX ON test USING gin (a, b, c);
INSERT INTO test SELECT i % 7, i % 9, i % 11 FROM generate_series(1,
1000000) i;
EXPLAIN ANALYZE SELECT * FROM test WHERE (a = 3 OR b = 5) AND c = 2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=829.45..4892.10 rows=21819 width=12)
(actual time=66.494..76.234 rows=21645 loops=1)
Recheck Cond: ((((a = 3) AND (c = 2)) OR ((b = 5) AND (c = 2))) AND
(c = 2))
Heap Blocks: exact=5406
-> Bitmap Index Scan on test_a_b_c_idx (cost=0.00..824.00
rows=2100 width=0) (actual time=65.272..65.272 rows=21645 loops=1)
Index Cond: ((((a = 3) AND (c = 2)) OR ((b = 5) AND (c = 2)))
AND (c = 2))
Planning time: 0.200 ms
Execution time: 77.206 ms
(7 rows)
Shouldn't the index condition just be "((a = 3) AND (c = 2)) OR ((b = 5)
AND (c = 2))"?
Also when applying and reading the patch I noticed some minor
issues/nitpick.
- I get whitespace warnings from git apply when I apply the patches.
- You have any insconstent style for casts: I think "(Node*)clause"
should be "(Node *) clause".
- Same with pointers. "List* quals" should be "List *quals"
- I am personally not a fan of seeing the "isorderby == false &&
index->rd_amroutine->amcanorclause" clause twice. Feels like a risk for
diverging code paths. But it could be that there is no clean alternative.
Andreas