Thread

1. RE: [SQL] indexing a datetime by date

   Jackson, DeJuan <djackson@cpsgroup.com> — 1999-03-30T17:33:46Z

   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
   > 
   
   

2. Re: [SQL] indexing a datetime by date

   Andrew Merrill <andrew@compclass.com> — 1999-03-30T18:11:19Z

   Jackson, DeJuan wrote:
   
   > try:
   >  explain select when from notes where datetime_date(when) = 'now'::date;
   >         -DEJ
   
   Aha.  That does the trick.  Thanks!
   
   Andrew Merrill