"Mario Weilguni" <[EMAIL PROTECTED]> writes:
> 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):
Yeah, that looks more reasonable.
I tried to reproduce this, without any luck:
regression=# explain analyze select count(*) from tenk1 a where exists (select
1 from tenk1 b, tenk1 c where b.unique1=c.unique2 and b.hundred in (4,5) and
c.hundred=a.hundred);
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3879742.37..3879742.38 rows=1 width=0) (actual
time=46579.077..46579.082 rows=1 loops=1)
-> Seq Scan on tenk1 a (cost=0.00..3879729.87 rows=5000 width=0) (actual
time=5.129..46528.208 rows=8500 loops=1)
Filter: (subplan)
SubPlan
-> Hash Join (cost=229.20..546.66 rows=2 width=0) (actual
time=4.569..4.569 rows=1 loops=10000)
Hash Cond: ("outer".unique1 = "inner".unique2)
-> Bitmap Heap Scan on tenk1 b (cost=4.69..321.15 rows=196
width=4) (actual time=0.947..1.698 rows=90 loops=10000)
Recheck Cond: ((hundred = 4) OR (hundred = 5))
-> BitmapOr (cost=4.69..4.69 rows=197 width=0) (actual
time=0.544..0.544 rows=0 loops=10000)
-> Bitmap Index Scan on tenk1_hundred
(cost=0.00..2.34 rows=98 width=0) (actual time=0.271..0.271 rows=100
loops=10000)
Index Cond: (hundred = 4)
-> Bitmap Index Scan on tenk1_hundred
(cost=0.00..2.34 rows=98 width=0) (actual time=0.262..0.262 rows=100
loops=10000)
Index Cond: (hundred = 5)
-> Hash (cost=224.26..224.26 rows=100 width=4) (actual
time=2.370..2.370 rows=100 loops=10000)
-> Bitmap Heap Scan on tenk1 c (cost=2.35..224.26
rows=100 width=4) (actual time=0.492..1.616 rows=100 loops=10000)
Recheck Cond: (hundred = $0)
-> Bitmap Index Scan on tenk1_hundred
(cost=0.00..2.35 rows=100 width=0) (actual time=0.278..0.278 rows=100
loops=10000)
Index Cond: (hundred = $0)
Total runtime: 46584.654 ms
(19 rows)
(I'm not bothering with setting up an ltree index, since the question
of what index is being used shouldn't affect hashjoin's decision to
rescan or not.)
That's using 8.1 branch CVS tip, but there aren't any related bug fixes
since 8.1 release. We did have several bug fixes in the hash join code
during the 8.1 beta cycle though ... is it possible you are really
running an 8.1 beta and not 8.1.0?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
