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 4616056381 for ; Mon, 14 Mar 2005 14:51:25 +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 41105-10 for ; Mon, 14 Mar 2005 14:51:19 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id F136D563A5 for ; Mon, 14 Mar 2005 14:51:18 +0000 (GMT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id D695D355AE; Mon, 14 Mar 2005 06:50:21 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id D4F8B35596; Mon, 14 Mar 2005 06:50:21 -0800 (PST) Date: Mon, 14 Mar 2005 06:50:21 -0800 (PST) From: Stephan Szabo To: Michael McFarland Cc: pgsql-performance@postgresql.org Subject: Re: adding 'limit' leads to very slow query In-Reply-To: Message-ID: <20050314064241.V6400@megazone.bigpanda.com> References: <20050307230123.L97147@megazone.bigpanda.com> 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.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/257 X-Sequence-Number: 10962 On Wed, 9 Mar 2005, Michael McFarland wrote: > I continue to be stumped by this. You are right that I should have > listed the estimates provided by explain... basically for the select where > bar = 41, it's estimating there will be 40,000 rows instead of 7, out of > what's actuallly 5 million records in the table. > > So far I've tried increase statistics for the bar column from the > default 10 to 100 (vacuum analyzing after) and the explain-plan hasn't > changed. I also notice that afterward, the pg_stats record for the bar Did the estimates change at all? > column still only lists the top 5 values of bar (out of 68 unique values > in the table). Are there any other settings I could try to improve the > detail of the statistics? Well, I'd first try moving up to a statistic target of 1000 in order to try sampling a greater number of rows. I'd wonder if there's enough difference in frequency that it's just not visiting any with the other values. I'm not sure that it'll help that much though; hopefully someone else will have an idea. > By the way, I think I do have a workaround for this particular query: > select * from (select * from foo where barId = 412 order by id > desc) as tempview limit 25; > This query uses the bar index and completes instantly. However, I feel > like I should find the heart of the problem, since bad statistics could > end up affecting other plans, right? Yeah, it's best to get it to estimate somewhat reasonably before looking for workarounds.