RE: [SQL] indexing a datetime by date
От | Jackson, DeJuan |
---|---|
Тема | RE: [SQL] indexing a datetime by date |
Дата | |
Msg-id | D05EF808F2DFD211AE4A00105AA1B5D2037A39@cpsmail обсуждение исходный текст |
Список | pgsql-hackers |
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 >
В списке pgsql-hackers по дате отправления: