pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gaetano Mendola <mendola@bigfoot.com>
To: Dennis Bjorklund <db@zigo.dhs.org>
Subject: Re: 7.4 vs 7.3 ( hash join issue )
Date: Wed, 22 Sep 2004 10:22:05 +0200
Message-ID: <4151362C.6070106@bigfoot.com> (raw)
In-Reply-To: <Pine.LNX.4.44.0409220844010.9559-100000@zigo.dhs.org>
References: <4150B096.1050904@bigfoot.com>
	<Pine.LNX.4.44.0409220844010.9559-100000@zigo.dhs.org>

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











view thread (13+ messages)  latest in thread

Message-ID: <4151362C.6070106@bigfoot.com>
Permalink:  ../4151362C.6070106@bigfoot.com/
Also on:    postgresql.org/message-id/4151362C.6070106@bigfoot.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mendola@bigfoot.com, db@zigo.dhs.org
  Subject: Re: 7.4 vs 7.3 ( hash join issue )
  In-Reply-To: <4151362C.6070106@bigfoot.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox