pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Fabio Panizzutti <panizzutti@interlogica.net>
To: 'Shridhar Daithankar' <shridhar@frodo.hserus.net>
Cc: pgsql-performance@postgresql.org
Subject: R: Query plan on identical tables differs . Why ?
Date: Thu, 13 May 2004 16:06:01 +0200
Message-ID: <006301c438f3$6ca33460$3c02020a@ufficio> (raw)
In-Reply-To: <40A3728F.2030402@frodo.hserus.net>



>>>-----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




view thread (3+ messages)  latest in thread

Message-ID: <006301c438f3$6ca33460$3c02020a@ufficio>
Permalink:  ../006301c438f3$6ca33460$3c02020a@ufficio/
Also on:    postgresql.org/message-id/006301c438f3$6ca33460$3c02020a@ufficio

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: panizzutti@interlogica.net, shridhar@frodo.hserus.net
  Subject: Re: R: Query plan on identical tables differs . Why ?
  In-Reply-To: <006301c438f3$6ca33460$3c02020a@ufficio>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox