Hi, all!
The optimizer will itself do a limited form of "normalizing to CNF".
Are you familiar with extract_restriction_or_clauses(), from
orclauses.c? Comments above the function have an example of how this
can work:
* Although a join clause must reference multiple relations overall,
* an OR of ANDs clause might contain sub-clauses that reference
just one
* relation and can be used to build a restriction clause for that rel.
* For example consider
* WHERE ((a.x = 42 AND b.y = 43) OR (a.x = 44 AND b.z = 45));
* We can transform this into
* WHERE ((a.x = 42 AND b.y = 43) OR (a.x = 44 AND b.z = 45))
* AND (a.x = 42 OR a.x = 44)
* AND (b.y = 43 OR b.z = 45);
* which allows the latter clauses to be applied during the scans of
a and b,
* perhaps as index qualifications, and in any case reducing the
number of
* rows arriving at the join. In essence this is a partial
transformation to
* CNF (AND of ORs format). It is not complete, however, because we
do not
* unravel the original OR --- doing so would usually bloat the
qualification
* expression to little gain.
This is an interesting feature. I didn't notice this function before,
I studied many times consider_new_or_cause, which were called there.
As far as I know, there is a selectivity calculation going on there,
but as far as I remember, I called it earlier after my conversion, and
unfortunately it didn't solve my problem with calculating selectivity.
I'll reconsider it again, maybe I can find something I missed.
Of course this immediately makes me wonder: shouldn't your patch be
able to perform an additional transformation here? You know, by
transforming "a.x = 42 OR a.x = 44" into "a IN (42, 44)"? Although I
haven't checked for myself, I assume that this doesn't happen right
now, since your patch currently performs all of its transformations
during parsing.
I also noticed that the same comment block goes on to say something
about "clauselist_selectivity's inability to recognize redundant
conditions". Perhaps that is relevant to the problems you were having
with selectivity estimation, back when the code was in
preprocess_qual_conditions() instead? I have no reason to believe that
there should be any redundancy left behind by your transformation, so
this is just one possibility to consider.
Separately, the commit message of commit 25a9e54d2d says something
about how the planner builds RestrictInfos, which seems
possibly-relevant. That commit enhanced extended statistics for OR
clauses, so the relevant paragraph describes a limitation of extended
statistics with OR clauses specifically. I'm just guessing, but it
still seems like it might be relevant to the problem you ran into with
selectivity estimation. Another possibility to consider.
I understood what is said about AND clauses in this comment. It seems
to me that AND clauses saved like (BoolExpr *)
expr->args->(RestrictInfo *) clauseA->(RestrictInfo *)clauseB lists
and OR clauses saved like (BoolExpr *) expr -> orclause->(RestrictInfo
*)clause A->(RestrictInfo *)clause B.
As I understand it, selectivity is calculated for each expression. But
I'll exploring it deeper, because I think this place may contain the
answer to the question, what's wrong with selectivity calculation in
my patch.
I could move transformation in there (extract_restriction_or_clauses)
and didn't have any problem with selectivity calculation, besides it
also works on the redundant or duplicates stage. So, it looks like:
CREATE TABLE tenk1 (unique1 int, unique2 int, ten int, hundred int);
insert into tenk1 SELECT x,x,x,x FROM generate_series(1,50000) as x;
CREATE INDEX a_idx1 ON tenk1(unique1); CREATE INDEX a_idx2 ON
tenk1(unique2); CREATE INDEX a_hundred ON tenk1(hundred);
explain analyze select * from tenk1 a join tenk1 b on ((a.unique2 = 3 or
a.unique2 = 7));
PLAN
------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.29..2033.62 rows=100000 width=32) (actual
time=0.090..60.258 rows=100000 loops=1) -> Seq Scan on tenk1 b
(cost=0.00..771.00 rows=50000 width=16) (actual time=0.016..9.747
rows=50000 loops=1) -> Materialize (cost=0.29..12.62 rows=2 width=16)
(actual time=0.000..0.000 rows=2 loops=50000) -> Index Scan using a_idx2
on tenk1 a (cost=0.29..12.62 rows=2 width=16) (actual time=0.063..0.068
rows=2 loops=1) Index Cond: (unique2 = ANY (ARRAY[3, 7])) Planning Time:
8.257 ms Execution Time: 64.453 ms (7 rows)
Overall, this was due to incorrectly defined types of elements in the
array, and if we had applied the transformation with the definition of
the tup operator, we could have avoided such problems (I used
make_scalar_array_op and have not yet found an alternative to this).
When I moved the transformation on the index creation stage, it couldn't
work properly and as a result I faced the same problem of selectivity
calculation. I supposed that the selectivity values are also used there,
and not recalculated all over again. perhaps we can solve this by
forcibly recalculating the selectivity values, but I foresee other
problems there.
explain analyze select * from tenk1 a join tenk1 b on ((a.unique2 = 3 or
a.unique2 = 7));
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=12.58..312942.91 rows=24950000 width=32) (actual
time=0.040..47.582 rows=100000 loops=1) -> Seq Scan on tenk1 b
(cost=0.00..771.00 rows=50000 width=16) (actual time=0.009..7.039
rows=50000 loops=1) -> Materialize (cost=12.58..298.16 rows=499
width=16) (actual time=0.000..0.000 rows=2 loops=50000) -> Bitmap Heap
Scan on tenk1 a (cost=12.58..295.66 rows=499 width=16) (actual
time=0.025..0.028 rows=2 loops=1) Recheck Cond: ((unique2 = 3) OR
(unique2 = 7)) Heap Blocks: exact=1 -> BitmapOr (cost=12.58..12.58
rows=500 width=0) (actual time=0.023..0.024 rows=0 loops=1) -> Bitmap
Index Scan on a_idx2 (cost=0.00..6.17 rows=250 width=0) (actual
time=0.019..0.019 rows=1 loops=1) Index Cond: (unique2 = 3) -> Bitmap
Index Scan on a_idx2 (cost=0.00..6.17 rows=250 width=0) (actual
time=0.003..0.003 rows=1 loops=1) Index Cond: (unique2 = 7) Planning
Time: 0.401 ms Execution Time: 51.350 ms (13 rows)
I have attached a diff file so far, but it is very raw and did not pass
all regression tests (I attached regression.diff) and even had bad
conversion cases (some of the cases did not work at all, in other cases
there were no non-converted nodes). But now I see an interesting
transformation, which was the most interesting for me.
EXPLAIN (COSTS OFF) SELECT * FROM tenk1 WHERE thousand = 42 AND
(tenthous = 1 OR tenthous = 3 OR tenthous = 42); - QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
- Bitmap Heap Scan on tenk1 - Recheck Cond: (((thousand = 42) AND
(tenthous = 1)) OR ((thousand = 42) AND (tenthous = 3)) OR ((thousand =
42) AND (tenthous = 42))) - -> BitmapOr - -> Bitmap Index Scan on
tenk1_thous_tenthous - Index Cond: ((thousand = 42) AND (tenthous = 1))
- -> Bitmap Index Scan on tenk1_thous_tenthous - Index Cond: ((thousand
= 42) AND (tenthous = 3)) - -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: ((thousand = 42) AND (tenthous = 42)) -(9 rows) + QUERY
PLAN
+------------------------------------------------------------------------
+ Index Scan using tenk1_thous_tenthous on tenk1 + Index Cond:
((thousand = 42) AND (tenthous = ANY (ARRAY[1, 3, 42]))) +(2 rows)
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/opr_sanity.out /home/alena/postgrespro7/src/test/regress/results/opr_sanity.out
--- /home/alena/postgrespro7/src/test/regress/expected/opr_sanity.out 2023-08-12 02:03:45.284074834 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/opr_sanity.out 2023-08-17 00:00:22.793043910 +0300
@@ -47,10 +47,8 @@
SELECT p1.oid, p1.proname
FROM pg_proc as p1
WHERE (prosrc = '' OR prosrc = '-') AND prosqlbody IS NULL;
- oid | proname
------+---------
-(0 rows)
-
+ERROR: could not determine which collation to use for string comparison
+HINT: Use the COLLATE clause to set the collation explicitly.
-- proretset should only be set for normal functions
SELECT p1.oid, p1.proname
FROM pg_proc AS p1
@@ -81,10 +79,8 @@
SELECT p1.oid, p1.proname
FROM pg_proc as p1
WHERE prolang = 13 AND (probin IS NULL OR probin = '' OR probin = '-');
- oid | proname
------+---------
-(0 rows)
-
+ERROR: could not determine which collation to use for string comparison
+HINT: Use the COLLATE clause to set the collation explicitly.
SELECT p1.oid, p1.proname
FROM pg_proc as p1
WHERE prolang != 13 AND probin IS NOT NULL;
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/create_index.out /home/alena/postgrespro7/src/test/regress/results/create_index.out
--- /home/alena/postgrespro7/src/test/regress/expected/create_index.out 2023-08-12 02:03:45.260074298 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/create_index.out 2023-08-17 00:00:25.309060540 +0300
@@ -1838,18 +1838,11 @@
EXPLAIN (COSTS OFF)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
- Bitmap Heap Scan on tenk1
- Recheck Cond: (((thousand = 42) AND (tenthous = 1)) OR ((thousand = 42) AND (tenthous = 3)) OR ((thousand = 42) AND (tenthous = 42)))
- -> BitmapOr
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: ((thousand = 42) AND (tenthous = 1))
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: ((thousand = 42) AND (tenthous = 3))
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: ((thousand = 42) AND (tenthous = 42))
-(9 rows)
+ QUERY PLAN
+------------------------------------------------------------------------
+ Index Scan using tenk1_thous_tenthous on tenk1
+ Index Cond: ((thousand = 42) AND (tenthous = ANY (ARRAY[1, 3, 42])))
+(2 rows)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
@@ -1861,20 +1854,17 @@
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
- QUERY PLAN
----------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Aggregate
-> Bitmap Heap Scan on tenk1
- Recheck Cond: ((hundred = 42) AND ((thousand = 42) OR (thousand = 99)))
+ Recheck Cond: ((hundred = 42) AND (thousand = ANY (ARRAY[42, 99])))
-> BitmapAnd
-> Bitmap Index Scan on tenk1_hundred
Index Cond: (hundred = 42)
- -> BitmapOr
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: (thousand = 42)
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: (thousand = 99)
-(11 rows)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = ANY (ARRAY[42, 99]))
+(8 rows)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/inherit.out /home/alena/postgrespro7/src/test/regress/results/inherit.out
--- /home/alena/postgrespro7/src/test/regress/expected/inherit.out 2023-08-12 02:03:29.411719453 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/inherit.out 2023-08-17 00:00:26.781070266 +0300
@@ -1929,7 +1929,7 @@
QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on part_ab_cd list_parted
- Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
+ Filter: (((a)::text = ANY ('{NULL,cd}'::text[])) OR ((a)::text = 'ab'::text))
(2 rows)
explain (costs off) select * from list_parted where a = 'ab';
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/misc.out /home/alena/postgrespro7/src/test/regress/results/misc.out
--- /home/alena/postgrespro7/src/test/regress/expected/misc.out 2023-08-12 02:03:29.439720081 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/misc.out 2023-08-17 00:00:37.729142525 +0300
@@ -99,10 +99,14 @@
SELECT 'posthacking', p.name
FROM person* p
WHERE p.name = 'mike' or p.name = 'jeff';
+ERROR: could not determine which collation to use for string comparison
+HINT: Use the COLLATE clause to set the collation explicitly.
INSERT INTO hobbies_r (name, person)
SELECT 'basketball', p.name
FROM person p
WHERE p.name = 'joe' or p.name = 'sally';
+ERROR: could not determine which collation to use for string comparison
+HINT: Use the COLLATE clause to set the collation explicitly.
INSERT INTO hobbies_r (name) VALUES ('skywalking');
INSERT INTO equipment_r (name, hobby) VALUES ('advil', 'posthacking');
INSERT INTO equipment_r (name, hobby) VALUES ('peet''s coffee', 'posthacking');
@@ -163,24 +167,17 @@
-- everyone else does nothing.
--
SELECT p.name, name(p.hobbies) FROM ONLY person p;
- name | name
--------+-------------
- mike | posthacking
- joe | basketball
- sally | basketball
-(3 rows)
+ name | name
+------+------
+(0 rows)
--
-- as above, but jeff also does post_hacking.
--
SELECT p.name, name(p.hobbies) FROM person* p;
- name | name
--------+-------------
- mike | posthacking
- joe | basketball
- sally | basketball
- jeff | posthacking
-(4 rows)
+ name | name
+------+------
+(0 rows)
--
-- the next two queries demonstrate how functions generate bogus duplicates.
@@ -188,25 +185,16 @@
--
SELECT DISTINCT hobbies_r.name, name(hobbies_r.equipment) FROM hobbies_r
ORDER BY 1,2;
- name | name
--------------+---------------
- basketball | hightops
- posthacking | advil
- posthacking | peet's coffee
- skywalking | guts
-(4 rows)
+ name | name
+------------+------
+ skywalking | guts
+(1 row)
SELECT hobbies_r.name, (hobbies_r.equipment).name FROM hobbies_r;
- name | name
--------------+---------------
- posthacking | advil
- posthacking | peet's coffee
- posthacking | advil
- posthacking | peet's coffee
- basketball | hightops
- basketball | hightops
- skywalking | guts
-(7 rows)
+ name | name
+------------+------
+ skywalking | guts
+(1 row)
--
-- mike needs advil and peet's coffee,
@@ -214,71 +202,41 @@
-- everyone else is fine.
--
SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM ONLY person p;
- name | name | name
--------+-------------+---------------
- mike | posthacking | advil
- mike | posthacking | peet's coffee
- joe | basketball | hightops
- sally | basketball | hightops
-(4 rows)
+ name | name | name
+------+------+------
+(0 rows)
--
-- as above, but jeff needs advil and peet's coffee as well.
--
SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM person* p;
- name | name | name
--------+-------------+---------------
- mike | posthacking | advil
- mike | posthacking | peet's coffee
- joe | basketball | hightops
- sally | basketball | hightops
- jeff | posthacking | advil
- jeff | posthacking | peet's coffee
-(6 rows)
+ name | name | name
+------+------+------
+(0 rows)
--
-- just like the last two, but make sure that the target list fixup and
-- unflattening is being done correctly.
--
SELECT name(equipment(p.hobbies)), p.name, name(p.hobbies) FROM ONLY person p;
- name | name | name
----------------+-------+-------------
- advil | mike | posthacking
- peet's coffee | mike | posthacking
- hightops | joe | basketball
- hightops | sally | basketball
-(4 rows)
+ name | name | name
+------+------+------
+(0 rows)
SELECT (p.hobbies).equipment.name, p.name, name(p.hobbies) FROM person* p;
- name | name | name
----------------+-------+-------------
- advil | mike | posthacking
- peet's coffee | mike | posthacking
- hightops | joe | basketball
- hightops | sally | basketball
- advil | jeff | posthacking
- peet's coffee | jeff | posthacking
-(6 rows)
+ name | name | name
+------+------+------
+(0 rows)
SELECT (p.hobbies).equipment.name, name(p.hobbies), p.name FROM ONLY person p;
- name | name | name
----------------+-------------+-------
- advil | posthacking | mike
- peet's coffee | posthacking | mike
- hightops | basketball | joe
- hightops | basketball | sally
-(4 rows)
+ name | name | name
+------+------+------
+(0 rows)
SELECT name(equipment(p.hobbies)), name(p.hobbies), p.name FROM person* p;
- name | name | name
----------------+-------------+-------
- advil | posthacking | mike
- peet's coffee | posthacking | mike
- hightops | basketball | joe
- hightops | basketball | sally
- advil | posthacking | jeff
- peet's coffee | posthacking | jeff
-(6 rows)
+ name | name | name
+------+------+------
+(0 rows)
SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer')));
name
@@ -334,7 +292,7 @@
SELECT hobbies_by_name('basketball');
hobbies_by_name
-----------------
- joe
+
(1 row)
SELECT name, overpaid(emp.*) FROM emp;
@@ -364,28 +322,16 @@
(1 row)
SELECT *, name(equipment(h.*)) FROM hobbies_r h;
- name | person | name
--------------+--------+---------------
- posthacking | mike | advil
- posthacking | mike | peet's coffee
- posthacking | jeff | advil
- posthacking | jeff | peet's coffee
- basketball | joe | hightops
- basketball | sally | hightops
- skywalking | | guts
-(7 rows)
+ name | person | name
+------------+--------+------
+ skywalking | | guts
+(1 row)
SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h;
- name | person | name
--------------+--------+---------------
- posthacking | mike | advil
- posthacking | mike | peet's coffee
- posthacking | jeff | advil
- posthacking | jeff | peet's coffee
- basketball | joe | hightops
- basketball | sally | hightops
- skywalking | | guts
-(7 rows)
+ name | person | name
+------------+--------+------
+ skywalking | | guts
+(1 row)
--
-- functional joins
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/tidscan.out /home/alena/postgrespro7/src/test/regress/results/tidscan.out
--- /home/alena/postgrespro7/src/test/regress/expected/tidscan.out 2023-08-12 02:03:29.511721694 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/tidscan.out 2023-08-17 00:00:37.521141153 +0300
@@ -46,7 +46,7 @@
QUERY PLAN
--------------------------------------------------------------
Tid Scan on tidscan
- TID Cond: ((ctid = '(0,2)'::tid) OR ('(0,1)'::tid = ctid))
+ TID Cond: (ctid = ANY (ARRAY['(0,2)'::tid, '(0,1)'::tid]))
(2 rows)
SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/stats_ext.out /home/alena/postgrespro7/src/test/regress/results/stats_ext.out
--- /home/alena/postgrespro7/src/test/regress/expected/stats_ext.out 2023-08-12 02:03:45.320075639 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/stats_ext.out 2023-08-17 00:00:41.165165174 +0300
@@ -1160,17 +1160,13 @@
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND (b = ''1'' OR b = ''2'')');
- estimated | actual
------------+--------
- 4 | 100
-(1 row)
-
+ERROR: could not determine which collation to use for string comparison
+HINT: Use the COLLATE clause to set the collation explicitly.
+CONTEXT: PL/pgSQL function check_estimated_rows(text) line 7 at FOR over EXECUTE statement
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 2 OR a = 51 OR a = 52) AND (b = ''1'' OR b = ''2'')');
- estimated | actual
------------+--------
- 8 | 200
-(1 row)
-
+ERROR: could not determine which collation to use for string comparison
+HINT: Use the COLLATE clause to set the collation explicitly.
+CONTEXT: PL/pgSQL function check_estimated_rows(text) line 7 at FOR over EXECUTE statement
-- OR clauses referencing different attributes
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR b = ''1'') AND b = ''1''');
estimated | actual
@@ -1322,21 +1318,17 @@
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND b = ''1''');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND (b = ''1'' OR b = ''2'')');
- estimated | actual
------------+--------
- 99 | 100
-(1 row)
-
+ERROR: could not determine which collation to use for string comparison
+HINT: Use the COLLATE clause to set the collation explicitly.
+CONTEXT: PL/pgSQL function check_estimated_rows(text) line 7 at FOR over EXECUTE statement
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 2 OR a = 51 OR a = 52) AND (b = ''1'' OR b = ''2'')');
- estimated | actual
------------+--------
- 197 | 200
-(1 row)
-
+ERROR: could not determine which collation to use for string comparison
+HINT: Use the COLLATE clause to set the collation explicitly.
+CONTEXT: PL/pgSQL function check_estimated_rows(text) line 7 at FOR over EXECUTE statement
-- OR clauses referencing different attributes are incompatible
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR b = ''1'') AND b = ''1''');
estimated | actual
@@ -1501,17 +1493,13 @@
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
- estimated | actual
------------+--------
- 1 | 100
-(1 row)
-
+ERROR: could not determine which collation to use for string comparison
+HINT: Use the COLLATE clause to set the collation explicitly.
+CONTEXT: PL/pgSQL function check_estimated_rows(text) line 7 at FOR over EXECUTE statement
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 4 OR (a * 2) = 102 OR (a * 2) = 104) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
- estimated | actual
------------+--------
- 1 | 200
-(1 row)
-
+ERROR: could not determine which collation to use for string comparison
+HINT: Use the COLLATE clause to set the collation explicitly.
+CONTEXT: PL/pgSQL function check_estimated_rows(text) line 7 at FOR over EXECUTE statement
-- OR clauses referencing different attributes
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR upper(b) = ''1'') AND upper(b) = ''1''');
estimated | actual
@@ -1664,21 +1652,17 @@
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = ''1''');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
- estimated | actual
------------+--------
- 99 | 100
-(1 row)
-
+ERROR: could not determine which collation to use for string comparison
+HINT: Use the COLLATE clause to set the collation explicitly.
+CONTEXT: PL/pgSQL function check_estimated_rows(text) line 7 at FOR over EXECUTE statement
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 4 OR (a * 2) = 102 OR (a * 2) = 104) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
- estimated | actual
------------+--------
- 197 | 200
-(1 row)
-
+ERROR: could not determine which collation to use for string comparison
+HINT: Use the COLLATE clause to set the collation explicitly.
+CONTEXT: PL/pgSQL function check_estimated_rows(text) line 7 at FOR over EXECUTE statement
-- OR clauses referencing different attributes
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR upper(b) = ''1'') AND upper(b) = ''1''');
estimated | actual
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/partition_join.out /home/alena/postgrespro7/src/test/regress/results/partition_join.out
--- /home/alena/postgrespro7/src/test/regress/expected/partition_join.out 2023-08-12 02:03:45.288074924 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/partition_join.out 2023-08-17 00:00:51.173231068 +0300
@@ -290,13 +290,13 @@
-- Currently we can't do partitioned join if nullable-side partitions are pruned
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 FULL JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 OR t2.a = 0 ORDER BY t1.a, t2.b;
- QUERY PLAN
-----------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------
Sort
Sort Key: prt1.a, prt2.b
-> Hash Full Join
Hash Cond: (prt1.a = prt2.b)
- Filter: ((prt1.b = 0) OR (prt2.a = 0))
+ Filter: (((prt1.b = 0) OR (prt2.a = 0)) AND ((prt1.b = 0) OR (prt2.a = 0)))
-> Append
-> Seq Scan on prt1_p1 prt1_1
Filter: (a < 450)
@@ -2270,10 +2270,11 @@
where not exists (select 1 from prtx2
where prtx2.a=prtx1.a and (prtx2.b=prtx1.b+1 or prtx2.c=99))
and a<20 and c=91;
- QUERY PLAN
------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------
Append
-> Nested Loop Anti Join
+ Join Filter: ((prtx2_1.b = (prtx1_1.b + 1)) OR (prtx2_1.c = 99))
-> Seq Scan on prtx1_1
Filter: ((a < 20) AND (c = 91))
-> Bitmap Heap Scan on prtx2_1
@@ -2285,6 +2286,7 @@
-> Bitmap Index Scan on prtx2_1_c_idx
Index Cond: (c = 99)
-> Nested Loop Anti Join
+ Join Filter: ((prtx2_2.b = (prtx1_2.b + 1)) OR (prtx2_2.c = 99))
-> Seq Scan on prtx1_2
Filter: ((a < 20) AND (c = 91))
-> Bitmap Heap Scan on prtx2_2
@@ -2295,7 +2297,7 @@
Index Cond: (b = (prtx1_2.b + 1))
-> Bitmap Index Scan on prtx2_2_c_idx
Index Cond: (c = 99)
-(23 rows)
+(25 rows)
select * from prtx1
where not exists (select 1 from prtx2
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/partition_prune.out /home/alena/postgrespro7/src/test/regress/results/partition_prune.out
--- /home/alena/postgrespro7/src/test/regress/expected/partition_prune.out 2023-08-12 02:03:45.292075013 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/partition_prune.out 2023-08-17 00:00:50.777228463 +0300
@@ -82,24 +82,38 @@
(2 rows)
explain (costs off) select * from lp where a = 'a' or a = 'c';
- QUERY PLAN
-----------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Append
-> Seq Scan on lp_ad lp_1
- Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
+ Filter: (a = ANY ('{a,c}'::bpchar[]))
-> Seq Scan on lp_bc lp_2
- Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
-(5 rows)
+ Filter: (a = ANY ('{a,c}'::bpchar[]))
+ -> Seq Scan on lp_ef lp_3
+ Filter: (a = ANY ('{a,c}'::bpchar[]))
+ -> Seq Scan on lp_g lp_4
+ Filter: (a = ANY ('{a,c}'::bpchar[]))
+ -> Seq Scan on lp_null lp_5
+ Filter: (a = ANY ('{a,c}'::bpchar[]))
+ -> Seq Scan on lp_default lp_6
+ Filter: (a = ANY ('{a,c}'::bpchar[]))
+(13 rows)
explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c');
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Append
-> Seq Scan on lp_ad lp_1
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
-> Seq Scan on lp_bc lp_2
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
-(5 rows)
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
+ -> Seq Scan on lp_ef lp_3
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
+ -> Seq Scan on lp_g lp_4
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
+ -> Seq Scan on lp_default lp_5
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
+(11 rows)
explain (costs off) select * from lp where a <> 'g';
QUERY PLAN
@@ -515,11 +529,13 @@
(27 rows)
explain (costs off) select * from rlp where a = 1 or a = 7;
- QUERY PLAN
---------------------------------
- Seq Scan on rlp2 rlp
- Filter: ((a = 1) OR (a = 7))
-(2 rows)
+ QUERY PLAN
+------------------------------------------------
+ Append
+ Subplans Removed: 1
+ -> Seq Scan on rlp2 rlp_1
+ Filter: (a = ANY ('{1,7}'::integer[]))
+(4 rows)
explain (costs off) select * from rlp where a = 1 or b = 'ab';
QUERY PLAN
@@ -596,14 +612,15 @@
-- where clause contradicts sub-partition's constraint
explain (costs off) select * from rlp where a = 20 or a = 40;
- QUERY PLAN
-----------------------------------------
+ QUERY PLAN
+--------------------------------------------------
Append
+ Subplans Removed: 2
-> Seq Scan on rlp4_1 rlp_1
- Filter: ((a = 20) OR (a = 40))
+ Filter: (a = ANY ('{20,40}'::integer[]))
-> Seq Scan on rlp5_default rlp_2
- Filter: ((a = 20) OR (a = 40))
-(5 rows)
+ Filter: (a = ANY ('{20,40}'::integer[]))
+(6 rows)
explain (costs off) select * from rlp3 where a = 20; /* empty */
QUERY PLAN
@@ -1933,10 +1950,10 @@
explain (costs off) select * from hp where a = 1 and b = 'abcde' and
(c = 2 or c = 3);
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------
Seq Scan on hp2 hp
- Filter: ((a = 1) AND (b = 'abcde'::text) AND ((c = 2) OR (c = 3)))
+ Filter: ((c = ANY ('{2,3}'::integer[])) AND (a = 1) AND (b = 'abcde'::text))
(2 rows)
drop table hp2;
@@ -2265,11 +2282,11 @@
Workers Launched: N
-> Parallel Append (actual rows=N loops=N)
-> Parallel Seq Scan on ab_a1_b2 ab_1 (actual rows=N loops=N)
- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
+ Filter: ((a = ANY (ARRAY[$0, $1])) AND (b = 2))
-> Parallel Seq Scan on ab_a2_b2 ab_2 (never executed)
- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
+ Filter: ((a = ANY (ARRAY[$0, $1])) AND (b = 2))
-> Parallel Seq Scan on ab_a3_b2 ab_3 (actual rows=N loops=N)
- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
+ Filter: ((a = ANY (ARRAY[$0, $1])) AND (b = 2))
(16 rows)
-- Test pruning during parallel nested loop query
@@ -3408,14 +3425,11 @@
(2 rows)
explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}');
- QUERY PLAN
-----------------------------------------------------------------------
- Append
- -> Seq Scan on pp_arrpart1 pp_arrpart_1
- Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
- -> Seq Scan on pp_arrpart2 pp_arrpart_2
- Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
-(5 rows)
+ QUERY PLAN
+------------------------------------
+ Seq Scan on pp_arrpart2 pp_arrpart
+ Filter: (a = '{4,5}'::integer[])
+(2 rows)
explain (costs off) update pp_arrpart set a = a where a = '{1}';
QUERY PLAN
@@ -3464,14 +3478,11 @@
(2 rows)
explain (costs off) select * from pph_arrpart where a in ('{4, 5}', '{1}');
- QUERY PLAN
-----------------------------------------------------------------------
- Append
- -> Seq Scan on pph_arrpart1 pph_arrpart_1
- Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
- -> Seq Scan on pph_arrpart2 pph_arrpart_2
- Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
-(5 rows)
+ QUERY PLAN
+--------------------------------------
+ Seq Scan on pph_arrpart1 pph_arrpart
+ Filter: (a = '{4,5}'::integer[])
+(2 rows)
drop table pph_arrpart;
-- enum type list partition key
diff --git a/src/backend/optimizer/path/clausesel.c b/src/backend/optimizer/path/clausesel.c
index 435438a1735..9c82297e242 100644
--- a/src/backend/optimizer/path/clausesel.c
+++ b/src/backend/optimizer/path/clausesel.c
@@ -751,7 +751,8 @@ clause_selectivity_ext(PlannerInfo *root,
* so that per-subclause selectivities can be cached.
*/
if (rinfo->orclause)
- clause = (Node *) rinfo->orclause;
+ {clause = (Node *) rinfo->orclause;
+ }
else
clause = (Node *) rinfo->clause;
}
@@ -823,6 +824,7 @@ clause_selectivity_ext(PlannerInfo *root,
* Almost the same thing as clauselist_selectivity, but with the
* clauses connected by OR.
*/
+
s1 = clauselist_selectivity_or(root,
((BoolExpr *) clause)->args,
varRelid,
diff --git a/src/backend/optimizer/util/orclauses.c b/src/backend/optimizer/util/orclauses.c
index 6ef9d14b902..81b11865203 100644
--- a/src/backend/optimizer/util/orclauses.c
+++ b/src/backend/optimizer/util/orclauses.c
@@ -22,6 +22,10 @@
#include "optimizer/optimizer.h"
#include "optimizer/orclauses.h"
#include "optimizer/restrictinfo.h"
+#include "utils/lsyscache.h"
+#include "parser/parse_expr.h"
+#include "parser/parse_coerce.h"
+#include "parser/parse_oper.h"
static bool is_safe_restriction_clause_for(RestrictInfo *rinfo, RelOptInfo *rel);
@@ -29,6 +33,300 @@ static Expr *extract_or_clause(RestrictInfo *or_rinfo, RelOptInfo *rel);
static void consider_new_or_clause(PlannerInfo *root, RelOptInfo *rel,
Expr *orclause, RestrictInfo *join_or_rinfo);
+typedef struct OrClauseGroupEntry
+{
+ Node *node;
+ List *consts;
+ Oid collation;
+ Oid opno;
+ RestrictInfo *rinfo;
+ Expr *expr;
+} OrClauseGroupEntry;
+
+/*
+ * Pass through baserestrictinfo clauses and try to convert OR clauses into IN
+ * Return a modified clause list or just the same baserestrictinfo, if no
+ * changes have made.
+ * XXX: do not change source list of clauses at all.
+ */
+static List *
+transform_ors(PlannerInfo *root, List *baserestrictinfo)
+{
+ ListCell *lc;
+ ListCell *lc_cp;
+ List *modified_rinfo = NIL;
+ bool something_changed = false;
+ List *baserestrictinfo_origin = list_copy(baserestrictinfo);
+
+ /*
+ * Complexity of a clause could be arbitrarily sophisticated. Here, we will
+ * look up only on the top level of clause list.
+ * XXX: It is substantiated? Could we change something here?
+ */
+ forboth (lc, baserestrictinfo, lc_cp, baserestrictinfo_origin)
+ {
+ RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
+ RestrictInfo *rinfo_base = lfirst_node(RestrictInfo, lc_cp);
+ List *or_list = NIL;
+ ListCell *lc_eargs,
+ *lc_rargs,
+ *lc_args;
+ List *groups_list = NIL;
+
+ if (!restriction_is_or_clause(rinfo))
+ {
+ /* Add a clause without changes */
+ modified_rinfo = lappend(modified_rinfo, rinfo);
+ continue;
+ }
+
+ /*
+ * NOTE:
+ * It is an OR-clause. So, rinfo->orclause is a BoolExpr node, contains
+ * a list of sub-restrictinfo args, and rinfo->clause - which is the
+ * same expression, made from bare clauses. To not break selectivity
+ * caches and other optimizations, use both:
+ * - use rinfos from orclause if no transformation needed
+ * - use bare quals from rinfo->clause in the case of transformation,
+ * to create new RestrictInfo: in this case we have no options to avoid
+ * selectivity estimation procedure.
+ */
+ forboth(lc_eargs, ((BoolExpr *) rinfo->clause)->args,
+ lc_rargs, ((BoolExpr *) rinfo->orclause)->args)
+ {
+ Expr *bare_orarg = (Expr *) lfirst(lc_eargs);
+ RestrictInfo *sub_rinfo;
+ Node *const_expr;
+ Node *non_const_expr;
+ ListCell *lc_groups;
+ OrClauseGroupEntry *gentry;
+ Oid opno;
+
+ /* It may be one more boolean expression, skip it for now */
+ if (!IsA(lfirst(lc_rargs), RestrictInfo))
+ {
+ or_list = lappend(or_list, (void *) bare_orarg);
+ continue;
+ }
+
+ sub_rinfo = lfirst_node(RestrictInfo, lc_rargs);
+
+ /* Check: it is an expr of the form 'F(x) oper ConstExpr' */
+ if (!IsA(bare_orarg, OpExpr) ||
+ !(bms_is_empty(sub_rinfo->left_relids) ^
+ bms_is_empty(sub_rinfo->right_relids)) ||
+ contain_volatile_functions((Node *) bare_orarg))
+ {
+ /* Again, it's not the expr we can transform */
+ or_list = lappend(or_list, (void *) bare_orarg);
+ continue;
+ }
+
+ /* Get pointers to constant and expression sides of the clause */
+ const_expr =bms_is_empty(sub_rinfo->left_relids) ?
+ get_leftop(sub_rinfo->clause) :
+ get_rightop(sub_rinfo->clause);
+ non_const_expr = bms_is_empty(sub_rinfo->left_relids) ?
+ get_rightop(sub_rinfo->clause) :
+ get_leftop(sub_rinfo->clause);
+
+ opno = ((OpExpr *) sub_rinfo->clause)->opno;
+ if (!op_mergejoinable(opno, exprType(non_const_expr)))
+ {
+ /* And again, filter out non-equality operators */
+ or_list = lappend(or_list, (void *) bare_orarg);
+ continue;
+ }
+
+ /*
+ * At this point we definitely have a transformable clause.
+ * Classify it and add into specific group of clauses, or create new
+ * group.
+ * TODO: to manage complexity in the case of many different clauses
+ * (X1=C1) OR (X2=C2 OR) ... (XN = CN) we could invent something
+ * like a hash table (htab key ???).
+ */
+ foreach(lc_groups, groups_list)
+ {
+ OrClauseGroupEntry *v = (OrClauseGroupEntry *) lfirst(lc_groups);
+
+ Assert(v->node != NULL);
+
+ if (equal(v->node, non_const_expr))
+ {
+ v->consts = lappend(v->consts, const_expr);
+ non_const_expr = NULL;
+ break;
+ }
+ }
+
+ if (non_const_expr == NULL)
+ /*
+ * The clause classified successfully and added into existed
+ * clause group.
+ */
+ continue;
+
+ /* New clause group needed */
+ gentry = palloc(sizeof(OrClauseGroupEntry));
+ gentry->node = non_const_expr;
+ gentry->consts = list_make1(const_expr);
+ gentry->collation = exprInputCollation((Node *)sub_rinfo->clause);
+ gentry->opno = opno;
+ gentry->rinfo = sub_rinfo;
+ gentry->expr = bare_orarg;
+ groups_list = lappend(groups_list, (void *) gentry);
+ }
+
+ if (groups_list == NIL)
+ {
+ /*
+ * No any transformations possible with this rinfo, just add itself
+ * to the list and go further.
+ */
+ modified_rinfo = lappend(modified_rinfo, rinfo);
+ continue;
+ }
+
+ /* Let's convert each group of clauses to an IN operation. */
+
+ /*
+ * Go through the list of groups and convert each, where number of
+ * consts more than 1. trivial groups move to OR-list again
+ */
+
+ foreach(lc_args, groups_list)
+ {
+ OrClauseGroupEntry *gentry = (OrClauseGroupEntry *) lfirst(lc_args);
+ List *allexprs;
+ Oid scalar_type;
+ Oid array_type;
+
+ Assert(list_length(gentry->consts) > 0);
+
+ if (list_length(gentry->consts) == 1)
+ {
+ /*
+ * Only one element in the class. Return rinfo into the BoolExpr
+ * args list unchanged.
+ */
+ list_free(gentry->consts);
+ or_list = lappend(or_list, gentry->expr);
+ continue;
+ }
+
+ /*
+ * Do the transformation.
+ *
+ * First of all, try to select a common type for the array elements.
+ * Note that since the LHS' type is first in the list, it will be
+ * preferred when there is doubt (eg, when all the RHS items are
+ * unknown literals).
+ *
+ * Note: use list_concat here not lcons, to avoid damaging rnonvars.
+ *
+ * As a source of insides, use make_scalar_array_op()
+ */
+ allexprs = list_concat(list_make1(gentry->node), gentry->consts);
+ scalar_type = select_common_type(NULL, allexprs, NULL, NULL);
+
+ if (scalar_type != RECORDOID && OidIsValid(scalar_type))
+ array_type = get_array_type(scalar_type);
+ else
+ array_type = InvalidOid;
+
+ if (array_type != InvalidOid)
+ {
+ /*
+ * OK: coerce all the right-hand non-Var inputs to the common
+ * type and build an ArrayExpr for them.
+ */
+ List *aexprs;
+ ArrayExpr *newa;
+ ScalarArrayOpExpr *saopexpr;
+ ListCell *l;
+
+ aexprs = NIL;
+
+ foreach(l, gentry->consts)
+ {
+ Node *rexpr = (Node *) lfirst(l);
+
+ rexpr = coerce_to_common_type(NULL, rexpr,
+ scalar_type,
+ "IN");
+ aexprs = lappend(aexprs, rexpr);
+ }
+
+ newa = makeNode(ArrayExpr);
+ /* array_collid will be set by parse_collate.c */
+ newa->element_typeid = scalar_type;
+ newa->array_typeid = array_type;
+ newa->multidims = false;
+ newa->elements = aexprs;
+ newa->location = -1;
+
+ saopexpr =
+ (ScalarArrayOpExpr *)
+ make_scalar_array_op(NULL,
+ list_make1(makeString((char *) "=")),
+ true,
+ gentry->node,
+ (Node *) newa,
+ -1);
+
+ or_list = lappend(or_list, (void *) saopexpr);
+ }
+ else
+ {
+ list_free(gentry->consts);
+ or_list = lappend(or_list, gentry->expr);
+ continue;
+ }
+ }
+
+ /*
+ * Make a new version of the restriction. Remember source restriction
+ * can be used in another path (SeqScan, for example).
+ */
+
+ /* One more trick: assemble correct clause */
+ rinfo = make_restrictinfo(root,
+ list_length(or_list) > 1 ? make_orclause(or_list) :
+ (Expr *) linitial(or_list),
+ rinfo->is_pushed_down,
+ rinfo->has_clone,
+ rinfo->is_clone,
+ rinfo->pseudoconstant,
+ rinfo->security_level,
+ rinfo->required_relids,
+ rinfo->incompatible_relids,
+ rinfo->outer_relids);
+ rinfo->eval_cost=rinfo_base->eval_cost;
+ rinfo->norm_selec=rinfo_base->norm_selec;
+ rinfo->outer_selec=rinfo_base->outer_selec;
+ rinfo->left_bucketsize=rinfo_base->left_bucketsize;
+ rinfo->right_bucketsize=rinfo_base->right_bucketsize;
+ rinfo->left_mcvfreq=rinfo_base->left_mcvfreq;
+ rinfo->right_mcvfreq=rinfo_base->right_mcvfreq;
+
+ modified_rinfo = lappend(modified_rinfo, rinfo);
+ list_free_deep(groups_list);
+ something_changed = true;
+ }
+
+ /*
+ * Check if transformation has made. If nothing changed - return
+ * baserestrictinfo as is.
+ */
+ if (something_changed)
+ {
+ return modified_rinfo;
+ }
+
+ list_free(modified_rinfo);
+ return baserestrictinfo;
+}
/*
* extract_restriction_or_clauses
@@ -93,6 +391,10 @@ extract_restriction_or_clauses(PlannerInfo *root)
if (rel->reloptkind != RELOPT_BASEREL)
continue;
+ if (rel->reloptkind == RELOPT_BASEREL)
+ rel->baserestrictinfo = transform_ors(root, rel->baserestrictinfo);
+ rel->joininfo = transform_ors(root, rel->joininfo);
+
/*
* Find potentially interesting OR joinclauses. We can use any
* joinclause that is considered safe to move to this rel by the
