aboutsummaryrefslogtreecommitdiff
path: root/src/test/regress/expected/equivclass.out
blob: 0391b8eec19fd71707b522bafa1f5df160133b27 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
--
-- 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
               ->  Materialize
                     ->  Sort
                           Sort Key: ec1.f1 USING <
                           ->  Index Scan using ec1_pkey on ec1
                                 Index Cond: (ff = '42'::bigint)
(20 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
   ->  Materialize
         ->  Sort
               Sort Key: ec1.f1 USING <
               ->  Index Scan using ec1_pkey on ec1
                     Index Cond: (ff = '42'::bigint)
(14 rows)