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 BE81956374 for ; Wed, 9 Mar 2005 16:00:27 +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 59062-08 for ; Wed, 9 Mar 2005 16:00:25 +0000 (GMT) Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.250.190.2]) by svr1.postgresql.org (Postfix) with ESMTP id 8C2CC55A07 for ; Wed, 9 Mar 2005 16:00:24 +0000 (GMT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 5FAE431E23; Wed, 9 Mar 2005 17:00:23 +0100 (MET) From: "Michael McFarland" X-Newsgroups: pgsql.performance Subject: Re: adding 'limit' leads to very slow query Date: Wed, 09 Mar 2005 11:00:20 -0500 Organization: Hub.Org Networking Services Lines: 91 Message-ID: References: <20050307230123.L97147@megazone.bigpanda.com> Mime-Version: 1.0 Content-Type: text/plain; format=flowed; delsp=yes; charset=us-ascii Content-Transfer-Encoding: Quoted-Printable X-Complaints-To: usenet@news.hub.org User-Agent: Opera M2(BETA1)/8.00 (Linux, build 913) To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.174 tagged_above=0 required=5 tests=FORGED_YAHOO_RCVD X-Spam-Level: ** X-Archive-Number: 200503/238 X-Sequence-Number: 10943 I continue to be stumped by this. You are right that I should have = = listed the estimates provided by explain... basically for the select whe= re = bar =3D 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 = = 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? By the way, I think I do have a workaround for this particular query= : select * from (select * from foo where barId =3D 412 order by i= d = 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? - Mike On Mon, 7 Mar 2005 23:03:43 -0800 (PST), Stephan Szabo = wrote: > On Mon, 7 Mar 2005, Michael McFarland wrote: > >> I'm trying to understand why a particular query is slow, and it se= ems >> like the optimizer is choosing a strange plan. See this summary: >> >> >> * I have a large table, with an index on the primary key 'id' and on = a >> field 'foo'. >> > select count(*) from foo; >> 1,000,000 >> > select count(*) from foo where bar =3D 41; >> 7 >> >> * This query happens very quickly. >> > explain select * from foo where barId =3D 412 order by id desc; >> Sort () >> Sort key=3D id >> -> Index scan using bar_index on foo () >> Index cond: barId =3D 412 >> >> But this query takes forever >> >> > explain select * from foo where barId =3D 412 order by id desc limi= t 25; >> Limit () >> -> Index scan backward using primarykey_index >> Filter: barID =3D 412 > > You didn't show the row estimates, but I'd guess that it's expecting > either that ther are more rows that match barId=3D412 than there actua= lly > are (which may be solvable by raising the statistics target on the col= umn > and re-analyzing) such that going backwards on id in order to make 25 > matching rows isn't a bad plan or that barId and id are correlated whi= ch > is unfortunately not going to be recognized right now. > -- = Using Opera's revolutionary e-mail client: http://www.opera.com/mail/