X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 672A6D1CCAE for ; Fri, 30 Jan 2004 15:07:37 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 64606-02 for ; Fri, 30 Jan 2004 11:07:41 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id B3B43D1CCAD for ; Fri, 30 Jan 2004 11:07:36 -0400 (AST) 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 i0UF7Z8x013309; Fri, 30 Jan 2004 10:07:35 -0500 (EST) To: Kari Lavikka Cc: pgsql-performance@postgresql.org Subject: Re: Cost of indexscan In-reply-to: References: Comments: In-reply-to Kari Lavikka message dated "Fri, 30 Jan 2004 10:16:23 +0200" Date: Fri, 30 Jan 2004 10:07:34 -0500 Message-ID: <13308.1075475254@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/326 X-Sequence-Number: 5500 Kari Lavikka writes: > Postgres seems to estimate the cost of indexscan to be a bit too high. > The table has something like 500000 rows and I have run reindex and vacuum > analyze recently. Is there something to tune? I think the real problem here is that the row estimate is off by a factor of thirty: > Seq Scan on admin_event (cost=0.00..19844.37 rows=154361 width=109) (actual time=479.173..2760.186 rows=4705 loops=1) With a correct estimate the indexscan would have been chosen. > galleria=> explain analyze SELECT * FROM admin_event WHERE stamp > (current_timestamp - '1 days'::INTERVAL)::TIMESTAMP WITHOUT TIME ZONE; It's not possible for the planner to make a good guess here since it doesn't know what the comparison value for the stamp column is. (current_timestamp isn't a constant and so the comparison expression can't be reduced to a constant at plan time.) The traditional solution for this is to cheat: create function ago(interval) returns timestamp without time zone as 'select localtimestamp - $1' language sql strict immutable; select * from admin_event where stamp > ago('1 days'); This works because the function is mislabeled as immutable, encouraging the planner to fold the result to a constant on sight. It also has the pleasant property of making your query more readable. The downside is that you are in fact lying to the system about the behavior of the ago() function, and so you can get in trouble. This only really works for queries executed interactively --- you can't use this method inside plpgsql functions, for instance. > Distribution of stamp looks like the following: Hm, you might also find that increasing the statistics target for stamp would be a good idea, since its distribution is so skewed. But unless you do something like the above, the statistics won't get used anyway... regards, tom lane