Date: Sun, 29 Jul 2007 20:29:26 -0500
From: Decibel! <decibel@decibel.org>
To: Bruno Rodrigues Siqueira <bruno@ravnus.com>
Cc: 'Ragnar' <gnari@hive.is>, pgsql-performance@postgresql.org
Subject: Re: RES: RES: select on 1milion register = 6s
Message-ID: <20070730012926.GP25704@nasby.net>
References: <20070729163536.GM25704@nasby.net>
	<005b01c7d1ff$b96ea3d0$e900a8c0@brunolaptop>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="BXr400anF0jyguTS"
Content-Disposition: inline
In-Reply-To: <005b01c7d1ff$b96ea3d0$e900a8c0@brunolaptop>
User-Agent: Mutt/1.5.13 (2006-08-11)


--BXr400anF0jyguTS
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Sun, Jul 29, 2007 at 01:44:23PM -0300, Bruno Rodrigues Siqueira wrote:
> EXPLAIN
>  ANALYZE
> select=20
>        to_char(data_encerramento,'mm/yyyy')  as opcoes_mes,=20
>        to_char(data_encerramento,'yyyy-mm') as ordem from detalhamento_ba=
s=20
> where
>=20
> data_encerramento >=3D  '01/12/2006'  and
> data_encerramento < '01/02/2007'=20
>=20
> GROUP BY opcoes_mes, ordem
> ORDER BY ordem DESC
>=20
> *************************************************************************=
***
>=20
> QUERY PLAN
> Sort  (cost=3D219113.10..219113.10 rows=3D4 width=3D8) (actual
> time=3D10079.212..10079.213 rows=3D2 loops=3D1)
>   Sort Key: to_char(data_encerramento, 'yyyy-mm'::text)
>   ->  HashAggregate  (cost=3D219113.09..219113.09 rows=3D4 width=3D8) (ac=
tual
> time=3D10079.193..10079.195 rows=3D2 loops=3D1)
>         ->  Seq Scan on detalhamento_bas  (cost=3D0.00..217945.41 rows=3D=
2335358
> width=3D8) (actual time=3D0.041..8535.792 rows=3D2335819 loops=3D1)
>               Filter: ((data_encerramento >=3D '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
>=20
> 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=3Dfalse and see if it
is or not.
--=20
Decibel!, aka Jim Nasby                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

--BXr400anF0jyguTS
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.3 (FreeBSD)

iD8DBQFGrT72dO30qud8SkgRAl8PAJwKayz/3RG1tYD7XBxTiZ6iaLeu+gCZAUix
D7e5XIhrBl0gpkr1EiRTcxs=
=pzb5
-----END PGP SIGNATURE-----

--BXr400anF0jyguTS--