Re: Select "todays" timestamps in an index friendly way

> create temporary table t (
> id SERIAL primary key,
> ts timestamp not null default now()
> );

* add date( ts ) as a field and index date = now()::date.

* Depending on the amount of data in your table the date
may not be seletive enough to be worth using, at which
point the index may be present and ignored. Only way to
be sure is analyze it.

* Might be worth looking at a partial index using >= 00:00:00
and < 24:00:00 (PG grocks the 2400 notation for "midnight at
the end of today) or

where ts && tsrange( ... 00:00:00, ... 24:00:00, '[)] )

Nice thing about the partial index is that you can create it
on all of the non-ts fields for fast lookup by whatever and
only index the portion for today.

* Think about using a materialized view rather than a temp
table. May prove simpler to query.

--
Steven Lembark 3920 10th Ave South
Workhorse Computing Birmingham, AL 35222
lembark(at)wrkhors(dot)com +1 888 359 3508