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 A9881D1C9FB for ; Mon, 12 Apr 2004 14:51:32 -0300 (ADT) 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 04791-04 for ; Mon, 12 Apr 2004 14:51:30 -0300 (ADT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id AA3ADD1C9C7 for ; Mon, 12 Apr 2004 14:51:28 -0300 (ADT) 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 i3CHpSpd012038; Mon, 12 Apr 2004 13:51:28 -0400 (EDT) To: jdunn@autorevenue.com Cc: "Postgresql Performance" Subject: Re: index v. seqscan for certain values In-reply-to: <000901c420a4$7b733650$4f01a8c0@jeremydunn> References: <000901c420a4$7b733650$4f01a8c0@jeremydunn> Comments: In-reply-to "Jeremy Dunn" message dated "Mon, 12 Apr 2004 11:40:28 -0400" Date: Mon, 12 Apr 2004 13:51:28 -0400 Message-ID: <12037.1081792288@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/136 X-Sequence-Number: 6436 "Jeremy Dunn" writes: > The question: why does the planner consider a sequential scan to be > better for these top 10 values? At some point a seqscan *will* be better. In the limit, if the key being sought is common enough to occur on every page of the table, it's certain that a seqscan will require less I/O than an indexscan (because reading the index isn't actually saving you any heap fetches). In practice the breakeven point is less than that because Unix kernels are better at handling sequential than random access. Your gripe appears to be basically that the planner's idea of the breakeven point is off a bit. It looks to me like it's within about a factor of 2 of being right, though, which is not all that bad when it's using generic cost parameters. > A) alter table xxx alter column cid set statistics 500; > analyze xxx; > This does not affect the results. It probably improved the accuracy of the row count estimates, no? The estimate you show for cid=7191032 is off by more than 25% (37765 vs 50792), which seems like a lot of error for one of the most common values in the table. (I hope that was with default stats target and not 500.) That leads directly to a 25% overestimate of the cost of an indexscan, while having IIRC no impact on the cost of a seqscan. Since the cost ratio was more than 25%, this didn't change the selected plan, but you want to fix that error as best you can before you move on to tweaking cost parameters. > C) decreasing cpu_index_tuple_cost by a factor of up to 1000, with no > success Wrong thing. You should be tweaking random_page_cost. Looks to me like a value near 2 might be appropriate for your setup. Also it is likely appropriate to increase effective_cache_size, which is awfully small in the default configuration. I'd set that to something related to your available RAM before trying to home in on a suitable random_page_cost. AFAIK hardly anyone bothers with changing the cpu_xxx costs ... regards, tom lane