Different runtime on the same query
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
NMB Webmaster <webmaster@nmb.it> writes:
I have two database quite similar, one has more tables than the other.
Are both of them vacuumed/analyzed recently? The slow plan seems to be
estimating many more rows out of the e/m join than the other one.
My Postgres version is 7.2.3. Please help me , it is very important to
me to tune this query.
I'd recommend an update, myself. 7.2.3 is old...
regards, tom lane
The first thing I made was vacuum/analyze them. Then I deleted and rebuilt the indicies but the problem remained. So I created a new table (e_catalogo2) with new indicies, I copied all data from the old table to the new one, weirdly the problem was solved. In my opinion there is something wrong (sometimes) in building indicies in Postgres, but of course, I am not sure.
I wish to update to the last stable version but I fear the passing will be painful, when I updated from the 7.0 to 7.2 I had to modify some data structure because not compliant with the new version.
Show quoted text
On 14/06/2004 20.58, Tom Lane <tgl@sss.pgh.pa.us> wrote:
NMB Webmaster <webmaster@nmb.it> writes:
I have two database quite similar, one has more tables than the
other.
Are both of them vacuumed/analyzed recently? The slow plan seems
to be
estimating many more rows out of the e/m join than the other one.My Postgres version is 7.2.3. Please help me , it is very important
to
me to tune this query.
I'd recommend an update, myself. 7.2.3 is old...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that
your
message can get through to the mailing list cleanly
Import Notes
Resolved by subject fallback