Received: from fullfeed.msn.fullfeed.com (fullfeed.msn.fullfeed.com [199.184.182.42]) by hub.org (8.9.2/8.9.1) with SMTP id WAA74748 for ; Mon, 29 Mar 1999 22:29:07 -0500 (EST) (envelope-from <@snapuser35.pacificcrest.net:andrew@compclass.com>) Received: from thatch2.compclass.com by fullfeed.msn.fullfeed.com (8.6.9/MSN-1.4) id VAA03058; Mon, 29 Mar 1999 21:28:51 -0600 Received: from snapuser35.pacificcrest.net by thatch2.compclass.com id aa00480; 29 Mar 99 21:31 CST Sender: andrew@compclass.com Message-ID: <370044A5.9E204166@compclass.com> Date: Mon, 29 Mar 1999 19:27:33 -0800 From: Andrew Merrill X-Mailer: Mozilla 4.04 [en] (X11; I; UnixWare 5 i386) MIME-Version: 1.0 To: pgsql-sql@hub.org Subject: indexing a datetime by date Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I have a table with a field, "when", of type "datetime". I can't use "date" because I need the times as well. I'm using PostgreSQL 6.4.2. I'd like to identify all of the records with today's date, as in: select when from notes where when::date = now()::date; The query works, but is very slow. Explain confirms that a sequential scan is being used. I've tried indexing on when: create index when_ndx1 on notes (when); But that doesn't help, as (I suppose) the optimizer can't match when::date with this index. Neither of these works: db=> create index when_ndx2 on notes (when::date); ERROR: parser: parse error at or near "::" db=> create index when_ndx3 on notes (date(when)); ERROR: DefineIndex: class not found As a workaround, I've been using this: select when from notes where when >= '3/29/1999 0:0:0' and when <= '3/29/1999 23:59:59'; but that's ugly and requires hardcoding today's date each time, rather than using now(). So, the question is, is there a way to index a datetime field by date? Andrew Merrill