R: Query plan on identical tables differs . Why ?

>>>-----Messaggio originale-----
>>>Da: pgsql-performance-owner(at)postgresql(dot)org
>>>[mailto:pgsql-performance-owner(at)postgresql(dot)org] Per conto di
>>>Shridhar Daithankar
>>>Inviato: giovedì 13 maggio 2004 15.05
>>>A: Fabio Panizzutti
>>>Cc: pgsql-performance(at)postgresql(dot)org
>>>Oggetto: Re: [PERFORM] Query plan on identical tables differs . Why ?
>>>
>>>
>>>Fabio Panizzutti wrote:
>>>> storico=# explain 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 ;
>>>
>>>Can you please post explain analyze? That includes actual timings.

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 pk_storico_misure_2 on storico_misure
(cost=0.00..1984.64 rows=658 width=21) (actual time=723.441..1858.107
rows=835 loops=1)
Index Cond: ((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) AND (tag_id = 37423))
Total runtime: 1860.641 ms
(3 rows)

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 pk_anagtstorico_misuree_short_idx_2 on
storico_misure_short (cost=0.00..1783.04 rows=629 width=20) (actual
time=0.323..42.186 rows=864 loops=1)
Index Cond: (tag_id = 37423)
Filter: ((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: 43.166 ms

>>>Looking at the schema, can you try "and
>>>tag_id=37423::integer" instead?
>>>

I try :
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::integer;
Index Scan using pk_storico_misure_2 on storico_misure
(cost=0.00..1984.64 rows=658 width=21) (actual time=393.337..1303.998
rows=835 loops=1)
Index Cond: ((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) AND (tag_id = 37423))
Total runtime: 1306.484 ms

>>>> enable_hashagg = false
>>>> enable_hashjoin = false
>>>> enable_indexscan = true
>>>> enable_mergejoin = true
>>>> enable_nestloop = false
>>>> enable_seqscan = true
>>>> enable_sort = false
>>>> enable_tidscan = false
>>>Why do you have these off? AFAIK, 7.4 improved hash
>>>aggregates a lot. So you
>>>might miss on these in this case.

I try for debug purpose , now i reset all 'enable' to default :

select * from pg_settings where name like 'enable%';
name | setting | context | vartype | source |
min_val | max_val
------------------+---------+---------+---------+--------------------+--
-------+---------
enable_hashagg | on | user | bool | configuration file |
|
enable_hashjoin | on | user | bool | configuration file |
|
enable_indexscan | on | user | bool | configuration file |
|
enable_mergejoin | on | user | bool | configuration file |
|
enable_nestloop | on | user | bool | configuration file |
|
enable_seqscan | on | user | bool | configuration file |
|
enable_sort | on | user | bool | configuration file |
|
enable_tidscan | on | user | bool | configuration file |
|
(8 rows)

The query plan are the same ....

>>>> # - Planner Cost Constants -
>>>>
>>>> #effective_cache_size = 1000 # typically 8KB each
>>>
>>>You might set it to something realistic.
>>>

I try 10000 and 100000 but nothing change .

>>>And what is your hardware setup? Disks/CPU/RAM?

32GB SCSI/DUAL Intel(R) Pentium(R) III CPU family 1133MHz/ 1GB RAM
and linux red-hat 9

I don't understand why the planner chose a different query plan on
identical tables with same indexes .

Thanks a lot for help!.

Fabio