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