pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
R: Query plan on identical tables differs . Why ?
3+ messages / 3 participants
[nested] [flat]

* R: Query plan on identical tables differs . Why ?
@ 2004-05-13 14:06 Fabio Panizzutti <panizzutti@interlogica.net>
  2004-05-13 15:01 ` Re: R: Query plan on identical tables differs . Why ? Tom Lane <tgl@sss.pgh.pa.us>
  2004-05-13 15:16 ` Re: R: Query plan on identical tables differs . Why ? Stephan Szabo <sszabo@megazone.bigpanda.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Fabio Panizzutti @ 2004-05-13 14:06 UTC (permalink / raw)
  To: 'Shridhar Daithankar' <shridhar@frodo.hserus.net>; +Cc: 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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: R: Query plan on identical tables differs . Why ?
  2004-05-13 14:06 R: Query plan on identical tables differs . Why ? Fabio Panizzutti <panizzutti@interlogica.net>
@ 2004-05-13 15:01 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2004-05-13 15:01 UTC (permalink / raw)
  To: Fabio Panizzutti <panizzutti@interlogica.net>; +Cc: 'Shridhar Daithankar' <shridhar@frodo.hserus.net>; pgsql-performance

"Fabio Panizzutti" <panizzutti@interlogica.net> writes:
> I don't understand why the planner chose a different query plan on
> identical tables with same indexes . 

Different data statistics; not to mention different table sizes
(the cost equations are not linear).

Have you ANALYZEd (or VACUUM ANALYZEd) both tables recently?

If the stats are up to date but still not doing the right thing,
you might try increasing the statistics target for the larger
table's tag_id column.  See ALTER TABLE SET STATISTICS.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: R: Query plan on identical tables differs . Why ?
  2004-05-13 14:06 R: Query plan on identical tables differs . Why ? Fabio Panizzutti <panizzutti@interlogica.net>
@ 2004-05-13 15:16 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Stephan Szabo @ 2004-05-13 15:16 UTC (permalink / raw)
  To: Fabio Panizzutti <panizzutti@interlogica.net>; +Cc: Shridhar Daithankar <shridhar@frodo.hserus.net>; pgsql-performance

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.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-05-13 15:16 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-13 14:06 R: Query plan on identical tables differs . Why ? Fabio Panizzutti <panizzutti@interlogica.net>
2004-05-13 15:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-05-13 15:16 ` Stephan Szabo <sszabo@megazone.bigpanda.com>

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