Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Guy Thornley <guy(at)esphion(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Why does a simple query not use an obvious index?
Date: 2004-08-30 15:41:02
Message-ID: 7269.1093880462@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Guy Thornley <guy(at)esphion(dot)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

In response to

pgsql-performance by date

Next:From: Greg StarkDate: 2004-08-30 15:41:05
Subject: Re: Why does a simple query not use an obvious index?
Previous:From: Guy ThornleyDate: 2004-08-30 08:19:59
Subject: Re: Why does a simple query not use an obvious index?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group