Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id F14BF63520D for ; Fri, 16 Apr 2010 05:25:39 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 25880-01 for ; Fri, 16 Apr 2010 08:25:24 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from serenity.inversion.ee (serenity.inversion.ee [194.204.33.112]) by mail.postgresql.org (Postfix) with ESMTP id 098E6634C6B for ; Fri, 16 Apr 2010 05:25:28 -0300 (ADT) Received: by serenity.inversion.ee (Postfix, from userid 108) id 648A4E34635; Fri, 16 Apr 2010 10:10:58 +0300 (EEST) Received: from [IPv6:::1] (localhost [127.0.0.1]) by serenity.inversion.ee (Postfix) with ESMTP id C50FDE3421C; Fri, 16 Apr 2010 10:10:57 +0300 (EEST) Subject: Re: Planner not using column limit specified for one column for another column equal to first From: Hannu Krosing To: =?UTF-8?Q?=D0=92=D1=96=D1=82=D0=B0=D0=BB=D1=96=D0=B9_?= =?UTF-8?Q?=D0=A2=D0=B8=D0=BC=D1=87=D0=B8=D1=88=D0=B8=D0=BD?= Cc: pgsql-performance@postgresql.org In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Date: Fri, 16 Apr 2010 11:25:25 +0300 Message-ID: <1271406325.27474.24.camel@hvost> Mime-Version: 1.0 X-Mailer: Evolution 2.28.1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.149 tagged_above=-10 required=5 tests=AWL=-1.148, BAYES_40=-0.001 X-Spam-Level: X-Archive-Number: 201004/237 X-Sequence-Number: 38487 On Fri, 2010-04-16 at 11:02 +0300, Віталій Тимчишин wrote: > Hello. > > > I have a query that performs very poor because there is a limit on > join column that is not applied to other columns: > > > select * from company this_ left outer join company_tag this_1_ on > this_.id=this_1_.company_id left outer join company_measures > companymea2_ on this_.id=companymea2_.company_id left outer join > company_descr ces3_ on this_.id=ces3_.company_id where this_1_.tag_id > = 7 and this_.id>50000000 > and this_1_.company_id>50000000 > order by this_.id asc limit 1000; > > > (plan1.txt) > Total runtime: 7794.692 ms > > > At the same time if I apply the limit (>50000000) to other columns in > query itself it works like a charm: > > > select * from company this_ left outer join company_tag this_1_ on > this_.id=this_1_.company_id left outer join company_measures > companymea2_ on this_.id=companymea2_.company_id left outer join > company_descr ces3_ on this_.id=ces3_.company_id where this_1_.tag_id > = 7 and this_.id>50000000 > and this_1_.company_id>50000000 > and companymea2_.company_id>50000000 and ces3_.company_id>50000000 > order by this_.id asc limit 1000; The queries are not the same. 2nd variant will not return the rows where there are no matching rows inthis_1_ , companymea2_ or ces3_.company_id A query equivalent to first one would be: select * from company this_ left outer join company_tag this_1_ on (this_.id=this_1_.company_id and this_1_.company_id>50000000) left outer join company_measures companymea2_ on (this_.id=companymea2_.company_id and companymea2_.company_id>50000000) left outer join company_descr ces3_ on (this_.id=ces3_.company_id and ces3_.company_id>50000000) where this_1_.tag_id = 7 and this_.id>50000000 order by this_.id asc limit 1000; I'm not sure that planner considers the above form of plan rewrite, nor that it would make much sense to do so unless there was a really small number of rows where x_.company_id>50000000 -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training