Re: Getting different number of results when using hashjoin on/off
От | Mario Weilguni |
---|---|
Тема | Re: Getting different number of results when using hashjoin on/off |
Дата | |
Msg-id | FA095C015271B64E99B197937712FD020E4B059D@freedom.grz.icomedias.com обсуждение исходный текст |
Ответ на | Getting different number of results when using hashjoin on/off (Mario Weilguni <mweilguni@sime.com>) |
Ответы |
Re: Getting different number of results when using hashjoin on/off
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-hackers |
Thanks Tom for you quick answer! No, I'm using 8.1.0, and tried it on different machines, always the same results. SELECT version(); PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 20040623 (Gentoo Hardened Linux 3.3.4-r1, ssp-3.3.2-2,pie-8.7.6) Best regards,Mario Weilguni icomedias - Digitale Kommunikation ------------------------------------------------------------------------ Mario Weilguni, Forschung und Entwicklung mario.weilguni@icomedias.com, http://www.icomedias.com/ icomedias Österreich Systemhaus GmbH: 8020 Graz, Entenplatz 1 Tel: +43 (316) 721.671-272, Fax: -103 icomedias Deutschland Systemhaus GmbH: 10969 Berlin, Alexandrinenstraße 2-3 Tel: +49 (30) 695.399-272, Fax: -103 -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Monday, November 28, 2005 5:20 PM To: Mario Weilguni Cc: Mario Weilguni; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Getting different number of results when using hashjoin on/off "Mario Weilguni" <mario.weilguni@icomedias.com> 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.unique2and 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.15rows=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.544rows=0 loops=10000) -> Bitmap Index Scan on tenk1_hundred (cost=0.00..2.34rows=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) (actualtime=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.35rows=100 width=0) (actual time=0.278..0.278 rows=100 loops=10000) IndexCond: (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
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: Help: 8.0.3 Vacuum of an empty table never completes ...
Следующее
От: James RobinsonДата:
Сообщение: Re: Help: 8.0.3 Vacuum of an empty table never completes ...