Re: New style of hash join proposal

Gregory Stark <stark@enterprisedb.com> writes:
> "Tom Lane" <tgl@sss.pgh.pa.us> writes:
>> Please give an example of what you're talking about that you think we
>> can't do now.

> Note that we're doing a full sequential scan of "a" even though we've already
> finished hashing "b" and know full well which keys we'll need. If we have an
> index on "a" and "b" is sufficiently smaller than "a", as in this case, then
> we could do a bitmap index scan on "a" and pull out just those keys.

You mean like this?

regression=# explain select * from tenk1 a where unique1 in (select f1 from int4_tbl b);
   QUERY PLAN                                      
 
-------------------------------------------------------------------------------------Nested Loop  (cost=1.06..42.52
rows=5width=244)  ->  HashAggregate  (cost=1.06..1.11 rows=5 width=4)        ->  Seq Scan on int4_tbl b
(cost=0.00..1.05rows=5 width=4)  ->  Index Scan using tenk1_unique1 on tenk1 a  (cost=0.00..8.27 rows=1 width=244)
 Index Cond: (a.unique1 = b.f1)
 
(5 rows)

In the example you give, this type of plan was rejected because there
were too many rows in the subplan (or so I suppose anyway; you might
play around with the cost constants and see what happens).
        regards, tom lane