Message-ID: <489AEC42.5080007@archonet.com>
Date: Thu, 07 Aug 2008 13:36:18 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.16 (X11/20080707)
MIME-Version: 1.0
To: Giorgio Valoti <giorgio_v@mac.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Query Plan choice with timestamps
References: <321506D5-4400-470C-909E-C7700D49A540@mac.com>
	<489AB3EB.8040903@archonet.com>
	<15821866-9564-40E8-BC12-52ADD6D6EEA5@mac.com>
In-Reply-To: <15821866-9564-40E8-BC12-52ADD6D6EEA5@mac.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: quoted-printable

Giorgio Valoti wrote:
>=20
> On 07/ago/08, at 10:35, Richard Huxton wrote:
>=20
>> Giorgio Valoti wrote:
>>> Hi, I have a timestamptz field that I want to use with a query, but I=
=20
>>> don=92t need the full timestamp resolution, so I=92ve created a=20
>>> day_trunc(timestamptz) immutable function which I=92ll use with the=20
>>> query and with a new index:
>>> logs=3D> create index test_idx on blackbox (day_trunc(ts));
>>> However, the query plan doesn=92t use the index:
>>
>> Does it use it ever? e.g. with
>>  SELECT * FROM blackbox WHERE day_trunk(ts) =3D '...'
>=20
> It=92s used:
[snip]

OK - so the index is working.

If you disable seq-scans before running the query, does it use it then?

SET enable_seqscan =3D off;

 > logs=3D>explain select count(*) from blackbox group by day_trunc(ts) o=
rder
 > by day_trunc(ts);
 >                                         QUERY PLAN
 >=20
-------------------------------------------------------------------------=
-----------------=20

 >
 >  GroupAggregate  (cost=3D98431.58..119773.92 rows=3D74226 width=3D8)

In particular:
1. Is the estimated cost more or less than 119773.92?
2. How does that match the actual time taken?

--=20
   Richard Huxton
   Archonet Ltd