pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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





view thread (10+ messages)  latest in thread

Message-ID: <6DF1C81C-2663-4F50-9F25-F53D427DD206@mac.com>
Permalink:  ../6DF1C81C-2663-4F50-9F25-F53D427DD206@mac.com/
Also on:    postgresql.org/message-id/6DF1C81C-2663-4F50-9F25-F53D427DD206@mac.com

 · 

reply

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