src/test/regress/expected/indexjoin_1.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=7250.73..7251.76 rows=412 width=16)
   Merge Key: fivemin
   ->  Sort  (cost=7250.73..7251.76 rows=206 width=16)
         Sort Key: partial_aggregation.unnamed_attr_1
         ->  HashAggregate  (cost=7227.69..7232.84 rows=206 width=16)
               Group By: "?column1?"
               ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=7210.18..7221.51 rows=206 width=16)
                     Hash Key: unnamed_attr_1
                     ->  HashAggregate  (cost=7210.18..7213.27 rows=206 width=16)
                           Group By: tt.event_ts / 100000 / 5 * 5
                           ->  Hash Join  (cost=29.30..6983.21 rows=22697 width=8)
                                 Hash Cond: tq.sym::bpchar = tt.symbol
                                 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..26.27 rows=1014 width=20)
                                 ->  Hash  (cost=18.80..18.80 rows=420 width=25)
                                       ->  Broadcast Motion 3:3  (slice1; segments: 3)  (cost=0.00..18.80 rows=420 width=25)
                                             ->  Seq Scan on my_tt_agg_small tt  (cost=0.00..6.20 rows=210 width=25)
(17 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=13721.17..13722.20 rows=412 width=16)
   Merge Key: fivemin
   ->  Sort  (cost=13721.17..13722.20 rows=206 width=16)
         Sort Key: partial_aggregation.unnamed_attr_1
         ->  HashAggregate  (cost=13698.13..13703.28 rows=206 width=16)
               Group By: "?column1?"
               ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=13680.62..13691.95 rows=206 width=16)
                     Hash Key: unnamed_attr_1
                     ->  HashAggregate  (cost=13680.62..13683.71 rows=206 width=16)
                           Group By: tt.event_ts / 100000 / 5 * 5
                           ->  Nested Loop  (cost=0.00..13453.65 rows=22697 width=8)
                                 Join Filter: tq.sym::bpchar = tt.symbol
                                 ->  Broadcast Motion 3:3  (slice1; segments: 3)  (cost=0.00..18.80 rows=420 width=25)
                                       ->  Seq Scan on my_tt_agg_small tt  (cost=0.00..6.20 rows=210 width=25)
                                 ->  Index Scan using my_tq_agg_small_ets_end_ts_ix on my_tq_agg_small tq  (cost=0.00..11.65 rows=113 width=20)
                                       Index Cond: tt.event_ts >= tq.ets AND tt.event_ts < tq.end_ts
 Settings:  enable_hashjoin=off; enable_indexscan=on; enable_mergejoin=off; enable_nestloop=on; enable_seqscan=off; optimizer_segments=2
(17 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)

set optimizer_enable_hashjoin = on;