Thread: 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,
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
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 createda new table (e_catalogo2) with new indicies, I copied all data from the old table to the new one, weirdly the problemwas solved. In my opinion there is something wrong (sometimes) in building indicies in Postgres, but of course, Iam 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 Ihad to modify some data structure because not compliant with the new version. 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 > > >