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 17FC13CCE1D for ; Fri, 17 Dec 2004 15:48:44 +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 23864-02 for ; Fri, 17 Dec 2004 15:48:32 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id A43413CC64C for ; Fri, 17 Dec 2004 15:48:33 +0000 (GMT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1CfKKj-00067I-00; Fri, 17 Dec 2004 10:47:57 -0500 To: Richard Huxton Cc: David Brown , pgsql-performance@postgresql.org Subject: Re: Seqscan rather than Index References: <20041217011853.9E8FF3CADB0@svr1.postgresql.org> <41C2D936.3060901@archonet.com> In-Reply-To: <41C2D936.3060901@archonet.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 17 Dec 2004 10:47:57 -0500 Message-ID: <87acsdkjte.fsf@stark.xeocode.com> Lines: 46 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.049 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200412/213 X-Sequence-Number: 9588 Richard Huxton writes: > 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 doesn't make sense or else it would switch to the index at random_page_cost = 1.0. If it was still using a sequential scan at random_page_cost < 1 then perhaps he had some problem with his query like mismatched data types that forced it to use a full scan. > > 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? The most common cause I've seen here is that Postgres makes very pessimistic assumptions about selectivity when it doesn't know better. Every other database I've tested assumes 'col > ?' is about 5% selectivity . Postgres assumes 33%. Postgres is also more pessimistic about the efficiency of index scans. It's willing to use a sequential scan down to well below 5% selectivity when other databases use the more traditional rule of thumb of 10%. In combination these effects do seem to cause an _awful_ lot of complaints. > > 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? I don't know what he's talking about either. Perhaps he's thinking of people who haven't been running vacuum enough? -- greg