src/test/regress/expected/indexjoin_optimizer.out

CREATE TABLE my_tt_agg_small (
    symbol character(16),
    event_ts bigint,
    trade_price numeric,
    trade_volume bigint
) DISTRIBUTED BY (symbol);
CREATE TABLE my_tq_agg_small (
    ets bigint,
    sym character varying(16),
    bid_price numeric,
    ask_price numeric,
    end_ts bigint
) DISTRIBUTED BY (ets);
COPY my_tt_agg_small (symbol, event_ts, trade_price, trade_volume) FROM stdin;
COPY my_tq_agg_small (ets, sym, bid_price, ask_price, end_ts) FROM stdin;
CREATE INDEX my_tq_agg_small_ets_end_ts_ix ON my_tq_agg_small USING btree (ets, end_ts);
analyze my_tq_agg_small;
analyze my_tt_agg_small;
set optimizer_enable_indexjoin=on;
set optimizer_nestloop_factor = 1.0;
-- force_explain
EXPLAIN 
SELECT (tt.event_ts / 100000) / 5 * 5 as fivemin, COUNT(*)
FROM my_tt_agg_small tt, my_tq_agg_small tq
WHERE tq.sym = tt.symbol AND
      tt.event_ts >= tq.ets AND
      tt.event_ts <  tq.end_ts
GROUP BY 1
ORDER BY 1 asc ;
                                                                        QUERY PLAN                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice3; segments: 3)  (cost=0.00..878.47 rows=413 width=16)
   Merge Key: ((((tt.event_ts / 100000) / 5) * 5))
   ->  Finalize GroupAggregate  (cost=0.00..878.44 rows=138 width=16)
         Group Key: ((((tt.event_ts / 100000) / 5) * 5))
         ->  Sort  (cost=0.00..878.44 rows=138 width=16)
               Sort Key: ((((tt.event_ts / 100000) / 5) * 5))
               ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..878.35 rows=138 width=16)
                     Hash Key: ((((tt.event_ts / 100000) / 5) * 5))
                     ->  Streaming Partial HashAggregate  (cost=0.00..878.34 rows=138 width=16)
                           Group Key: (((tt.event_ts / 100000) / 5) * 5)
                           ->  Hash Join  (cost=0.00..865.64 rows=94594 width=8)
                                 Hash Cond: ((tq.sym)::text = (tt.symbol)::text)
                                 Join Filter: ((tt.event_ts >= tq.ets) AND (tt.event_ts < tq.end_ts))
                                 ->  Seq Scan on my_tq_agg_small tq  (cost=0.00..431.02 rows=676 width=20)
                                 ->  Hash  (cost=431.30..431.30 rows=420 width=25)
                                       ->  Broadcast Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.30 rows=420 width=25)
                                             ->  Seq Scan on my_tt_agg_small tt  (cost=0.00..431.01 rows=140 width=25)
 Optimizer: Pivotal Optimizer (GPORCA) version 3.83.0
(18 rows)

SELECT (tt.event_ts / 100000) / 5 * 5 as fivemin, COUNT(*)
FROM my_tt_agg_small tt, my_tq_agg_small tq
WHERE tq.sym = tt.symbol AND
      tt.event_ts >= tq.ets AND
      tt.event_ts <  tq.end_ts
GROUP BY 1
ORDER BY 1 asc ;
   fivemin    | count 
--------------+-------
 201011261015 |     2
 201011261045 |     1
 201011261110 |     1
 201011261125 |     1
 201011261240 |     1
 201011261245 |     1
 201011261315 |     2
 201011261320 |     3
(8 rows)

set optimizer_enable_hashjoin = off;
set enable_hashjoin=off;
set enable_seqscan=off;
set enable_mergejoin=off;
set enable_nestloop=on;
set enable_indexscan=on;
-- start_ignore
-- Known_opt_diff: OPT-929
-- end_ignore
-- force_explain
set optimizer_segments = 2;
set optimizer_nestloop_factor = 1.0;
EXPLAIN 
SELECT (tt.event_ts / 100000) / 5 * 5 as fivemin, COUNT(*)
FROM my_tt_agg_small tt, my_tq_agg_small tq
WHERE tq.sym = tt.symbol AND
      tt.event_ts >= tq.ets AND
      tt.event_ts <  tq.end_ts
GROUP BY 1
ORDER BY 1 asc ;
                                                                                                      QUERY PLAN                                                                                                       
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice3; segments: 3)  (cost=0.00..1359.12 rows=413 width=16)
   Merge Key: ((((tt.event_ts / 100000) / 5) * 5))
   ->  Finalize GroupAggregate  (cost=0.00..1359.09 rows=138 width=16)
         Group Key: ((((tt.event_ts / 100000) / 5) * 5))
         ->  Sort  (cost=0.00..1359.08 rows=138 width=16)
               Sort Key: ((((tt.event_ts / 100000) / 5) * 5))
               ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..1358.94 rows=138 width=16)
                     Hash Key: ((((tt.event_ts / 100000) / 5) * 5))
                     ->  Streaming Partial HashAggregate  (cost=0.00..1358.93 rows=138 width=16)
                           Group Key: (((tt.event_ts / 100000) / 5) * 5)
                           ->  Nested Loop  (cost=0.00..1339.87 rows=94594 width=8)
                                 Join Filter: (((tq.sym)::bpchar = tt.symbol) AND (tt.event_ts >= tq.ets) AND (tt.event_ts < tq.end_ts))
                                 ->  Broadcast Motion 3:3  (slice3; segments: 3)  (cost=0.00..431.29 rows=280 width=25)
                                       ->  Seq Scan on my_tt_agg_small tt  (cost=0.00..431.01 rows=140 width=25)
                                 ->  Seq Scan on my_tq_agg_small tq  (cost=0.00..431.03 rows=676 width=20)
 Optimizer: Pivotal Optimizer (GPORCA)
(16 rows)

reset optimizer_segments;
reset optimizer_nestloop_factor;
SELECT (tt.event_ts / 100000) / 5 * 5 as fivemin, COUNT(*)
FROM my_tt_agg_small tt, my_tq_agg_small tq
WHERE tq.sym = tt.symbol AND
      tt.event_ts >= tq.ets AND
      tt.event_ts <  tq.end_ts
GROUP BY 1
ORDER BY 1 asc ;
   fivemin    | count 
--------------+-------
 201011261015 |     2
 201011261045 |     1
 201011261110 |     1
 201011261125 |     1
 201011261240 |     1
 201011261245 |     1
 201011261315 |     2
 201011261320 |     3
(8 rows)

-- Test Index Scan on CO table as the right tree of a NestLoop join.
create table no_index_table(fake_col1 int, fake_col2 int, fake_col3 int, a int, b int) distributed by (a, b);
insert into no_index_table values (1,1,1,1,1);
analyze no_index_table;
create table with_index_table(x int, y int) with (appendonly=true, orientation=column);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'x' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
create index with_index_table_index on with_index_table (x);
insert into with_index_table select i, 1 from generate_series(1, 20)i;
analyze with_index_table;
set enable_material to off;
set enable_seqscan to off;
set enable_mergejoin to off;
set enable_hashjoin to off;
set enable_nestloop to on;
set optimizer_enable_materialize to off;
set optimizer_enable_hashjoin to off;
explain (costs off)
SELECT * from with_index_table td JOIN no_index_table ro ON td.y = ro.a AND td.x = ro.b;
                          QUERY PLAN                           
---------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   ->  Nested Loop
         Join Filter: true
         ->  Redistribute Motion 3:3  (slice2; segments: 3)
               Hash Key: ro.b
               ->  Seq Scan on no_index_table ro
         ->  Bitmap Heap Scan on with_index_table td
               Recheck Cond: (x = ro.b)
               Filter: (y = ro.a)
               ->  Bitmap Index Scan on with_index_table_index
                     Index Cond: (x = ro.b)
 Optimizer: Pivotal Optimizer (GPORCA) version 3.88.1
(12 rows)

SELECT * from with_index_table td JOIN no_index_table ro ON td.y = ro.a AND td.x = ro.b;
 x | y | fake_col1 | fake_col2 | fake_col3 | a | b 
---+---+-----------+-----------+-----------+---+---
 1 | 1 |         1 |         1 |         1 | 1 | 1
(1 row)

reset all;