pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Guy Thornley <guy@esphion.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why does a simple query not use an obvious index?
Date: Mon, 30 Aug 2004 11:41:02 -0400
Message-ID: <7269.1093880462@sss.pgh.pa.us> (raw)
In-Reply-To: <20040830081959.GC3714@conker.esphion.com>
References: <ENEIKKAEJOFHOIHMMOIDIEBMJCAA.jack.kerkhof@guest-tek.com>
	<1093804128.5493.22.camel@localhost.localdomain>
	<87fz65k5sq.fsf@stark.xeocode.com>
	<16093.1093817023@sss.pgh.pa.us>
	<20040830081959.GC3714@conker.esphion.com>

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



view thread (28+ messages)  latest in thread

Message-ID: <7269.1093880462@sss.pgh.pa.us>
Permalink:  ../7269.1093880462@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/7269.1093880462@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, guy@esphion.com
  Subject: Re: Why does a simple query not use an obvious index?
  In-Reply-To: <7269.1093880462@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox