Skip site navigation (1) Skip section navigation (2)

RES: RES: select on 1milion register = 6s

From: "Bruno Rodrigues Siqueira" <bruno(at)ravnus(dot)com>
To: "'Decibel!'" <decibel(at)decibel(dot)org>,"'Ragnar'" <gnari(at)hive(dot)is>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: RES: RES: select on 1milion register = 6s
Date: 2007-07-29 16:44:23
Message-ID: 005b01c7d1ff$b96ea3d0$e900a8c0@brunolaptop (view raw or flat)
Thread:
Lists: pgsql-performance
Look it




EXPLAIN
 ANALYZE
select 
       to_char(data_encerramento,'mm/yyyy')  as opcoes_mes, 
       to_char(data_encerramento,'yyyy-mm') as ordem from detalhamento_bas 
where

data_encerramento  =  '01/12/2006'  

GROUP BY opcoes_mes, ordem
ORDER BY ordem DESC


****************************************************************************


QUERY PLAN
Sort  (cost=60.72..60.72 rows=1 width=8) (actual time=4.586..4.586 rows=0
loops=1)
  Sort Key: to_char(data_encerramento, 'yyyy-mm'::text)
  ->  HashAggregate  (cost=60.72..60.72 rows=1 width=8) (actual
time=4.579..4.579 rows=0 loops=1)
        ->  Index Scan using detalhamento_bas_idx3005 on detalhamento_bas
(cost=0.00..60.67 rows=105 width=8) (actual time=4.576..4.576 rows=0
loops=1)
              Index Cond: (data_encerramento = '2006-12-01
00:00:00'::timestamp without time zone)
Total runtime: 4.629 ms


////////////////////////////////////////////////////////////////////////////

EXPLAIN
 ANALYZE
select 
       to_char(data_encerramento,'mm/yyyy')  as opcoes_mes, 
       to_char(data_encerramento,'yyyy-mm') as ordem from detalhamento_bas 
where

data_encerramento >=  '01/12/2006'  and
data_encerramento < '01/02/2007' 

GROUP BY opcoes_mes, ordem
ORDER BY ordem DESC

****************************************************************************

QUERY PLAN
Sort  (cost=219113.10..219113.10 rows=4 width=8) (actual
time=10079.212..10079.213 rows=2 loops=1)
  Sort Key: to_char(data_encerramento, 'yyyy-mm'::text)
  ->  HashAggregate  (cost=219113.09..219113.09 rows=4 width=8) (actual
time=10079.193..10079.195 rows=2 loops=1)
        ->  Seq Scan on detalhamento_bas  (cost=0.00..217945.41 rows=2335358
width=8) (actual time=0.041..8535.792 rows=2335819 loops=1)
              Filter: ((data_encerramento >= '2006-12-01
00:00:00'::timestamp without time zone) AND (data_encerramento < '2007-02-01
00:00:00'::timestamp without time zone))
Total runtime: 10079.256 ms







Strange!!! Why does the index not works?

All my querys doesn't work with range dates.... I don't know what to do...
Please, help!  


Bruno



-----Mensagem original-----
De: Decibel! [mailto:decibel(at)decibel(dot)org] 
Enviada em: domingo, 29 de julho de 2007 13:36
Para: Ragnar
Cc: Bruno Rodrigues Siqueira; pgsql-performance(at)postgresql(dot)org
Assunto: Re: RES: [PERFORM] select on 1milion register = 6s

On Sat, Jul 28, 2007 at 10:36:16PM +0000, Ragnar wrote:
> On lau, 2007-07-28 at 17:12 -0300, Bruno Rodrigues Siqueira wrote:
> 
> > where
> > 
> > to_char( data_encerramento ,'yyyy-mm') 
> > between   '2006-12' and  '2007-01'
> 
> assuming data_encerramento is a date column, try:
> WHERE data_encerramento between   '2006-12-01' and  '2007-01-31'

IMO, much better would be:

WHERE data_encerramento >= '2006-12-01' AND data_encerramento <
'2007-02-01'

This means you don't have to worry about last day of the month or
timestamp precision. In fact, since the field is actually a timestamp,
the between posted above won't work correctly.
-- 
Decibel!, aka Jim Nasby                        decibel(at)decibel(dot)org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)


In response to

Responses

pgsql-performance by date

Next:From: Alvaro HerreraDate: 2007-07-29 18:02:20
Subject: Re: select on 1milion register = 6s
Previous:From: Decibel!Date: 2007-07-29 16:35:37
Subject: Re: RES: select on 1milion register = 6s

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group