Received: from bjke.com (firewall-user@ganymede.bjke.com [144.210.8.38]) by hub.org (8.9.2/8.9.1) with ESMTP id MAA68961; Tue, 30 Mar 1999 12:33:42 -0500 (EST) (envelope-from djackson@cpsgroup.com) Received: by bjke.com; id LAA04307; Tue, 30 Mar 1999 11:33:40 -0600 (CST) Received: from unknown(144.210.12.10) by gauntlet.bjke.com via smap (4.1) id xma004082; Tue, 30 Mar 99 11:33:20 -0600 Received: by cpsmail with Internet Mail Service (5.5.2448.0) id ; Tue, 30 Mar 1999 11:33:47 -0600 Message-ID: From: "Jackson, DeJuan" To: Andrew Merrill , Tom Lane Cc: Thomas Lockhart , "Jackson, DeJuan" , pgsql-sql@hub.org, PGSQL Hackers Subject: RE: [SQL] indexing a datetime by date Date: Tue, 30 Mar 1999 11:33:46 -0600 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2448.0) Content-Type: text/plain; charset="iso-8859-1" try: explain select when from notes where datetime_date(when) = 'now'::date; -DEJ > 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 >