X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 99D075E470E for ; Mon, 30 Aug 2004 12:41:03 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 38269-09 for ; Mon, 30 Aug 2004 15:41:07 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 511BF5E4720 for ; Mon, 30 Aug 2004 12:40:58 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i7UFf3Bb007270; Mon, 30 Aug 2004 11:41:03 -0400 (EDT) To: Guy Thornley Cc: pgsql-performance@postgresql.org Subject: Re: Why does a simple query not use an obvious index? In-reply-to: <20040830081959.GC3714@conker.esphion.com> References: <1093804128.5493.22.camel@localhost.localdomain> <87fz65k5sq.fsf@stark.xeocode.com> <16093.1093817023@sss.pgh.pa.us> <20040830081959.GC3714@conker.esphion.com> Comments: In-reply-to Guy Thornley message dated "Mon, 30 Aug 2004 20:19:59 +1200" Date: Mon, 30 Aug 2004 11:41:02 -0400 Message-ID: <7269.1093880462@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/441 X-Sequence-Number: 8077 Guy Thornley 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