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 5DA77D1BA4C for ; Mon, 23 Feb 2004 00:09:15 +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 95762-06 for ; Sun, 22 Feb 2004 20:09:11 -0400 (AST) Received: from rwcrmhc13.comcast.net (rwcrmhc13.comcast.net [204.127.198.39]) by svr1.postgresql.org (Postfix) with ESMTP id BE64FD1B9BE for ; Sun, 22 Feb 2004 20:09:07 -0400 (AST) Received: from [10.0.1.2] (h000393e3ce55.ne.client2.attbi.com[24.91.235.158]) by comcast.net (rwcrmhc13) with SMTP id <2004022300090801500i05jae>; Mon, 23 Feb 2004 00:09:08 +0000 User-Agent: Microsoft-Entourage/10.1.4.030702.0 Date: Sun, 22 Feb 2004 19:09:07 -0500 Subject: Re: Column correlation drifts, index ignored again From: John Siracusa To: Postgres Performance Message-ID: In-Reply-To: <19441.1077493226@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/256 X-Sequence-Number: 5780 On 2/22/04 6:40 PM, Tom Lane wrote: > John Siracusa writes: >> I think the key is to get the planner to correctly ballpark the number of >> rows in the date range. > > I thought it was. What you showed was > > -> Index Scan using mytable_date_idx on mytable (cost=0.00..3071.70 rows=52155 > width=23) (actual time=2.610..1688.111 rows=49679 loops=1) > > which seemed plenty close enough to me. That's after the planner correctly chooses the date index. Unfortunately, I forgot to save the EXPLAIN output from when it was choosing seqscan instead. Does the planner get estimates from both plans before deciding whether or not to use the one that references the date index? -John