Support rewriting join plan when predicates are pushed down from outer stage

[navis]

TPCH-20

02:05:51.622 - -> INNER (supplier:10000:1.000 + nation:1:0.040)
02:05:51.871 - -- nation (R) is materialized (1 rows)
02:05:51.873 - --- selectivity 0.040 merged into supplier(10000:1.000 to 400:0.040)
02:05:51.873 - -- nation:1:0.040 (R) is merged into supplier (L) as filter on [S_NATIONKEY]
02:05:51.873 - -> INNER (partsupp:800000:1.000 + lineitem:541895:0.152)
02:05:51.873 - -- partsupp:800000:1.000 (L) (sort)
02:05:51.873 - -- lineitem:541895 (R) (sort)
02:05:51.875 - -> INNER (partsupp+lineitem:541895:0.182 + part:2127:0.011)
02:05:52.193 - -- part (R) is materialized (2127 rows)
02:05:52.194 - --- selectivity 0.011 merged into partsupp(800000:1.000 to 8508:0.011)
02:05:52.195 - --- assigning 'hashing' to partsupp:8508.. disabling sort-merge join
02:05:52.195 - --- compressed in filter [PS_PARTKEY:2127], 9,631 bytes into 7,449 bytes (22% reduction, 0 msec)
02:05:52.235 - --- partsupp:8508 (hash) is applied as filter to lineitem
02:05:52.236 - -- part:2127:0.011 (R) is merged into partsupp+lineitem (L) as filter on [PS_PARTKEY]
02:05:52.236 - -> INNER (supplier+nation:400:0.048 + partsupp+lineitem+part:3457:0.008)
02:05:52.237 - -- compressing ins filter with [8508] values
02:05:52.238 - --- compressed ins filter [0:[L_PARTKEY, L_SUPPKEY]], 22,393 bytes into 9,272 bytes (58% reduction, 1 msec)
02:05:52.239 - --- compressed ins filter [1:[L_PARTKEY, L_SUPPKEY]], 41,575 bytes into 31,383 bytes (24% reduction, 2 msec)
02:05:52.266 - Running 2-way join processing [partsupp, lineitem]
02:05:52.605 - >> INNER ([partsupp].[PS_PARTKEY, PS_SUPPKEY](hashed:8508/8508) <-- [lineitem].[d0, d1](stream:541895?)) ([PS_AVAILQTY, PS_PARTKEY, PS_SUPPKEY] + [d0, d1, a0])
02:05:52.620 - << INNER ([partsupp].[PS_PARTKEY, PS_SUPPKEY](hashed:8508/8508) + [lineitem].[d0, d1](stream:5843)), resulting 5843 rows ([PS_AVAILQTY, PS_PARTKEY, PS_SUPPKEY]+[d0, d1, a0]) : (15 msec)
02:05:52.621 - -- partsupp+lineitem+part (R) is materialized (4397 rows)
02:05:52.624 - --- selectivity 0.008 merged into supplier(400:0.048 to 63:0.008)
02:05:52.624 - -- partsupp+lineitem+part:3457:0.008 (R) is merged into supplier+nation (L) as filter on [S_SUPPKEY]
02:05:52.625 - --- compressed in filter [S_SUPPKEY:4397], 13,737 bytes into 7,060 bytes (48% reduction, 0 msec)
02:05:52.645 - {"success":true,"query/time":1236,"query/rows":186,"query/bytes":13405}  "select  S_NAME,  S_ADDRESS from  supplier, nation where  S_SUPPKEY in (    select      PS_SUPPKEY    from      partsupp    where      PS_PARTKEY in (        select          P_PARTKEY        from          part        where          P_NAME like 'forest%'        )      and PS_AVAILQTY > (        select (..222 more..) t          0.5 * sum(L_QUANTITY)        from          lineitem        where          L_PARTKEY = PS_PARTKEY          and L_SUPPKEY = PS_SUPPKEY          and L_SHIPDATE >= '1994-01-01'          and L_SHIPDATE < '1995-01-01'        )    )  and S_NATIONKEY = N_NATIONKEY  and N_NAME = 'CANADA' order by  S_NAME"