Pantek Library
Hosting Provided By
CybrHost
High Speed Hosting
Mailing Lists: postgresql.org > pgsql-performance > 07 > 070587.html (Request Expert postgresql.org Support)

RES: RES: [PERFORM] select on 1milion register = 6s

From: Bruno Rodrigues Siqueira <bruno(at)ravnus.com>
Date: Sun Jul 29 2007 - 12:44:23 EDT


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
Do you need help?X

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

Do you need more help?X

-----Mensagem original-----
De: Decibel! [mailto:decibel@decibel.org] Enviada em: domingo, 29 de julho de 2007 13:36 Para: Ragnar
Cc: Bruno Rodrigues Siqueira; pgsql-performance@postgresql.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@decibel.org
EnterpriseDB      
http://enterprisedb.com      512.569.9461 (cell)


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               
http://archives.postgresql.org
Received on Sun Jul 29 12:47:10 2007

This archive was generated by hypermail 2.1.8 : Thu Aug 09 2007 - 19:46:50 EDT

Contact Us  Legal Notices  Order Services Online 
Pantek Home  Privacy Policy  IT news  Site Map  Pantek Library