From: Ian Cass <ian.cass@mblox.com>
To: pgsql-sql@postgresql.org
Subject: Date indexing
Date: Tue, 23 Apr 2002 10:35:59 +0100
Message-ID: <005501c1eaaa$46eb2500$6602a8c0@salamander> (raw)
Hi,
I've been trying to do date range index lookups. According to EXPLAIN, my
sql is using the appropriate index, but the result takes forever to return
(~10mins) even tho the dataset it's referencing is reasonably small (~5000
lines). Similar queries on a similar sized dataset on the same database that
are not referencing a date range, but a specific field (WHERE user_name =
'U12345' for example) take a matter of seconds to return.
The index is on a timestamp field, and the only way I could find to get it
to use this index was something like the following...
AND (messages.client_id = '89' AND messages.originator like '%' AND
messages.logtime >= timestamp '20/04/2002 00:00:00' AND messages.logtime <=
timestamp '20/04/2002 23:59:59')
Index is on logtime, client_id, originator
logtime = timezone
client id = int2
originator = text
I'll only ever be referencing this data to a granularity of 1 day, so is
there a way I can use a function to index this so that the date column in
the index is text containing just DD/MM/YYYY? I did try to index something
like..
create index messages_200204_ix3 on messages_200204
(to_char(logtime,'DD/MM/YYYY'), client_id, originator)
...but it didn't work. Gives me parse errors on '.
Are date range index lookups slow on Postgres?
Can forward more details if required.
--
Ian Cass
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-sql@postgresql.org
Cc: ian.cass@mblox.com
Subject: Re: Date indexing
In-Reply-To: <005501c1eaaa$46eb2500$6602a8c0@salamander>
* 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