Hi all.
On the very same database and session I have two different (but similar)
queries behaving in a very different way as far as timings.
This is the first one:
prove=# explain analyze select d.* from t_vcol natural join v_dati_attuali d
natural join tt_elem where vtab_id='TEST';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=5.65..8562012.60 rows=88104022 width=73) (actual
time=36.579..36.772 rows=7 loops=1)
-> Hash Join (cost=1.19..442967.06 rows=408730 width=73) (actual
time=36.547..36.660 rows=7 loops=1)
Hash Cond: (d.camp_id = t_vcol.camp_id)
-> Nested Loop (cost=0.00..430860.08 rows=1603700 width=73) (actual
time=36.480..36.558 rows=24 loops=1)
-> Seq Scan on tt_elem (cost=0.00..29.40 rows=1940 width=8)
(actual time=0.012..0.013 rows=1 loops=1)
-> Index Scan using i_dati_0 on t_dati d (cost=0.00..211.74
rows=827 width=73) (actual time=36.461..36.498 rows=24 loops
Index Cond: (d.elem_id = tt_elem.elem_id)
Filter: dato_flag
-> Hash (cost=1.12..1.12 rows=5 width=15) (actual time=0.039..0.039
rows=5 loops=1)
-> Seq Scan on t_vcol (cost=0.00..1.12 rows=5 width=15)
(actual time=0.015..0.026 rows=5 loops=1)
Filter: (vtab_id = 'TEST'::text)
-> Bitmap Heap Scan on tt_data (cost=4.46..16.62 rows=216 width=8)
(actual time=0.009..0.009 rows=1 loops=7)
Recheck Cond: ((d.dato_validita <= tt_data.data_corr) AND
(d.dato_scadenza > tt_data.data_corr))
-> Bitmap Index Scan on tt_data_pkey (cost=0.00..4.41 rows=216
width=0) (actual time=0.006..0.006 rows=1 loops=7)
Index Cond: ((d.dato_validita <= tt_data.data_corr) AND
(d.dato_scadenza > tt_data.data_corr))
Total runtime: 36.922 ms
(16 rows)
And this is the second one:
prove=# explain analyze SELECT d.* from t_campi_ricerche natural join
v_dati_attuali d natural join tt_rice where rice_id='CODICE';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=43.29..38167065.82 rows=409498649 width=73) (actual
time=2927.890..56922.415 rows=1 loops=1)
-> Hash Join (cost=38.83..430557.39 rows=1899736 width=73) (actual
time=2915.990..56910.510 rows=1 loops=1)
Hash Cond: (d.dato_t = tt_rice.dato_t)
-> Hash Join (cost=1.15..402765.04 rows=2335285 width=73) (actual
time=191.261..55238.816 rows=2394966 loops=1)
Hash Cond: (d.camp_id = t_campi_ricerche.camp_id)
-> Seq Scan on t_dati d (cost=0.00..326867.12 rows=14011712
width=73) (actual time=16.612..42797.766 rows=14011712 loops
Filter: dato_flag
-> Hash (cost=1.09..1.09 rows=5 width=15) (actual
time=0.053..0.053 rows=5 loops=1)
-> Seq Scan on t_campi_ricerche (cost=0.00..1.09 rows=5
width=15) (actual time=0.031..0.041 rows=5 loops=1)
Filter: (rice_id = 'CODICE'::text)
-> Hash (cost=22.30..22.30 rows=1230 width=32) (actual
time=0.009..0.009 rows=1 loops=1)
-> Seq Scan on tt_rice (cost=0.00..22.30 rows=1230 width=32)
(actual time=0.003..0.004 rows=1 loops=1)
-> Bitmap Heap Scan on tt_data (cost=4.46..16.62 rows=216 width=8)
(actual time=11.885..11.886 rows=1 loops=1)
Recheck Cond: ((d.dato_validita <= tt_data.data_corr) AND
(d.dato_scadenza > tt_data.data_corr))
-> Bitmap Index Scan on tt_data_pkey (cost=0.00..4.41 rows=216
width=0) (actual time=0.033..0.033 rows=1 loops=1)
Index Cond: ((d.dato_validita <= tt_data.data_corr) AND
(d.dato_scadenza > tt_data.data_corr))
Total runtime: 56922.563 ms
(17 rows)
The v_dati_attuali is a view and is common to both queries.
The structure of indexes is on t_vcol and t_campi_ricerche is very similar and
both tt_rice and tt_elem have just one row wirh one field being primary key.
Of course I'd like the second query to behave the same as the first one but
have no clue on how to achieve it.
Is there any hint?
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
