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

От: Decibel!
Тема: Re: RES: RES: select on 1milion register = 6s
Дата: ,
Msg-id: 20070730012926.GP25704@nasby.net
(см: обсуждение, исходный текст)
Ответ на: RES: RES: select on 1milion register = 6s  ("Bruno Rodrigues Siqueira")
Список: pgsql-performance

Скрыть дерево обсуждения

select on 1milion register = 6s  ("Bruno Rodrigues Siqueira", )
 Re: select on 1milion register = 6s  (Craig James, )
  RES: select on 1milion register = 6s  ("Bruno Rodrigues Siqueira", )
   Re: RES: select on 1milion register = 6s  (Ragnar, )
    RES: RES: select on 1milion register = 6s  ("Bruno Rodrigues Siqueira", )
    Re: RES: select on 1milion register = 6s  (Decibel!, )
     RES: RES: select on 1milion register = 6s  ("Bruno Rodrigues Siqueira", )
      Re: RES: RES: select on 1milion register = 6s  (Decibel!, )
     Re: RES: RES: select on 1milion register = 6s  (Decibel!, )
   Re: select on 1milion register = 6s  ("Scott Marlowe", )
    Re: select on 1milion register = 6s  (Alvaro Herrera, )
    Re: select on 1milion register = 6s  (Jan Dittmer, )
 Re: select on 1milion register = 6s  (Hervé Piedvache, )
  RES: select on 1milion register = 6s  ("Bruno Rodrigues Siqueira", )
  RES: select on 1milion register = 6s  ("Bruno Rodrigues Siqueira", )

On Sun, Jul 29, 2007 at 01:44:23PM -0300, Bruno Rodrigues Siqueira wrote:
> 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?

It's unlikely that it's going to be faster to index scan 2.3M rows than
to sequential scan them. Try setting enable_seqscan=false and see if it
is or not.
--
Decibel!, aka Jim Nasby                        
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

Вложения

В списке pgsql-performance по дате сообщения:

От: Richard Huxton
Дата:
Сообщение: Re: Questions on Tags table schema
От: "Jay Kang"
Дата:
Сообщение: Re: Questions on Tags table schema