From: Giorgio Valoti <giorgio_v@mac.com>
To: pgsql-performance@postgresql.org
Subject: Re: Query Plan choice with timestamps
Date: Thu, 07 Aug 2008 20:42:19 +0200
Message-ID: <6DF1C81C-2663-4F50-9F25-F53D427DD206@mac.com> (raw)
In-Reply-To: <489AEC42.5080007@archonet.com>
References: <321506D5-4400-470C-909E-C7700D49A540@mac.com>
<489AB3EB.8040903@archonet.com>
<15821866-9564-40E8-BC12-52ADD6D6EEA5@mac.com>
<489AEC42.5080007@archonet.com>
On 07/ago/08, at 14:36, Richard Huxton wrote:
> Giorgio Valoti wrote:
>> On 07/ago/08, at 10:35, Richard Huxton wrote:
>>> Giorgio Valoti wrote:
>>>> Hi, I have a timestamptz field that I want to use with a query,
>>>> but I don’t need the full timestamp resolution, so I’ve created a
>>>> day_trunc(timestamptz) immutable function which I’ll use with the
>>>> query and with a new index:
>>>> logs=> create index test_idx on blackbox (day_trunc(ts));
>>>> However, the query plan doesn’t use the index:
>>>
>>> Does it use it ever? e.g. with
>>> SELECT * FROM blackbox WHERE day_trunk(ts) = '...'
>> It’s 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 = off;
Yes
> […]
>
> In particular:
> 1. Is the estimated cost more or less than 119773.92?
QUERY PLAN
-----------------------------------------------------------------------------------------
GroupAggregate (cost=0.00..122309.32 rows=74226 width=8)
-> Index Scan using date_idx on blackbox (cost=0.00..101586.31
rows=247736 width=8)
>
> 2. How does that match the actual time taken?
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=0.00..122309.32 rows=74226 width=8) (actual
time=0.222..1931.651 rows=428 loops=1)
-> Index Scan using date_idx on blackbox (cost=0.00..101586.31
rows=247736 width=8) (actual time=0.072..1861.367 rows=247736 loops=1)
Total runtime: 1931.782 ms
But I haven’t revised the vacuum settings.
Thank you
--
Giorgio Valoti
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: giorgio_v@mac.com
Subject: Re: Query Plan choice with timestamps
In-Reply-To: <6DF1C81C-2663-4F50-9F25-F53D427DD206@mac.com>
References: <321506D5-4400-470C-909E-C7700D49A540@mac.com> <489AB3EB.8040903@archonet.com> <15821866-9564-40E8-BC12-52ADD6D6EEA5@mac.com> <489AEC42.5080007@archonet.com> <6DF1C81C-2663-4F50-9F25-F53D427DD206@mac.com>
* 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