From: Decibel! <decibel@decibel.org>
To: Bruno Rodrigues Siqueira <bruno@ravnus.com>
Cc: Ragnar <gnari@hive.is>
Cc: pgsql-performance@postgresql.org
Subject: Re: RES: RES: select on 1milion register = 6s
Date: Sun, 29 Jul 2007 20:29:26 -0500
Message-ID: <20070730012926.GP25704@nasby.net> (raw)
In-Reply-To: <005b01c7d1ff$b96ea3d0$e900a8c0@brunolaptop>
References: <20070729163536.GM25704@nasby.net>
<005b01c7d1ff$b96ea3d0$e900a8c0@brunolaptop>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: decibel@decibel.org, bruno@ravnus.com, gnari@hive.is
Subject: Re: RES: RES: select on 1milion register = 6s
In-Reply-To: <20070730012926.GP25704@nasby.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox