Re: New style of hash join proposal

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> Gregory Stark <stark@enterprisedb.com> writes:
>> "Tom Lane" <tgl@sss.pgh.pa.us> writes:
>>> I already demonstrated that we could.
>
>> We seem to be talking past each other. The plan you showed is analogous but
>> using a plain old index scan.
>
> That's only because that seemed like the appropriate thing for the given
> case's statistics.  [ fiddles with example... ]
>
> regression=# explain select * from tenk1 a where thousand in (select f1 from int4_tbl b);
>                                         QUERY PLAN                                        
> ------------------------------------------------------------------------------------------
>  Nested Loop  (cost=5.39..198.81 rows=51 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)
>    ->  Bitmap Heap Scan on tenk1 a  (cost=4.33..39.41 rows=10 width=244)
>          Recheck Cond: (a.thousand = b.f1)
>          ->  Bitmap Index Scan on tenk1_thous_tenthous  (cost=0.00..4.33 rows=10 width=0)
>                Index Cond: (a.thousand = b.f1)
> (7 rows)

Sure, but that's still re-executing the bitmap index scan 51 times -- possibly
having to fetch the same records off disk repeatedly. Avoiding that is kind of
the point behind the hash join plan after all.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!