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 MAA67759; Tue, 30 Mar 1999 12:28:37 -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 LAA14752; Tue, 30 Mar 1999 11:26:54 -0600 Received: from snapuser35.pacificcrest.net by thatch2.compclass.com id aa10592; 30 Mar 99 11:29 CST Sender: andrew@compclass.com Message-ID: <37010913.4298B216@compclass.com> Date: Tue, 30 Mar 1999 09:25:39 -0800 From: Andrew Merrill X-Mailer: Mozilla 4.04 [en] (X11; I; UnixWare 5 i386) MIME-Version: 1.0 To: Tom Lane CC: Thomas Lockhart , "Jackson, DeJuan" , pgsql-sql@hub.org, PGSQL Hackers Subject: Re: [SQL] indexing a datetime by date References: <6913.922811495@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Tom Lane wrote: > In the meantime, a workaround is to call the function using its > builtin name: > > play=> create table notes (when datetime); > CREATE > play=> create index when_ndx3 on notes (datetime_date(when) date_ops); > CREATE Thanks, that helps - I can now index a datetime field by date.But the index doesn't appear to be used: db=> create index ndx3 on notes (datetime_date(when) date_ops); CREATE db=> vacuum analyze notes; VACUUM db=> explain select when from notes where when::date = 'now'::date; NOTICE: QUERY PLAN: Seq Scan on notes (cost=4330.37 size=43839 width=8) EXPLAIN So it appears that the optimizer doesn't like this index. (This is with version 6.4.2.) The table has about 90,000 rows, of which between 10 and 100 might match a given date, so an index would really help. Am I missing something simple here? Thanks again for all your help. Andrew Merrill