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