R: Query plan on identical tables differs . Why ? - Mailing list pgsql-performance

>>>-----Messaggio originale-----
>>>Da: pgsql-performance-owner@postgresql.org
>>>[mailto:pgsql-performance-owner@postgresql.org] Per conto di
>>>Shridhar Daithankar
>>>Inviato: giovedì 13 maggio 2004 15.05
>>>A: Fabio Panizzutti
>>>Cc: pgsql-performance@postgresql.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