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

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

From: Decibel! <decibel(at)decibel(dot)org>
To: Bruno Rodrigues Siqueira <bruno(at)ravnus(dot)com>
Cc: 'Ragnar' <gnari(at)hive(dot)is>, pgsql-performance(at)postgresql(dot)org
Subject: Re: RES: RES: select on 1milion register = 6s
Date: 2007-07-30 01:29:26
Message-ID: 20070730012926.GP25704@nasby.net (view raw or flat)
Thread:
Lists: 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(at)decibel(dot)org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

In response to

Responses

pgsql-performance by date

Next:From: Decibel!Date: 2007-07-30 06:52:41
Subject: Re: RES: RES: select on 1milion register = 6s
Previous:From: Alvaro HerreraDate: 2007-07-29 18:02:20
Subject: Re: select on 1milion register = 6s

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