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 877C0D1B9C5 for ; Sun, 22 Feb 2004 22:34:49 +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 76203-07 for ; Sun, 22 Feb 2004 18:34:50 -0400 (AST) Received: from rwcrmhc13.comcast.net (rwcrmhc13.comcast.net [204.127.198.39]) by svr1.postgresql.org (Postfix) with ESMTP id 4D2B2D1B9BE for ; Sun, 22 Feb 2004 18:34:47 -0400 (AST) Received: from [10.0.1.2] (h000393e3ce55.ne.client2.attbi.com[24.91.235.158]) by comcast.net (rwcrmhc13) with SMTP id <2004022222344901500hrnppe>; Sun, 22 Feb 2004 22:34:49 +0000 User-Agent: Microsoft-Entourage/10.1.4.030702.0 Date: Sun, 22 Feb 2004 17:34:47 -0500 Subject: Re: Column correlation drifts, index ignored again From: John Siracusa To: Postgres Performance Message-ID: In-Reply-To: <18941.1077487618@sss.pgh.pa.us> 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: 200402/253 X-Sequence-Number: 5777 On 2/22/04 5:06 PM, Tom Lane wrote: > John Siracusa writes: >> I want to do something that will convince Postgres that using the date >> index is, by far, the best plan when running my queries, even when the >> date column correlation stat drops well below 1.0. > > Have you tried experimenting with random_page_cost? Seems like your > results suggest that you need to lower it. I don't want to do anything that "universal" if I can help it, because I don't want to adversely affect any other queries that the planner currently aces. I'm guessing that the reason using the date index is always so much faster is that doing so only reads the rows in the date range (say, 1,000 of them) instead of reading every single row in the table (1,000,000) as in a seqscan plan. I think the key is to get the planner to correctly ballpark the number of rows in the date range. If it does, I can't imagine it ever deciding to read 1,000,000 rows instead of 1,000 with any sane "cost" setting. I'm assuming the defaults are sane :) -John