Re: Why does a simple query not use an obvious index?

Guy Thornley <guy@esphion.com> writes:
> However, I'm seeing breakage of the form mentioned by the original poster
> even when the query uses a _constant_ timestamp: [Postgres 7.4.3]

>         Indexes:
>             "stats_pkey" primary key, btree (anomaly_id, stat_type_id, "at")
>             "stats__ends_at" btree (stats__ends_at("at", resolution, "values"))

>         ntais=# EXPLAIN ANALYZE
>           SELECT anomaly_id, stat_type_id
>           FROM detect.stats
>           WHERE detect.stats__ends_at(at, resolution, values) > '2004-08-30 16:21:09+12'::timestamptz
>           ORDER BY anomaly_id, stat_type_id
>           ;

Here I'm afraid you're just stuck until 8.0 comes out (or you're feeling
brave enough to use a beta).  Releases before 8.0 do not maintain any
statistics about the contents of functional indexes, so the planner is
flying blind here in any case, and you end up with the very same 1/3rd
default assumption no matter what the right-hand side looks like.
You'll have to fall back to Plan A or Plan B to get this case to work
in 7.4.

            regards, tom lane