X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 59F063CC9A3 for ; Fri, 17 Dec 2004 13:04:01 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 52421-04 for ; Fri, 17 Dec 2004 13:03:55 +0000 (GMT) Received: from anchor-post-35.mail.demon.net (anchor-post-35.mail.demon.net [194.217.242.85]) by svr1.postgresql.org (Postfix) with ESMTP id 66F2F3CC982 for ; Fri, 17 Dec 2004 13:03:57 +0000 (GMT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-35.mail.demon.net with esmtp (Exim 4.42) id 1CfHlI-000N7e-Gh; Fri, 17 Dec 2004 13:03:13 +0000 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id B80DF162E6; Fri, 17 Dec 2004 13:03:55 +0000 (GMT) Message-ID: <41C2D936.3060901@archonet.com> Date: Fri, 17 Dec 2004 13:03:50 +0000 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: David Brown Cc: pgsql-performance@postgresql.org Subject: Re: Seqscan rather than Index References: <20041217011853.9E8FF3CADB0@svr1.postgresql.org> In-Reply-To: <20041217011853.9E8FF3CADB0@svr1.postgresql.org> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.087 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200412/212 X-Sequence-Number: 9587 David Brown wrote: >> You might want to reduce random_page_cost a little. > > >> Keep in mind that your test case is small enough to fit in RAM and >> is probably not reflective of what will happen with larger tables. > > > I am also running 8.0 rc1 for Windows. Despite many hours spent > tweaking various planner cost constants, I found little effect on > cost estimates. Even reducing random_page_cost from 4.0 to 0.1 had > negligible impact and failed to significantly influence the planner. I'm not sure setting random_page_cost below 1.0 makes much sense. > Increasing the statistics target for the last_name column to 250 or > so *may* help, at least if you're only selecting one name at a time. Not going to do anything in this case. The planner is roughly right about how many rows will be returned, it's just not expecting everything to be in RAM. > That's the standard advice around here and the only thing I've found > useful. Half the threads in this forum are about under-utilized > indexes. It would be great if someone could admit the planner is > broken and talk about actually fixing it! Not sure I agree here - when the stats are accurate, you can get the planner to make near-optimal choices most of the time. Is there any particular pattern you've seen? > I'm unconvinced that the planner only favours sequential scans as > table size decreases. In my experience so far, larger tables have the > same problem only it's more noticeable. Hmm - assuming your statistics are good, this would suggest the other cost settings just aren't right for your hardware. > The issue hits PostgreSQL harder than others because of its awful > sequential scan speed, which is two to five times slower than other > DBMS. The archives show there has been talk for years about this, but > it seems, no solution. The obvious thing to consider is the block > size, but people have tried increasing this in the past with only > marginal success. Must admit this puzzles me. Are you saying you can't saturate your disk I/O? Or are you saying other DBMS store records in 0.5 to 0.2 times less space than PG? -- Richard Huxton Archonet Ltd