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 AA4625E3631 for ; Mon, 30 Aug 2004 05:20:08 -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 89949-09 for ; Mon, 30 Aug 2004 08:19:58 +0000 (GMT) Received: from mail.esphion.com (mail.esphion.com [202.6.75.178]) by svr1.postgresql.org (Postfix) with ESMTP id C1C6F5E46C3 for ; Mon, 30 Aug 2004 05:20:01 -0300 (ADT) Received: (qmail 8266 invoked from network); 30 Aug 2004 08:19:31 -0000 Received: from unknown (HELO conker.?none?) (10.0.1.137) by mail.esphion.com with SMTP; 30 Aug 2004 08:19:31 -0000 Received: (nullmailer pid 13919 invoked by uid 10001); Mon, 30 Aug 2004 08:19:59 -0000 Date: Mon, 30 Aug 2004 20:19:59 +1200 From: Guy Thornley To: pgsql-performance@postgresql.org Subject: Re: Why does a simple query not use an obvious index? Message-ID: <20040830081959.GC3714@conker.esphion.com> Reply-To: Guy Thornley References: <1093804128.5493.22.camel@localhost.localdomain> <87fz65k5sq.fsf@stark.xeocode.com> <16093.1093817023@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <16093.1093817023@sss.pgh.pa.us> User-Agent: Mutt/1.5.5.1+cvs20040105i 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/440 X-Sequence-Number: 8076 On Sun, Aug 29, 2004 at 06:03:43PM -0400, Tom Lane wrote: > >> select somefield from sometable where timestampfield > now()-'60 > >> seconds'::interval > > This is a FAQ, but since the archives don't seem to be up at the moment, > here's the answer once again: > > The expression "now() - something" is not a constant, so the planner > is faced with "timestampfield > unknownvalue". Its default assumption > about the number of rows that will match is much too high to make an > indexscan look profitable (from memory, I think it guesses that about > a third of the table will match...). Ok; this explains some really wierd stuff I've been seeing. 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] ntais# \d detect.stats Table "detect.stats" Column | Type | Modifiers --------------+--------------------------+------------------------------------------------------------- anomaly_id | integer | not null at | timestamp with time zone | not null default ('now'::text)::timestamp(6) with time zone resolution | real | default 1.0 values | real[] | stat_type_id | integer | not null Indexes: "stats_pkey" primary key, btree (anomaly_id, stat_type_id, "at") "stats__ends_at" btree (stats__ends_at("at", resolution, "values")) Foreign-key constraints: "$1" FOREIGN KEY (anomaly_id) REFERENCES anomalies(anomaly_id) ON DELETE CASCADE "$2" FOREIGN KEY (stat_type_id) REFERENCES stat_types(stat_type_id) ntais=# SET enable_seqscan = on; SET 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 ; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Sort (cost=602473.59..608576.72 rows=2441254 width=8) (actual time=198577.407..198579.136 rows=6152 loops=1) Sort Key: anomaly_id, stat_type_id -> Seq Scan on stats (cost=0.00..248096.42 rows=2441254 width=8) (actual time=198299.685..198551.460 rows=6152 loops=1) Filter: (stats__ends_at("at", resolution, "values") > '2004-08-30 16:21:09+12'::timestamp with time zone) Total runtime: 198641.649 ms (5 rows) 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 ; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=10166043.26..10172146.40 rows=2441254 width=8) (actual time=44.710..46.661 rows=6934 loops=1) Sort Key: anomaly_id, stat_type_id -> Index Scan using stats__ends_at on stats (cost=0.00..9811666.09 rows=2441254 width=8) (actual time=0.075..24.702 rows=6934 loops=1) Index Cond: (stats__ends_at("at", resolution, "values") > '2004-08-30 16:21:09+12'::timestamp with time zone) Total runtime: 50.354 ms (5 rows) ntais=# SELECT count(*) FROM detect.stats; count --------- 7326151 (1 row) Ive done repeated ANALYZE's, both table-specific and database-wide, and get the same result every time. For us, a global 'enable_seqscan = off' in postgresql.conf is the way to go. You occasionally see an odd plan while developing a query (eg: scanning an index with no contraint to simply get ORDER BY). Usually thats a broken query/index, and I simply fix it. Guy Thornley