Different runtime on the same query

From: NMB Webmaster <webmaster(at)nmb(dot)it>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Different runtime on the same query
Date: 2004-06-10 09:34:29
Message-ID: 3169712064webmaster@nmb.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have two database quite similar, one has more tables than the other. I execute the same query on the two database, here is 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 WHERE cod_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 understand why. 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.08 rows=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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2004-06-10 10:15:53 Re: Transactions and insertion ordering
Previous Message Richard Huxton 2004-06-10 08:32:44 Re: How to tell when postmaster is ready