diff options
Diffstat (limited to 'src/test/regress/expected/equivclass.out')
| -rw-r--r-- | src/test/regress/expected/equivclass.out | 453 |
1 files changed, 453 insertions, 0 deletions
diff --git a/src/test/regress/expected/equivclass.out b/src/test/regress/expected/equivclass.out new file mode 100644 index 0000000..126f704 --- /dev/null +++ b/src/test/regress/expected/equivclass.out @@ -0,0 +1,453 @@ +-- +-- Tests for the planner's "equivalence class" mechanism +-- +-- One thing that's not tested well during normal querying is the logic +-- for handling "broken" ECs. This is because an EC can only become broken +-- if its underlying btree operator family doesn't include a complete set +-- of cross-type equality operators. There are not (and should not be) +-- any such families built into Postgres; so we have to hack things up +-- to create one. We do this by making two alias types that are really +-- int8 (so we need no new C code) and adding only some operators for them +-- into the standard integer_ops opfamily. +create type int8alias1; +create function int8alias1in(cstring) returns int8alias1 + strict immutable language internal as 'int8in'; +NOTICE: return type int8alias1 is only a shell +create function int8alias1out(int8alias1) returns cstring + strict immutable language internal as 'int8out'; +NOTICE: argument type int8alias1 is only a shell +create type int8alias1 ( + input = int8alias1in, + output = int8alias1out, + like = int8 +); +create type int8alias2; +create function int8alias2in(cstring) returns int8alias2 + strict immutable language internal as 'int8in'; +NOTICE: return type int8alias2 is only a shell +create function int8alias2out(int8alias2) returns cstring + strict immutable language internal as 'int8out'; +NOTICE: argument type int8alias2 is only a shell +create type int8alias2 ( + input = int8alias2in, + output = int8alias2out, + like = int8 +); +create cast (int8 as int8alias1) without function; +create cast (int8 as int8alias2) without function; +create cast (int8alias1 as int8) without function; +create cast (int8alias2 as int8) without function; +create function int8alias1eq(int8alias1, int8alias1) returns bool + strict immutable language internal as 'int8eq'; +create operator = ( + procedure = int8alias1eq, + leftarg = int8alias1, rightarg = int8alias1, + commutator = =, + restrict = eqsel, join = eqjoinsel, + merges +); +alter operator family integer_ops using btree add + operator 3 = (int8alias1, int8alias1); +create function int8alias2eq(int8alias2, int8alias2) returns bool + strict immutable language internal as 'int8eq'; +create operator = ( + procedure = int8alias2eq, + leftarg = int8alias2, rightarg = int8alias2, + commutator = =, + restrict = eqsel, join = eqjoinsel, + merges +); +alter operator family integer_ops using btree add + operator 3 = (int8alias2, int8alias2); +create function int8alias1eq(int8, int8alias1) returns bool + strict immutable language internal as 'int8eq'; +create operator = ( + procedure = int8alias1eq, + leftarg = int8, rightarg = int8alias1, + restrict = eqsel, join = eqjoinsel, + merges +); +alter operator family integer_ops using btree add + operator 3 = (int8, int8alias1); +create function int8alias1eq(int8alias1, int8alias2) returns bool + strict immutable language internal as 'int8eq'; +create operator = ( + procedure = int8alias1eq, + leftarg = int8alias1, rightarg = int8alias2, + restrict = eqsel, join = eqjoinsel, + merges +); +alter operator family integer_ops using btree add + operator 3 = (int8alias1, int8alias2); +create function int8alias1lt(int8alias1, int8alias1) returns bool + strict immutable language internal as 'int8lt'; +create operator < ( + procedure = int8alias1lt, + leftarg = int8alias1, rightarg = int8alias1 +); +alter operator family integer_ops using btree add + operator 1 < (int8alias1, int8alias1); +create function int8alias1cmp(int8, int8alias1) returns int + strict immutable language internal as 'btint8cmp'; +alter operator family integer_ops using btree add + function 1 int8alias1cmp (int8, int8alias1); +create table ec0 (ff int8 primary key, f1 int8, f2 int8); +create table ec1 (ff int8 primary key, f1 int8alias1, f2 int8alias2); +create table ec2 (xf int8 primary key, x1 int8alias1, x2 int8alias2); +-- for the moment we only want to look at nestloop plans +set enable_hashjoin = off; +set enable_mergejoin = off; +-- +-- Note that for cases where there's a missing operator, we don't care so +-- much whether the plan is ideal as that we don't fail or generate an +-- outright incorrect plan. +-- +explain (costs off) + select * from ec0 where ff = f1 and f1 = '42'::int8; + QUERY PLAN +----------------------------------- + Index Scan using ec0_pkey on ec0 + Index Cond: (ff = '42'::bigint) + Filter: (f1 = '42'::bigint) +(3 rows) + +explain (costs off) + select * from ec0 where ff = f1 and f1 = '42'::int8alias1; + QUERY PLAN +--------------------------------------- + Index Scan using ec0_pkey on ec0 + Index Cond: (ff = '42'::int8alias1) + Filter: (f1 = '42'::int8alias1) +(3 rows) + +explain (costs off) + select * from ec1 where ff = f1 and f1 = '42'::int8alias1; + QUERY PLAN +--------------------------------------- + Index Scan using ec1_pkey on ec1 + Index Cond: (ff = '42'::int8alias1) + Filter: (f1 = '42'::int8alias1) +(3 rows) + +explain (costs off) + select * from ec1 where ff = f1 and f1 = '42'::int8alias2; + QUERY PLAN +--------------------------------------------------- + Seq Scan on ec1 + Filter: ((ff = f1) AND (f1 = '42'::int8alias2)) +(2 rows) + +explain (costs off) + select * from ec1, ec2 where ff = x1 and ff = '42'::int8; + QUERY PLAN +------------------------------------------------------------------- + Nested Loop + Join Filter: (ec1.ff = ec2.x1) + -> Index Scan using ec1_pkey on ec1 + Index Cond: ((ff = '42'::bigint) AND (ff = '42'::bigint)) + -> Seq Scan on ec2 +(5 rows) + +explain (costs off) + select * from ec1, ec2 where ff = x1 and ff = '42'::int8alias1; + QUERY PLAN +--------------------------------------------- + Nested Loop + -> Index Scan using ec1_pkey on ec1 + Index Cond: (ff = '42'::int8alias1) + -> Seq Scan on ec2 + Filter: (x1 = '42'::int8alias1) +(5 rows) + +explain (costs off) + select * from ec1, ec2 where ff = x1 and '42'::int8 = x1; + QUERY PLAN +----------------------------------------- + Nested Loop + Join Filter: (ec1.ff = ec2.x1) + -> Index Scan using ec1_pkey on ec1 + Index Cond: (ff = '42'::bigint) + -> Seq Scan on ec2 + Filter: ('42'::bigint = x1) +(6 rows) + +explain (costs off) + select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias1; + QUERY PLAN +--------------------------------------------- + Nested Loop + -> Index Scan using ec1_pkey on ec1 + Index Cond: (ff = '42'::int8alias1) + -> Seq Scan on ec2 + Filter: (x1 = '42'::int8alias1) +(5 rows) + +explain (costs off) + select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias2; + QUERY PLAN +----------------------------------------- + Nested Loop + -> Seq Scan on ec2 + Filter: (x1 = '42'::int8alias2) + -> Index Scan using ec1_pkey on ec1 + Index Cond: (ff = ec2.x1) +(5 rows) + +create unique index ec1_expr1 on ec1((ff + 1)); +create unique index ec1_expr2 on ec1((ff + 2 + 1)); +create unique index ec1_expr3 on ec1((ff + 3 + 1)); +create unique index ec1_expr4 on ec1((ff + 4)); +explain (costs off) + select * from ec1, + (select ff + 1 as x from + (select ff + 2 as ff from ec1 + union all + select ff + 3 as ff from ec1) ss0 + union all + select ff + 4 as x from ec1) as ss1 + where ss1.x = ec1.f1 and ec1.ff = 42::int8; + QUERY PLAN +----------------------------------------------------- + Nested Loop + -> Index Scan using ec1_pkey on ec1 + Index Cond: (ff = '42'::bigint) + -> Append + -> Index Scan using ec1_expr2 on ec1 ec1_1 + Index Cond: (((ff + 2) + 1) = ec1.f1) + -> Index Scan using ec1_expr3 on ec1 ec1_2 + Index Cond: (((ff + 3) + 1) = ec1.f1) + -> Index Scan using ec1_expr4 on ec1 ec1_3 + Index Cond: ((ff + 4) = ec1.f1) +(10 rows) + +explain (costs off) + select * from ec1, + (select ff + 1 as x from + (select ff + 2 as ff from ec1 + union all + select ff + 3 as ff from ec1) ss0 + union all + select ff + 4 as x from ec1) as ss1 + where ss1.x = ec1.f1 and ec1.ff = 42::int8 and ec1.ff = ec1.f1; + QUERY PLAN +------------------------------------------------------------------- + Nested Loop + Join Filter: ((((ec1_1.ff + 2) + 1)) = ec1.f1) + -> Index Scan using ec1_pkey on ec1 + Index Cond: ((ff = '42'::bigint) AND (ff = '42'::bigint)) + Filter: (ff = f1) + -> Append + -> Index Scan using ec1_expr2 on ec1 ec1_1 + Index Cond: (((ff + 2) + 1) = '42'::bigint) + -> Index Scan using ec1_expr3 on ec1 ec1_2 + Index Cond: (((ff + 3) + 1) = '42'::bigint) + -> Index Scan using ec1_expr4 on ec1 ec1_3 + Index Cond: ((ff + 4) = '42'::bigint) +(12 rows) + +explain (costs off) + select * from ec1, + (select ff + 1 as x from + (select ff + 2 as ff from ec1 + union all + select ff + 3 as ff from ec1) ss0 + union all + select ff + 4 as x from ec1) as ss1, + (select ff + 1 as x from + (select ff + 2 as ff from ec1 + union all + select ff + 3 as ff from ec1) ss0 + union all + select ff + 4 as x from ec1) as ss2 + where ss1.x = ec1.f1 and ss1.x = ss2.x and ec1.ff = 42::int8; + QUERY PLAN +--------------------------------------------------------------------- + Nested Loop + -> Nested Loop + -> Index Scan using ec1_pkey on ec1 + Index Cond: (ff = '42'::bigint) + -> Append + -> Index Scan using ec1_expr2 on ec1 ec1_1 + Index Cond: (((ff + 2) + 1) = ec1.f1) + -> Index Scan using ec1_expr3 on ec1 ec1_2 + Index Cond: (((ff + 3) + 1) = ec1.f1) + -> Index Scan using ec1_expr4 on ec1 ec1_3 + Index Cond: ((ff + 4) = ec1.f1) + -> Append + -> Index Scan using ec1_expr2 on ec1 ec1_4 + Index Cond: (((ff + 2) + 1) = (((ec1_1.ff + 2) + 1))) + -> Index Scan using ec1_expr3 on ec1 ec1_5 + Index Cond: (((ff + 3) + 1) = (((ec1_1.ff + 2) + 1))) + -> Index Scan using ec1_expr4 on ec1 ec1_6 + Index Cond: ((ff + 4) = (((ec1_1.ff + 2) + 1))) +(18 rows) + +-- let's try that as a mergejoin +set enable_mergejoin = on; +set enable_nestloop = off; +explain (costs off) + select * from ec1, + (select ff + 1 as x from + (select ff + 2 as ff from ec1 + union all + select ff + 3 as ff from ec1) ss0 + union all + select ff + 4 as x from ec1) as ss1, + (select ff + 1 as x from + (select ff + 2 as ff from ec1 + union all + select ff + 3 as ff from ec1) ss0 + union all + select ff + 4 as x from ec1) as ss2 + where ss1.x = ec1.f1 and ss1.x = ss2.x and ec1.ff = 42::int8; + QUERY PLAN +----------------------------------------------------------------- + Merge Join + Merge Cond: ((((ec1_4.ff + 2) + 1)) = (((ec1_1.ff + 2) + 1))) + -> Merge Append + Sort Key: (((ec1_4.ff + 2) + 1)) + -> Index Scan using ec1_expr2 on ec1 ec1_4 + -> Index Scan using ec1_expr3 on ec1 ec1_5 + -> Index Scan using ec1_expr4 on ec1 ec1_6 + -> Materialize + -> Merge Join + Merge Cond: ((((ec1_1.ff + 2) + 1)) = ec1.f1) + -> Merge Append + Sort Key: (((ec1_1.ff + 2) + 1)) + -> Index Scan using ec1_expr2 on ec1 ec1_1 + -> Index Scan using ec1_expr3 on ec1 ec1_2 + -> Index Scan using ec1_expr4 on ec1 ec1_3 + -> Sort + Sort Key: ec1.f1 USING < + -> Index Scan using ec1_pkey on ec1 + Index Cond: (ff = '42'::bigint) +(19 rows) + +-- check partially indexed scan +set enable_nestloop = on; +set enable_mergejoin = off; +drop index ec1_expr3; +explain (costs off) + select * from ec1, + (select ff + 1 as x from + (select ff + 2 as ff from ec1 + union all + select ff + 3 as ff from ec1) ss0 + union all + select ff + 4 as x from ec1) as ss1 + where ss1.x = ec1.f1 and ec1.ff = 42::int8; + QUERY PLAN +----------------------------------------------------- + Nested Loop + -> Index Scan using ec1_pkey on ec1 + Index Cond: (ff = '42'::bigint) + -> Append + -> Index Scan using ec1_expr2 on ec1 ec1_1 + Index Cond: (((ff + 2) + 1) = ec1.f1) + -> Seq Scan on ec1 ec1_2 + Filter: (((ff + 3) + 1) = ec1.f1) + -> Index Scan using ec1_expr4 on ec1 ec1_3 + Index Cond: ((ff + 4) = ec1.f1) +(10 rows) + +-- let's try that as a mergejoin +set enable_mergejoin = on; +set enable_nestloop = off; +explain (costs off) + select * from ec1, + (select ff + 1 as x from + (select ff + 2 as ff from ec1 + union all + select ff + 3 as ff from ec1) ss0 + union all + select ff + 4 as x from ec1) as ss1 + where ss1.x = ec1.f1 and ec1.ff = 42::int8; + QUERY PLAN +----------------------------------------------------- + Merge Join + Merge Cond: ((((ec1_1.ff + 2) + 1)) = ec1.f1) + -> Merge Append + Sort Key: (((ec1_1.ff + 2) + 1)) + -> Index Scan using ec1_expr2 on ec1 ec1_1 + -> Sort + Sort Key: (((ec1_2.ff + 3) + 1)) + -> Seq Scan on ec1 ec1_2 + -> Index Scan using ec1_expr4 on ec1 ec1_3 + -> Sort + Sort Key: ec1.f1 USING < + -> Index Scan using ec1_pkey on ec1 + Index Cond: (ff = '42'::bigint) +(13 rows) + +-- check effects of row-level security +set enable_nestloop = on; +set enable_mergejoin = off; +alter table ec1 enable row level security; +create policy p1 on ec1 using (f1 < '5'::int8alias1); +create user regress_user_ectest; +grant select on ec0 to regress_user_ectest; +grant select on ec1 to regress_user_ectest; +-- without any RLS, we'll treat {a.ff, b.ff, 43} as an EquivalenceClass +explain (costs off) + select * from ec0 a, ec1 b + where a.ff = b.ff and a.ff = 43::bigint::int8alias1; + QUERY PLAN +--------------------------------------------- + Nested Loop + -> Index Scan using ec0_pkey on ec0 a + Index Cond: (ff = '43'::int8alias1) + -> Index Scan using ec1_pkey on ec1 b + Index Cond: (ff = '43'::int8alias1) +(5 rows) + +set session authorization regress_user_ectest; +-- with RLS active, the non-leakproof a.ff = 43 clause is not treated +-- as a suitable source for an EquivalenceClass; currently, this is true +-- even though the RLS clause has nothing to do directly with the EC +explain (costs off) + select * from ec0 a, ec1 b + where a.ff = b.ff and a.ff = 43::bigint::int8alias1; + QUERY PLAN +--------------------------------------------- + Nested Loop + -> Index Scan using ec0_pkey on ec0 a + Index Cond: (ff = '43'::int8alias1) + -> Index Scan using ec1_pkey on ec1 b + Index Cond: (ff = a.ff) + Filter: (f1 < '5'::int8alias1) +(6 rows) + +reset session authorization; +revoke select on ec0 from regress_user_ectest; +revoke select on ec1 from regress_user_ectest; +drop user regress_user_ectest; +-- check that X=X is converted to X IS NOT NULL when appropriate +explain (costs off) + select * from tenk1 where unique1 = unique1 and unique2 = unique2; + QUERY PLAN +------------------------------------------------------------- + Seq Scan on tenk1 + Filter: ((unique1 IS NOT NULL) AND (unique2 IS NOT NULL)) +(2 rows) + +-- this could be converted, but isn't at present +explain (costs off) + select * from tenk1 where unique1 = unique1 or unique2 = unique2; + QUERY PLAN +-------------------------------------------------------- + Seq Scan on tenk1 + Filter: ((unique1 = unique1) OR (unique2 = unique2)) +(2 rows) + +-- check that we recognize equivalence with dummy domains in the way +create temp table undername (f1 name, f2 int); +create temp view overview as + select f1::information_schema.sql_identifier as sqli, f2 from undername; +explain (costs off) -- this should not require a sort + select * from overview where sqli = 'foo' order by sqli; + QUERY PLAN +------------------------------ + Seq Scan on undername + Filter: (f1 = 'foo'::name) +(2 rows) + |