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
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 |