Received: from mblox.com (unknown [213.155.153.59]) by postgresql.org (Postfix) with ESMTP id A566D474E42 for ; Tue, 23 Apr 2002 05:37:29 -0400 (EDT) Received: from salamander (salamander.mblox.com [::ffff:192.168.2.102]) (AUTH: LOGIN ian.cass) by aerosmith with esmtp; Tue, 23 Apr 2002 09:37:31 +0000 Message-ID: <005501c1eaaa$46eb2500$6602a8c0@salamander> From: "Ian Cass" To: pgsql-sql@postgresql.org Subject: Date indexing Date: Tue, 23 Apr 2002 10:35:59 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200204/274 X-Sequence-Number: 7538 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