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

Yes. This is from a 8.0.3 (with slightly older and different data,
resulting in only 9 rows, but the rest is the same):
Index Scan using ben_uk3 on foo1 ben  (cost=0.00..73867.23 rows=863
width=27) (actual time=38.591..501.839 rows=9 loops=1)  Filter: (subplan)  SubPlan    ->  Hash Join  (cost=14.25..42.53
rows=1width=0) (actual 
time=0.284..0.284 rows=0 loops=1725)          Hash Cond: ("outer".id = "inner".str_id)          ->  Index Scan using
str_uk4on structure str 
(cost=0.00..27.91 rows=13 width=4) (actual time=0.765..4.043 rows=1
loops=112)                Index Cond: (path ~ '*.2330676.*'::lquery)          ->  Hash  (cost=14.23..14.23 rows=10
width=4)(actual 
time=0.012..0.012 rows=0 loops=1725)                ->  Index Scan using foo2_ben_id_key1 on foo2 bz
(cost=0.00..14.23 rows=10 width=4) (actual time=0.008..0.009 rows=1
loops=1725)                      Index Cond: (ben_id = $0)Total runtime: 501.980 ms

Best regards

P.s. sorry for the stupid quoting, I've to use Outlook....


Mario Weilguni <mweilguni@sime.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?


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate      subscribe-nomail command to
majordomo@postgresql.orgso that your      message can get through to the mailing list cleanly