R: R: Query plan on identical tables differs . Why ?

>>>-----Messaggio originale-----
>>>Da: Stephan Szabo [mailto:sszabo(at)megazone(dot)bigpanda(dot)com]
>>>Inviato: giovedì 13 maggio 2004 17.17
>>>A: Fabio Panizzutti
>>>Cc: 'Shridhar Daithankar'; pgsql-performance(at)postgresql(dot)org
>>>Oggetto: Re: R: [PERFORM] Query plan on identical tables
>>>differs . Why ?
>>>
>>>
>>>On Thu, 13 May 2004, Fabio Panizzutti wrote:
>>>
>>>
>>>> I don't understand why the planner chose a different query plan on
>>>> identical tables with same indexes .
>>>
>>>Because it's more than table structure that affects the
>>>choice made by the planner. In addition the statistics
>>>about the values that are there as well as the estimated
>>>size of the table have effects. One way to see is to see
>>>what it thinks is best is to remove the indexes it is using
>>>and see what plan it gives then, how long it takes and the
>>>estimated costs for those plans.
>>>
>>>In other suggestions, I think having a (tag_id, data_tag)
>>>index rather than (data_tag, tag_id) may be a win for
>>>queries like this. Also, unless you're doing many select
>>>queries by only the first field of the composite index and
>>>you're not doing very many insert/update/deletes, you may
>>>want to drop the other index on just that field.
>>>

Thanks for your attention , i change the indexes on the tables as you
suggested :

storico=# \d storico_misure_short
Table "tenore.storico_misure_short"
Column | Type | Modifiers
-------------------------+-----------------------------+-----------
data_tag | timestamp without time zone | not null
tag_id | integer | not null
unita_misura | character varying(6) | not null
valore_tag | numeric(20,3) | not null
qualita | integer | not null
numero_campioni | numeric(5,0) |
frequenza_campionamento | numeric(3,0) |
Indexes:
"storico_misure_short_idx" primary key, btree (tag_id, data_tag)
"storico_misure_short_data_tag_idx2" btree (data_tag)

storico=# \d storico_misure
Table "tenore.storico_misure"
Column | Type | Modifiers
-------------------------+-----------------------------+-----------
data_tag | timestamp without time zone | not null
tag_id | integer | not null
unita_misura | character varying(6) | not null
valore_tag | numeric(20,3) | not null
qualita | integer | not null
numero_campioni | numeric(5,0) |
frequenza_campionamento | numeric(3,0) |
Indexes:
"storico_misure_idx" primary key, btree (tag_id, data_tag)
"storico_misure_data_tag_idx2" btree (data_tag)

And now performance are similar and the planner works correctly :

storico=# \d storico_misure_short
Table "tenore.storico_misure_short"
Column | Type | Modifiers
-------------------------+-----------------------------+-----------
data_tag | timestamp without time zone | not null
tag_id | integer | not null
unita_misura | character varying(6) | not null
valore_tag | numeric(20,3) | not null
qualita | integer | not null
numero_campioni | numeric(5,0) |
frequenza_campionamento | numeric(3,0) |
Indexes:
"storico_misure_short_idx" primary key, btree (tag_id, data_tag)
"storico_misure_short_data_tag_idx2" btree (data_tag)

storico=# \d storico_misure
Table "tenore.storico_misure"
Column | Type | Modifiers
-------------------------+-----------------------------+-----------
data_tag | timestamp without time zone | not null
tag_id | integer | not null
unita_misura | character varying(6) | not null
valore_tag | numeric(20,3) | not null
qualita | integer | not null
numero_campioni | numeric(5,0) |
frequenza_campionamento | numeric(3,0) |
Indexes:
"storico_misure_idx" primary key, btree (tag_id, data_tag)
"storico_misure_data_tag_idx2" btree (data_tag)

storico=# explain analyze select tag_id,valore_tag,data_tag from
storico_misure_short where (data_tag>'2004-05-03' and data_tag
<'2004-05-12') and tag_id=37423 ;

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
--------------------------
Index Scan using storico_misure_short_idx on storico_misure_short
(cost=0.00..2104.47 rows=584 width=20) (actual time=0.232..39.932
rows=864 loops=1)
Index Cond: ((tag_id = 37423) AND (data_tag > '2004-05-03
00:00:00'::timestamp without time zone) AND (data_tag < '2004-05-12
00:00:00'::timestamp without time zone))
Total runtime: 40.912 ms
(3 rows)

Time: 43,233 ms
storico=# explain analyze select tag_id,valore_tag,data_tag from
storico_misure where (data_tag>'2004-05-03' and data_tag <'2004-05-12')
and tag_id=37423 ;

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
--------------------------
Index Scan using storico_misure_idx on storico_misure
(cost=0.00..2097.56 rows=547 width=21) (actual time=0.518..92.067
rows=835 loops=1)
Index Cond: ((tag_id = 37423) AND (data_tag > '2004-05-03
00:00:00'::timestamp without time zone) AND (data_tag < '2004-05-12
00:00:00'::timestamp without time zone))
Total runtime: 93.459 ms
(3 rows)

I need the index on data_tag for other query ( last values on the last
date ) .

Regards

Fabio