X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 124E7D1B444 for ; Sun, 4 Jan 2004 23:37:08 +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 50082-05 for ; Sun, 4 Jan 2004 19:36:20 -0400 (AST) Received: from sccrmhc11.comcast.net (sccrmhc11.comcast.net [204.127.202.55]) by svr1.postgresql.org (Postfix) with ESMTP id 69588D1B4A7 for ; Sun, 4 Jan 2004 19:36:17 -0400 (AST) Received: from [10.0.1.3] (h000393e3ce55.ne.client2.attbi.com[24.91.235.158]) by comcast.net (sccrmhc11) with SMTP id <20040104233619011006cu21e>; Sun, 4 Jan 2004 23:36:19 +0000 User-Agent: Microsoft-Entourage/10.1.1.2418 Date: Sun, 04 Jan 2004 18:36:16 -0500 Subject: Use my (date) index, darn it! From: John Siracusa To: Message-ID: Mime-version: 1.0 Reply-To: siracusa@mindspring.com Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/24 X-Sequence-Number: 5199 I have a very large table (about a million rows) which I most frequently want to select a subset of rows from base on a date field. That date field is indexed, and when Postgres uses that index, queries are fast. But sometimes it decides not to use the index, resorting to a sequential scan instead. This is really, really slow. To try to convince it to use my date index, I turned off the sequential scan strategy in the planner. That worked on one copy of the db, but not on another, where it decided to use an index from another column entirely, which didn't help performance. I dropped the other index, leaving only the date index, and performance was good again. Obviously the planner is making some bad choices here. I know that it is trying to avoid random seeks or other scary things implied by a "correlation" statistic that is not close to 1 or -1, but it is massively overestimating the hit caused by those seeks and seemingly not taking into account the size of the table! This is Postgres 7.4 on Linux and Mac OS X, BTW. Anyway, to "fix" the situation, I clustered the table on the date column. But I fear that the data will slowly "drift" back to a state where the planner decides again that a sequential scan is a good idea. Blah. So, my question: how can I prevent this? Ideally, the planner should be smarter. Failing that, I'd like to be able to force it to use the index that I know will result in the fastest queries (3 seconds vs. 30 seconds in many cases). Suggestions? -John