Обсуждение: timestamp and indexes

Hi all,

I seem to remember seeing something related to this on this list in the
past, but I can't find it now.  So I'll be brief for starters.

I have a log table that's getting several thousand inserts per day, properly
vacuumed/analysed at night, etc.  A query like this:
  select * from log where date_log = now();
refuses to use the index on date_log (the timestamp).

explain says this:
  Seq Scan on log  (cost=0.00..2053.26 rows=8 width=80)

If I do 'set enable_seqscan to off', it still won't use the index.

Does this ring a bell with anyone?  I can supply lots more data if needed...

Oh..  :-)  This is PG_7.1.2 on Linux 2.2.

TIA

Glen Parker
glenebob@nwlink.com

On Tue, 22 Jan 2002, Glen Parker wrote:

> Hi all,
>
> I seem to remember seeing something related to this on this list in the
> past, but I can't find it now.  So I'll be brief for starters.
>
> I have a log table that's getting several thousand inserts per day, properly
> vacuumed/analysed at night, etc.  A query like this:
>   select * from log where date_log = now();
> refuses to use the index on date_log (the timestamp).
>
> explain says this:
>   Seq Scan on log  (cost=0.00..2053.26 rows=8 width=80)
>
> If I do 'set enable_seqscan to off', it still won't use the index.

It looks like now() is not cachable (at least in my 7.2beta4 pg_proc) so
it's not going to use the index. You might be able to fake things with
an iscachable function that just returns the now() value (like:
create function foonow() returns timestamp as
'select now();' language 'sql' with (iscachable);
)