1-- 2-- Tests for the planner's "equivalence class" mechanism 3-- 4-- One thing that's not tested well during normal querying is the logic 5-- for handling "broken" ECs. This is because an EC can only become broken 6-- if its underlying btree operator family doesn't include a complete set 7-- of cross-type equality operators. There are not (and should not be) 8-- any such families built into Postgres; so we have to hack things up 9-- to create one. We do this by making two alias types that are really 10-- int8 (so we need no new C code) and adding only some operators for them 11-- into the standard integer_ops opfamily. 12create type int8alias1; 13create function int8alias1in(cstring) returns int8alias1 14 strict immutable language internal as 'int8in'; 15NOTICE: return type int8alias1 is only a shell 16create function int8alias1out(int8alias1) returns cstring 17 strict immutable language internal as 'int8out'; 18NOTICE: argument type int8alias1 is only a shell 19create type int8alias1 ( 20 input = int8alias1in, 21 output = int8alias1out, 22 like = int8 23); 24create type int8alias2; 25create function int8alias2in(cstring) returns int8alias2 26 strict immutable language internal as 'int8in'; 27NOTICE: return type int8alias2 is only a shell 28create function int8alias2out(int8alias2) returns cstring 29 strict immutable language internal as 'int8out'; 30NOTICE: argument type int8alias2 is only a shell 31create type int8alias2 ( 32 input = int8alias2in, 33 output = int8alias2out, 34 like = int8 35); 36create cast (int8 as int8alias1) without function; 37create cast (int8 as int8alias2) without function; 38create cast (int8alias1 as int8) without function; 39create cast (int8alias2 as int8) without function; 40create function int8alias1eq(int8alias1, int8alias1) returns bool 41 strict immutable language internal as 'int8eq'; 42create operator = ( 43 procedure = int8alias1eq, 44 leftarg = int8alias1, rightarg = int8alias1, 45 commutator = =, 46 restrict = eqsel, join = eqjoinsel, 47 merges 48); 49alter operator family integer_ops using btree add 50 operator 3 = (int8alias1, int8alias1); 51create function int8alias2eq(int8alias2, int8alias2) returns bool 52 strict immutable language internal as 'int8eq'; 53create operator = ( 54 procedure = int8alias2eq, 55 leftarg = int8alias2, rightarg = int8alias2, 56 commutator = =, 57 restrict = eqsel, join = eqjoinsel, 58 merges 59); 60alter operator family integer_ops using btree add 61 operator 3 = (int8alias2, int8alias2); 62create function int8alias1eq(int8, int8alias1) returns bool 63 strict immutable language internal as 'int8eq'; 64create operator = ( 65 procedure = int8alias1eq, 66 leftarg = int8, rightarg = int8alias1, 67 restrict = eqsel, join = eqjoinsel, 68 merges 69); 70alter operator family integer_ops using btree add 71 operator 3 = (int8, int8alias1); 72create function int8alias1eq(int8alias1, int8alias2) returns bool 73 strict immutable language internal as 'int8eq'; 74create operator = ( 75 procedure = int8alias1eq, 76 leftarg = int8alias1, rightarg = int8alias2, 77 restrict = eqsel, join = eqjoinsel, 78 merges 79); 80alter operator family integer_ops using btree add 81 operator 3 = (int8alias1, int8alias2); 82create function int8alias1lt(int8alias1, int8alias1) returns bool 83 strict immutable language internal as 'int8lt'; 84create operator < ( 85 procedure = int8alias1lt, 86 leftarg = int8alias1, rightarg = int8alias1 87); 88alter operator family integer_ops using btree add 89 operator 1 < (int8alias1, int8alias1); 90create function int8alias1cmp(int8, int8alias1) returns int 91 strict immutable language internal as 'btint8cmp'; 92alter operator family integer_ops using btree add 93 function 1 int8alias1cmp (int8, int8alias1); 94create table ec0 (ff int8 primary key, f1 int8, f2 int8); 95create table ec1 (ff int8 primary key, f1 int8alias1, f2 int8alias2); 96create table ec2 (xf int8 primary key, x1 int8alias1, x2 int8alias2); 97-- for the moment we only want to look at nestloop plans 98set enable_hashjoin = off; 99set enable_mergejoin = off; 100-- 101-- Note that for cases where there's a missing operator, we don't care so 102-- much whether the plan is ideal as that we don't fail or generate an 103-- outright incorrect plan. 104-- 105explain (costs off) 106 select * from ec0 where ff = f1 and f1 = '42'::int8; 107 QUERY PLAN 108----------------------------------- 109 Index Scan using ec0_pkey on ec0 110 Index Cond: (ff = '42'::bigint) 111 Filter: (f1 = '42'::bigint) 112(3 rows) 113 114explain (costs off) 115 select * from ec0 where ff = f1 and f1 = '42'::int8alias1; 116 QUERY PLAN 117--------------------------------------- 118 Index Scan using ec0_pkey on ec0 119 Index Cond: (ff = '42'::int8alias1) 120 Filter: (f1 = '42'::int8alias1) 121(3 rows) 122 123explain (costs off) 124 select * from ec1 where ff = f1 and f1 = '42'::int8alias1; 125 QUERY PLAN 126--------------------------------------- 127 Index Scan using ec1_pkey on ec1 128 Index Cond: (ff = '42'::int8alias1) 129 Filter: (f1 = '42'::int8alias1) 130(3 rows) 131 132explain (costs off) 133 select * from ec1 where ff = f1 and f1 = '42'::int8alias2; 134 QUERY PLAN 135--------------------------------------------------- 136 Seq Scan on ec1 137 Filter: ((ff = f1) AND (f1 = '42'::int8alias2)) 138(2 rows) 139 140explain (costs off) 141 select * from ec1, ec2 where ff = x1 and ff = '42'::int8; 142 QUERY PLAN 143------------------------------------------------------------------- 144 Nested Loop 145 Join Filter: (ec1.ff = ec2.x1) 146 -> Index Scan using ec1_pkey on ec1 147 Index Cond: ((ff = '42'::bigint) AND (ff = '42'::bigint)) 148 -> Seq Scan on ec2 149(5 rows) 150 151explain (costs off) 152 select * from ec1, ec2 where ff = x1 and ff = '42'::int8alias1; 153 QUERY PLAN 154--------------------------------------------- 155 Nested Loop 156 -> Index Scan using ec1_pkey on ec1 157 Index Cond: (ff = '42'::int8alias1) 158 -> Seq Scan on ec2 159 Filter: (x1 = '42'::int8alias1) 160(5 rows) 161 162explain (costs off) 163 select * from ec1, ec2 where ff = x1 and '42'::int8 = x1; 164 QUERY PLAN 165----------------------------------------- 166 Nested Loop 167 Join Filter: (ec1.ff = ec2.x1) 168 -> Index Scan using ec1_pkey on ec1 169 Index Cond: (ff = '42'::bigint) 170 -> Seq Scan on ec2 171 Filter: ('42'::bigint = x1) 172(6 rows) 173 174explain (costs off) 175 select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias1; 176 QUERY PLAN 177--------------------------------------------- 178 Nested Loop 179 -> Index Scan using ec1_pkey on ec1 180 Index Cond: (ff = '42'::int8alias1) 181 -> Seq Scan on ec2 182 Filter: (x1 = '42'::int8alias1) 183(5 rows) 184 185explain (costs off) 186 select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias2; 187 QUERY PLAN 188----------------------------------------- 189 Nested Loop 190 -> Seq Scan on ec2 191 Filter: (x1 = '42'::int8alias2) 192 -> Index Scan using ec1_pkey on ec1 193 Index Cond: (ff = ec2.x1) 194(5 rows) 195 196create unique index ec1_expr1 on ec1((ff + 1)); 197create unique index ec1_expr2 on ec1((ff + 2 + 1)); 198create unique index ec1_expr3 on ec1((ff + 3 + 1)); 199create unique index ec1_expr4 on ec1((ff + 4)); 200explain (costs off) 201 select * from ec1, 202 (select ff + 1 as x from 203 (select ff + 2 as ff from ec1 204 union all 205 select ff + 3 as ff from ec1) ss0 206 union all 207 select ff + 4 as x from ec1) as ss1 208 where ss1.x = ec1.f1 and ec1.ff = 42::int8; 209 QUERY PLAN 210----------------------------------------------------- 211 Nested Loop 212 -> Index Scan using ec1_pkey on ec1 213 Index Cond: (ff = '42'::bigint) 214 -> Append 215 -> Index Scan using ec1_expr2 on ec1 ec1_1 216 Index Cond: (((ff + 2) + 1) = ec1.f1) 217 -> Index Scan using ec1_expr3 on ec1 ec1_2 218 Index Cond: (((ff + 3) + 1) = ec1.f1) 219 -> Index Scan using ec1_expr4 on ec1 ec1_3 220 Index Cond: ((ff + 4) = ec1.f1) 221(10 rows) 222 223explain (costs off) 224 select * from ec1, 225 (select ff + 1 as x from 226 (select ff + 2 as ff from ec1 227 union all 228 select ff + 3 as ff from ec1) ss0 229 union all 230 select ff + 4 as x from ec1) as ss1 231 where ss1.x = ec1.f1 and ec1.ff = 42::int8 and ec1.ff = ec1.f1; 232 QUERY PLAN 233------------------------------------------------------------------- 234 Nested Loop 235 Join Filter: ((((ec1_1.ff + 2) + 1)) = ec1.f1) 236 -> Index Scan using ec1_pkey on ec1 237 Index Cond: ((ff = '42'::bigint) AND (ff = '42'::bigint)) 238 Filter: (ff = f1) 239 -> Append 240 -> Index Scan using ec1_expr2 on ec1 ec1_1 241 Index Cond: (((ff + 2) + 1) = '42'::bigint) 242 -> Index Scan using ec1_expr3 on ec1 ec1_2 243 Index Cond: (((ff + 3) + 1) = '42'::bigint) 244 -> Index Scan using ec1_expr4 on ec1 ec1_3 245 Index Cond: ((ff + 4) = '42'::bigint) 246(12 rows) 247 248explain (costs off) 249 select * from ec1, 250 (select ff + 1 as x from 251 (select ff + 2 as ff from ec1 252 union all 253 select ff + 3 as ff from ec1) ss0 254 union all 255 select ff + 4 as x from ec1) as ss1, 256 (select ff + 1 as x from 257 (select ff + 2 as ff from ec1 258 union all 259 select ff + 3 as ff from ec1) ss0 260 union all 261 select ff + 4 as x from ec1) as ss2 262 where ss1.x = ec1.f1 and ss1.x = ss2.x and ec1.ff = 42::int8; 263 QUERY PLAN 264--------------------------------------------------------------------- 265 Nested Loop 266 -> Nested Loop 267 -> Index Scan using ec1_pkey on ec1 268 Index Cond: (ff = '42'::bigint) 269 -> Append 270 -> Index Scan using ec1_expr2 on ec1 ec1_1 271 Index Cond: (((ff + 2) + 1) = ec1.f1) 272 -> Index Scan using ec1_expr3 on ec1 ec1_2 273 Index Cond: (((ff + 3) + 1) = ec1.f1) 274 -> Index Scan using ec1_expr4 on ec1 ec1_3 275 Index Cond: ((ff + 4) = ec1.f1) 276 -> Append 277 -> Index Scan using ec1_expr2 on ec1 ec1_4 278 Index Cond: (((ff + 2) + 1) = (((ec1_1.ff + 2) + 1))) 279 -> Index Scan using ec1_expr3 on ec1 ec1_5 280 Index Cond: (((ff + 3) + 1) = (((ec1_1.ff + 2) + 1))) 281 -> Index Scan using ec1_expr4 on ec1 ec1_6 282 Index Cond: ((ff + 4) = (((ec1_1.ff + 2) + 1))) 283(18 rows) 284 285-- let's try that as a mergejoin 286set enable_mergejoin = on; 287set enable_nestloop = off; 288explain (costs off) 289 select * from ec1, 290 (select ff + 1 as x from 291 (select ff + 2 as ff from ec1 292 union all 293 select ff + 3 as ff from ec1) ss0 294 union all 295 select ff + 4 as x from ec1) as ss1, 296 (select ff + 1 as x from 297 (select ff + 2 as ff from ec1 298 union all 299 select ff + 3 as ff from ec1) ss0 300 union all 301 select ff + 4 as x from ec1) as ss2 302 where ss1.x = ec1.f1 and ss1.x = ss2.x and ec1.ff = 42::int8; 303 QUERY PLAN 304----------------------------------------------------------------- 305 Merge Join 306 Merge Cond: ((((ec1_4.ff + 2) + 1)) = (((ec1_1.ff + 2) + 1))) 307 -> Merge Append 308 Sort Key: (((ec1_4.ff + 2) + 1)) 309 -> Index Scan using ec1_expr2 on ec1 ec1_4 310 -> Index Scan using ec1_expr3 on ec1 ec1_5 311 -> Index Scan using ec1_expr4 on ec1 ec1_6 312 -> Materialize 313 -> Merge Join 314 Merge Cond: ((((ec1_1.ff + 2) + 1)) = ec1.f1) 315 -> Merge Append 316 Sort Key: (((ec1_1.ff + 2) + 1)) 317 -> Index Scan using ec1_expr2 on ec1 ec1_1 318 -> Index Scan using ec1_expr3 on ec1 ec1_2 319 -> Index Scan using ec1_expr4 on ec1 ec1_3 320 -> Sort 321 Sort Key: ec1.f1 USING < 322 -> Index Scan using ec1_pkey on ec1 323 Index Cond: (ff = '42'::bigint) 324(19 rows) 325 326-- check partially indexed scan 327set enable_nestloop = on; 328set enable_mergejoin = off; 329drop index ec1_expr3; 330explain (costs off) 331 select * from ec1, 332 (select ff + 1 as x from 333 (select ff + 2 as ff from ec1 334 union all 335 select ff + 3 as ff from ec1) ss0 336 union all 337 select ff + 4 as x from ec1) as ss1 338 where ss1.x = ec1.f1 and ec1.ff = 42::int8; 339 QUERY PLAN 340----------------------------------------------------- 341 Nested Loop 342 -> Index Scan using ec1_pkey on ec1 343 Index Cond: (ff = '42'::bigint) 344 -> Append 345 -> Index Scan using ec1_expr2 on ec1 ec1_1 346 Index Cond: (((ff + 2) + 1) = ec1.f1) 347 -> Seq Scan on ec1 ec1_2 348 Filter: (((ff + 3) + 1) = ec1.f1) 349 -> Index Scan using ec1_expr4 on ec1 ec1_3 350 Index Cond: ((ff + 4) = ec1.f1) 351(10 rows) 352 353-- let's try that as a mergejoin 354set enable_mergejoin = on; 355set enable_nestloop = off; 356explain (costs off) 357 select * from ec1, 358 (select ff + 1 as x from 359 (select ff + 2 as ff from ec1 360 union all 361 select ff + 3 as ff from ec1) ss0 362 union all 363 select ff + 4 as x from ec1) as ss1 364 where ss1.x = ec1.f1 and ec1.ff = 42::int8; 365 QUERY PLAN 366----------------------------------------------------- 367 Merge Join 368 Merge Cond: ((((ec1_1.ff + 2) + 1)) = ec1.f1) 369 -> Merge Append 370 Sort Key: (((ec1_1.ff + 2) + 1)) 371 -> Index Scan using ec1_expr2 on ec1 ec1_1 372 -> Sort 373 Sort Key: (((ec1_2.ff + 3) + 1)) 374 -> Seq Scan on ec1 ec1_2 375 -> Index Scan using ec1_expr4 on ec1 ec1_3 376 -> Sort 377 Sort Key: ec1.f1 USING < 378 -> Index Scan using ec1_pkey on ec1 379 Index Cond: (ff = '42'::bigint) 380(13 rows) 381 382-- check effects of row-level security 383set enable_nestloop = on; 384set enable_mergejoin = off; 385alter table ec1 enable row level security; 386create policy p1 on ec1 using (f1 < '5'::int8alias1); 387create user regress_user_ectest; 388grant select on ec0 to regress_user_ectest; 389grant select on ec1 to regress_user_ectest; 390-- without any RLS, we'll treat {a.ff, b.ff, 43} as an EquivalenceClass 391explain (costs off) 392 select * from ec0 a, ec1 b 393 where a.ff = b.ff and a.ff = 43::bigint::int8alias1; 394 QUERY PLAN 395--------------------------------------------- 396 Nested Loop 397 -> Index Scan using ec0_pkey on ec0 a 398 Index Cond: (ff = '43'::int8alias1) 399 -> Index Scan using ec1_pkey on ec1 b 400 Index Cond: (ff = '43'::int8alias1) 401(5 rows) 402 403set session authorization regress_user_ectest; 404-- with RLS active, the non-leakproof a.ff = 43 clause is not treated 405-- as a suitable source for an EquivalenceClass; currently, this is true 406-- even though the RLS clause has nothing to do directly with the EC 407explain (costs off) 408 select * from ec0 a, ec1 b 409 where a.ff = b.ff and a.ff = 43::bigint::int8alias1; 410 QUERY PLAN 411--------------------------------------------- 412 Nested Loop 413 -> Index Scan using ec0_pkey on ec0 a 414 Index Cond: (ff = '43'::int8alias1) 415 -> Index Scan using ec1_pkey on ec1 b 416 Index Cond: (ff = a.ff) 417 Filter: (f1 < '5'::int8alias1) 418(6 rows) 419 420reset session authorization; 421revoke select on ec0 from regress_user_ectest; 422revoke select on ec1 from regress_user_ectest; 423drop user regress_user_ectest; 424