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