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









---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Reply via email to