X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 04393329D95 for ; Wed, 22 Sep 2004 09:44:33 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 71194-03 for ; Wed, 22 Sep 2004 08:44:29 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 065E1329CDB for ; Wed, 22 Sep 2004 09:44:30 +0100 (BST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i8M8iQfi072946 for ; Wed, 22 Sep 2004 08:44:28 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i8M8M8gQ065875 for pgsql-performance@postgresql.org; Wed, 22 Sep 2004 08:22:08 GMT From: Gaetano Mendola X-Newsgroups: comp.databases.postgresql.performance Subject: Re: 7.4 vs 7.3 ( hash join issue ) Date: Wed, 22 Sep 2004 10:22:05 +0200 Organization: PYRENET Midi-pyrenees Provider Lines: 68 Message-ID: <4151362C.6070106@bigfoot.com> References: <4150B096.1050904@bigfoot.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: abuse@pyrenet.fr To: Dennis Bjorklund User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en In-Reply-To: X-Enigmail-Version: 0.86.1.0 X-Enigmail-Supports: pgp-inline, pgp-mime To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/212 X-Sequence-Number: 8340 Dennis Bjorklund wrote: > On Wed, 22 Sep 2004, Gaetano Mendola wrote: > > >>Now my question is why the 7.4 choose the hash join ? :-( > > > It looks to me that the marge join is faster because there wasn't really > anything to merge, it resulted in 0 rows. Maybe the hash join that is > choosen in 7.4 would have been faster had there been a couple of result > rows (just a guess). > > It would be interesting to compare the plans in 7.4 with and without > hash_join active and see what costs it estimates for a merge join compared > to a hash join. Here they are: hash_join = on QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=10.21..10.21 rows=1 width=24) (actual time=0.885..0.885 rows=0 loops=1) -> Sort (cost=10.21..10.21 rows=1 width=24) (actual time=0.880..0.880 rows=0 loops=1) Sort Key: e.id_evento -> Hash Join (cost=9.02..10.21 rows=1 width=24) (actual time=0.687..0.687 rows=0 loops=1) Hash Cond: ("outer".id_tipo_evento = "inner".id_tipo_evento) -> Seq Scan on lookup_tipo_evento le (cost=0.00..1.16 rows=16 width=32) (actual time=0.017..0.038 rows=16 loops=1) -> Hash (cost=9.02..9.02 rows=1 width=16) (actual time=0.212..0.212 rows=0 loops=1) -> Index Scan using t_evento_id_pratica_key on t_evento e (cost=0.00..9.02 rows=1 width=16) (actual time=0.208..0.208 rows=0 loops=1) Index Cond: (id_pratica = 5) Filter: (((id_tipo_evento)::text = '5'::text) OR ((id_tipo_evento)::text = '6'::text) OR ((id_tipo_evento)::text = '7'::text) OR ((id_tipo_evento)::text = '8'::text)) Total runtime: 1.244 ms (11 rows) hash_join = off QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=10.28..10.28 rows=1 width=24) (actual time=0.429..0.429 rows=0 loops=1) -> Sort (cost=10.28..10.28 rows=1 width=24) (actual time=0.425..0.425 rows=0 loops=1) Sort Key: e.id_evento -> Merge Join (cost=10.25..10.27 rows=1 width=24) (actual time=0.218..0.218 rows=0 loops=1) Merge Cond: ("outer".id_tipo_evento = "inner".id_tipo_evento) -> Sort (cost=9.02..9.02 rows=1 width=16) (actual time=0.214..0.214 rows=0 loops=1) Sort Key: e.id_tipo_evento -> Index Scan using t_evento_id_pratica_key on t_evento e (cost=0.00..9.02 rows=1 width=16) (actual time=0.110..0.110 rows=0 loops=1) Index Cond: (id_pratica = 5) Filter: (((id_tipo_evento)::text = '5'::text) OR ((id_tipo_evento)::text = '6'::text) OR ((id_tipo_evento)::text = '7'::text) OR ((id_tipo_evento)::text = '8'::text)) -> Sort (cost=1.22..1.23 rows=16 width=32) (never executed) Sort Key: le.id_tipo_evento -> Seq Scan on lookup_tipo_evento le (cost=0.00..1.16 rows=16 width=32) (never executed) Total runtime: 0.721 ms (14 rows) Regards Gaetano Mendola