Skip site navigation (1) Skip section navigation (2)

Re: Getting different number of results when using hashjoin on/off

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Mario Weilguni <mweilguni(at)sime(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Getting different number of results when using hashjoin on/off
Date: 2005-11-28 15:11:31
Message-ID: 9936.1133190691@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Mario Weilguni <mweilguni(at)sime(dot)com> writes:
> The failing case is:
> ...
>    SubPlan
>      ->  Hash Join  (cost=8.47..19.46 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=21619)
>            Hash Cond: ("outer".id = "inner".str_id)
>            ->  Bitmap Heap Scan on structure str  (cost=2.02..12.92 rows=6 width=4) (actual time=0.100..30.095 rows=1 loops=1)
>                  Recheck Cond: (path ~ '142.2330445.2330598.2330676.*'::lquery)
>                  ->  Bitmap Index Scan on str_uk4  (cost=0.00..2.02 rows=6 width=0) (actual time=0.090..0.090 rows=1 loops=1)
>                        Index Cond: (path ~ '142.2330445.2330598.2330676.*'::lquery)
>            ->  Hash  (cost=6.43..6.43 rows=5 width=4) (actual time=0.032..0.032 rows=0 loops=1)
>                  ->  Bitmap Heap Scan on foo2 bz  (cost=2.02..6.43 rows=5 width=4) (actual time=0.025..0.025 rows=0 loops=1)
>                        Recheck Cond: (bid = $0)
>                        ->  Bitmap Index Scan on foo2_bid_key1  (cost=0.00..2.02 rows=5 width=0) (actual time=0.021..0.021 rows=0 loops=1)
>                              Index Cond: (bid = $0)

Hmm, I wonder why the hash join's input nodes are showing "loops=1" ...
the hash depends on the subplan parameter $0 so it needs to be
re-evaluated each time through.  It looks like that's not happening.
Do you have the corresponding results from 8.0 --- if so, what do
the loop counts look like?

			regards, tom lane

In response to

pgsql-hackers by date

Next:From: James RobinsonDate: 2005-11-28 16:04:39
Subject: Help: 8.0.3 Vacuum of an empty table never completes ...
Previous:From: Tom LaneDate: 2005-11-28 15:04:20
Subject: Re: Ipv6 and Postgresql 8.0.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group