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




view thread (5+ messages)  latest in thread

Message-ID: <005501c1eaaa$46eb2500$6602a8c0@salamander>
Permalink:  ../005501c1eaaa$46eb2500$6602a8c0@salamander/
Also on:    postgresql.org/message-id/005501c1eaaa$46eb2500$6602a8c0@salamander

 · 

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 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