From: Michael Fuhr <mike@fuhr.org>
To: Michael Glaesemann <grzm@myrealbox.com>
Cc: Antony Paul <antonypaul24@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Checking = with timestamp field is slow
Date: Fri, 5 Nov 2004 02:13:06 -0700
Message-ID: <20041105091306.GA45244@winnie.fuhr.org> (raw)
In-Reply-To: <4704C044-2F05-11D9-BE4A-000A95C88220@myrealbox.com>
References: <2989532e0411042316566b35f4@mail.gmail.com>
<A64DEAC4-2F02-11D9-BE4A-000A95C88220@myrealbox.com>
<4704C044-2F05-11D9-BE4A-000A95C88220@myrealbox.com>
On Fri, Nov 05, 2004 at 05:32:49PM +0900, Michael Glaesemann wrote:
>
> On Nov 5, 2004, at 5:14 PM, Michael Glaesemann wrote:
>
> >
> >On Nov 5, 2004, at 4:16 PM, Antony Paul wrote:
> >>where today::date = '2004-11-05';
> >>
> >>This is the only condition in the query. There is a btree index on the
> >>column today.
> >>Is there any way to optimise it.
> >
> >I'm sure others out there have better ideas, but you might want to try
> >
> >where current_date = date '2004-11-05'
>
> Ach! just re-read that. today is one of your columns! Try
>
> where today::date = date '2004-11-05'
Casting '2004-11-05' to DATE shouldn't be necessary, at least not
in 7.4.5.
test=> EXPLAIN ANALYZE SELECT * FROM foo WHERE today::DATE = '2004-11-05';
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..268.00 rows=50 width=16) (actual time=0.592..50.854 rows=1 loops=1)
Filter: ((today)::date = '2004-11-05'::date)
As you can see, '2004-11-05' is already cast to DATE. The sequential
scan is happening because there's no index on today::DATE.
test=> CREATE INDEX foo_date_idx ON foo (DATE(today));
CREATE INDEX
test=> EXPLAIN ANALYZE SELECT * FROM foo WHERE DATE(today) = '2004-11-05';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Index Scan using foo_date_idx on foo (cost=0.00..167.83 rows=50 width=16) (actual time=0.051..0.061 rows=1 loops=1)
Index Cond: (date(today) = '2004-11-05'::date)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
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: mike@fuhr.org, grzm@myrealbox.com, antonypaul24@gmail.com
Subject: Re: Checking = with timestamp field is slow
In-Reply-To: <20041105091306.GA45244@winnie.fuhr.org>
* 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