Re: why doesn't an index help my simple query?

Peter Bierman <bierman(at)apple(dot)com> writes:
> At 10:29 PM -0400 5/30/03, Tom Lane wrote:
>> Hm, why is that shown as a "filter" and not an "index condition"? And
>> why is there an explicit conversion to timestamp with time zone in
>> there? Better tell us about the exact data types involved here ...

> I was hoping you'd say 'hm'. :-)

> CREATE TABLE events (
> "time" timestamp without time zone DEFAULT
> ('now'::text)::timestamp(6) with time zone NOT NULL,

Right. You're getting bit by ye same olde problem of datatype mismatch:
the planner does not realize that there is any connection between the
types "timestamp without time zone" and "timestamp with time zone", so
the presence of a WHERE condition expressed in terms of a timestamp-with-
tz operator doesn't induce it to do anything that a timestamp-without-tz
index could recognize.

Short answer is you probably ought to declare events.time as timestamp
with time zone; or if you have a *really good* reason why it should not
be declared that way, you ought to cast what you are comparing it to
to timestamp without tz. ("now()" yields timestamp with tz, which is
considered the preferred type in this category, so the default
assumption is to cast to timestamp with tz not vice versa.)

I'm starting to wonder if we shouldn't devise some way to allow these
sorts of cross-datatype comparisons to be more easily indexable. No
immediate ideas about how to do it without breaking stuff, though...

regards, tom lane