Re: New style of hash join proposal

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)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=5 width=244)
-> HashAggregate (cost=1.06..1.11 rows=5 width=4)
-> Seq Scan on int4_tbl b (cost=0.00..1.05 rows=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