Different runtime on the same query - Mailing list pgsql-general

From NMB Webmaster
Subject Different runtime on the same query
Date
Msg-id 3169712064webmaster@nmb.it
Whole thread Raw
Responses Re: Different runtime on the same query  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
I have two database quite similar, one has more tables than the other. I execute the same query on the two database,
hereis the query: 

EXPLAIN ANALYZE  SELECT E.id_prodotto, E.id_pagina FROM merchant M, e_catalogo E, editasito ES WHERE M.data_scadenza >=
TIMENOW()AND M.sospeso = 'f' AND M.id_categoria = '17' AND ES.id = E.id_pagina AND ES.id = (SELECT id FROM editasito
WHEREcod_pagina = 'e-vetrina' AND cod_lingua = '1') AND E.id_merchant = M.id_merchant AND E.visibile = 't'; 

As you can see below the query plan is quite different (and unfortunately also the total runtime!) and I can't
understandwhy. The only difference is that the table "e_catalogo" has 37659 rows on database #1 and 12427 rows on
database#2. My Postgres version is 7.2.3. Please help me , it is very important to me to tune this query. 

Database #1:

NOTICE:  QUERY PLAN:

Hash Join  (cost=9.32..5938.46 rows=392 width=24) (actual time=369.08..369.08 rows=0 loops=1)
  InitPlan
    ->  Seq Scan on editasito  (cost=0.00..22.09 rows=1 width=4) (actual time=0.24..0.92 rows=1 loops=1)
  ->  Hash Join  (cost=3.36..5915.83 rows=2354 width=20) (actual time=367.96..367.96 rows=0 loops=1)
        ->  Seq Scan on e_catalogo e  (cost=0.00..5694.75 rows=37660 width=16) (actual time=67.78..333.19 rows=37659
loops=1)
        ->  Hash  (cost=3.36..3.36 rows=1 width=4) (actual time=0.27..0.27 rows=0 loops=1)
              ->  Seq Scan on merchant m  (cost=0.00..3.36 rows=1 width=4) (actual time=0.27..0.27 rows=0 loops=1)
  ->  Hash  (cost=5.95..5.95 rows=1 width=4) (actual time=1.09..1.09 rows=0 loops=1)
        ->  Index Scan using id_editasito_ukey on editasito es  (cost=0.00..5.95 rows=1 width=4) (actual
time=1.07..1.08rows=1 loops=1) 
Total runtime: 369.30 msec

EXPLAIN


---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Database #2:

NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..21.71 rows=1 width=22) (actual time=0.53..0.53 rows=0 loops=1)
  InitPlan
    ->  Seq Scan on editasito  (cost=0.00..2.09 rows=1 width=2) (actual time=0.03..0.17 rows=1 loops=1)
  ->  Seq Scan on editasito es  (cost=0.00..2.08 rows=1 width=2) (actual time=0.25..0.27 rows=1 loops=1)
  ->  Materialize  (cost=19.61..19.61 rows=2 width=20) (actual time=0.26..0.26 rows=0 loops=1)
        ->  Nested Loop  (cost=0.00..19.61 rows=2 width=20) (actual time=0.26..0.26 rows=0 loops=1)
              ->  Seq Scan on merchant m  (cost=0.00..2.50 rows=1 width=4) (actual time=0.25..0.25 rows=0 loops=1)
              ->  Index Scan using id_merchant_e_catalogo_key on e_catalogo e  (cost=0.00..17.08 rows=2 width=16)
Total runtime: 0.71 msec

EXPLAIN



pgsql-general by date:

Previous
From: agus liem
Date:
Subject: Error application using Powerbuilder 7 and postgresql 7.3
Next
From: sathish
Date:
Subject: doubt