Re: RES: RES: select on 1milion register = 6s - Mailing list pgsql-performance

From Decibel!
Subject Re: RES: RES: select on 1milion register = 6s
Date
Msg-id 20070730012926.GP25704@nasby.net
Whole thread Raw
In response to RES: RES: select on 1milion register = 6s  ("Bruno Rodrigues Siqueira" <bruno@ravnus.com>)
List pgsql-performance
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                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

Attachment

pgsql-performance by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: select on 1milion register = 6s
Next
From: Decibel!
Date:
Subject: Re: RES: RES: select on 1milion register = 6s