indexing a datetime by date
От | Andrew Merrill |
---|---|
Тема | indexing a datetime by date |
Дата | |
Msg-id | 370044A5.9E204166@compclass.com обсуждение исходный текст |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: