Re: [PGSQL v8.2.5] Similar queries behave differently - Mailing list pgsql-general

Hai all again.

Maybe I've solved the problem, but would like to have some hint on "why".

In the second query I've substituted the last join (natural join tt_rice)
with an additional "where condition". I can do this as I am sure that
the tt_rice table will always contain just one row with one field.

The main difference with the first query is that in the first case the
single row with a single field is a "bigint", while in the second one it
is "text".

Is there any deeper tutorial on how to read (and understand) the explain
analyze output?

Many thanks again.

Il Thursday 25 October 2007 10:17:23 Reg Me Please ha scritto:
> 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?