Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 694916326F5 for ; Fri, 16 Apr 2010 10:00:04 -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 80499-04 for ; Fri, 16 Apr 2010 12:59:45 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ww0-f46.google.com (mail-ww0-f46.google.com [74.125.82.46]) by mail.postgresql.org (Postfix) with ESMTP id 2FB3163276B for ; Fri, 16 Apr 2010 09:59:52 -0300 (ADT) Received: by wwb24 with SMTP id 24so1131144wwb.19 for ; Fri, 16 Apr 2010 05:59:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:received:message-id:subject:from:cc:content-type; bh=jb0mL8vMwfJ4iEeE80C/HUM2XUJ2qencUqPcpezoPBM=; b=CW1meyxQau7GS2EmtQTq7knwvbpet6HIMNS7eBApd74t2F60ZIdMPzMFcDxmSwAgpa lglFlQpqQuT01sgATtJ4lzfP/vLhGDsEYE0Trw9RuMhfVhqJm5bK7Runm+uxqEnLU5PA i5uBO5ztYVR9K3ga1Z/z1z5la3V86AcXedi20= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:cc :content-type; b=vDWcXDGRw4O8VG+P4JTpdyMUN5ZEX5i+STSBJ4NHX+bVPipS4Dzrb6OBpUJGNWEzoB aYbN9NWnntVbIHkLiQtjNF8qtw51TFu3fO6he+p7mUYjzopgnYUhCg30ZpAkUD97jBmp QZ9e5N/uNP1SU5pgthHm15nLT/TktHhU2T4rQ= MIME-Version: 1.0 Received: by 10.216.25.194 with HTTP; Fri, 16 Apr 2010 05:59:50 -0700 (PDT) In-Reply-To: <1271406325.27474.24.camel@hvost> References: <1271406325.27474.24.camel@hvost> Date: Fri, 16 Apr 2010 15:59:50 +0300 Received: by 10.216.183.205 with SMTP id q55mr1750582wem.172.1271422790756; Fri, 16 Apr 2010 05:59:50 -0700 (PDT) Message-ID: Subject: Re: Planner not using column limit specified for one column for another column equal to first From: =?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= Cc: pgsql-performance@postgresql.org Content-Type: multipart/mixed; boundary=0016364d1df1cd755504845a2f48 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.069 tagged_above=-10 required=5 tests=AWL=-0.591, BAYES_05=-0.5, HTML_MESSAGE=0.001, MISSING_HEADERS=1.021 X-Spam-Level: X-Archive-Number: 201004/201 X-Sequence-Number: 38451 --0016364d1df1cd755504845a2f48 Content-Type: multipart/alternative; boundary=0016364d1df1cd754a04845a2f46 --0016364d1df1cd754a04845a2f46 Content-Type: text/plain; charset=KOI8-U Content-Transfer-Encoding: quoted-printable 16 =CB=D7=A6=D4=CE=D1 2010 =D2. 11:25 Hannu Krosing = =CE=C1=D0=C9=D3=C1=D7: > On Fri, 2010-04-16 at 11:02 +0300, =F7=A6=D4=C1=CC=A6=CA =F4=C9=CD=DE=C9= =DB=C9=CE 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=3Dthis_1_.company_id left outer join company_measures > > companymea2_ on this_.id=3Dcompanymea2_.company_id left outer join > > company_descr ces3_ on this_.id=3Dces3_.company_id where this_1_.tag_id > > =3D 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=3Dthis_1_.company_id left outer join company_measures > > companymea2_ on this_.id=3Dcompanymea2_.company_id left outer join > > company_descr ces3_ on this_.id=3Dces3_.company_id where this_1_.tag_id > > =3D 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=3Dthis_1_.company_id > and this_1_.company_id>50000000) > left outer join company_measures companymea2_ > on (this_.id=3Dcompanymea2_.company_id > and companymea2_.company_id>50000000) > left outer join company_descr ces3_ > on (this_.id=3Dces3_.company_id > and ces3_.company_id>50000000) > where this_1_.tag_id =3D 7 > and this_.id>50000000 > order by this_.id asc > limit 1000; > And it's still fast (see plan in another mail), while "inner join" variant of original query is still slow. > > > 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 > > Actually no, select id > 50000000, count(*) from company group by 1 f,1096042 t,5725630 I don't know why the planner wishes to perform few merges of 1000 to a million of records (and the merges is the thing that takes time) instead of taking a 1000 of records from main table and then doing a nested loop. And it must read all the records that DO NOT match the criteria for secondary tables before getting to correct records if it do not filter secondary tables with index on retrieve. set enable_mergejoin=3Dfalse helps original query, but this is another prob= lem and first solution is simpler and can be used by planner automatically, while second requires rethinking/rewrite of LIMIT estimation logic (Plan of nested loop attached) --0016364d1df1cd754a04845a2f46 Content-Type: text/html; charset=KOI8-U Content-Transfer-Encoding: quoted-printable

16 =CB=D7=A6=D4=CE=D1 2010 =D2. 11:25 Ha= nnu Krosing <= hannu@2ndquadrant.com> =CE=C1=D0=C9=D3=C1=D7:
On Fri, 2010-04-16 at 11:02 +0300, =F7=A6=D4=C1=CC=A6=CA = =F4=C9=CD=DE=C9=DB=C9=CE 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=3Dthis_1_.company_id left outer join company_measures
> companymea2_ on this_.id=3Dcompanymea2_.company_id left outer join
> company_descr ces3_ on this_.id=3Dces3_.company_id where this_1_.tag_i= d
> =3D 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=3Dthis_1_.company_id left outer join company_measures
> companymea2_ on this_.id=3Dcompanymea2_.company_id left outer join
> company_descr ces3_ on this_.id=3Dces3_.company_id where this_1_.tag_i= d
> =3D 7 and this_.id>50000000
> and this_1_.company_id>50000000
> and companymea2_.company_id>50000000 and ces3_.company_id>500000= 00
> 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_
=9Aleft outer join company_tag this_1_
=9A =9A =9A =9A =9A =9A =9A on (this_.id=3Dthis_1_.company_id
=9A =9A =9A =9A =9A =9A =9A and this_1_.company_id>50000000)
=9Aleft outer join company_measures companymea2_
=9A =9A =9A =9A =9A =9A =9A on (this_.id=3Dcompanymea2_.company_id
=9A =9A =9A =9A =9A =9A =9A and companymea2_.company_id>50000000)=
=9Aleft outer join company_descr ces3_
=9A =9A =9A =9A =9A =9A =9A on (this_.id=3Dces3_.company_id
=9A =9A =9A =9A =9A =9A =9A and ces3_.company_id>50000000)
=9Awhere this_1_.tag_id =3D 7
=9A and this_.id>50000000
=9Aorder by this_.id asc
=9Alimit 1000;

And it's still= fast (see plan in another mail), while "inner join" variant of o= riginal query is still slow.
=9A


I'm not sure that planner considers the above form of plan rewrit= e, nor
that it would make much sense to do so unless there was a really small
number of rows where x_.company_id>50000000

Actually no,
select id > 50000000, count(*) from company group by 1
f,1096042
t,5725630

I don't kn= ow why the planner wishes to perform few merges of 1000 to a million of rec= ords (and the merges is the thing that takes time) instead of taking a 1000= of records from main table and then doing a nested loop. And it must read = all the records that DO NOT match the criteria for secondary tables before = getting to correct records if it do not filter secondary tables with index = on retrieve.

set enable_mergejoin=3Dfalse helps original query, but = this is another problem and first solution is=9Asimpler=9Aand can be used b= y planner automatically, while second requires rethinking/rewrite of LIMIT = estimation logic
(Plan of nested loop attached)

--0016364d1df1cd754a04845a2f46-- --0016364d1df1cd755504845a2f48 Content-Type: text/plain; charset=US-ASCII; name="nestplan.txt" Content-Disposition: attachment; filename="nestplan.txt" Content-Transfer-Encoding: base64 X-Attachment-Id: f_g8306swg0 TGltaXQgIChjb3N0PTAuMDAuLjIzNjkuMzYgcm93cz0xMDAwIHdpZHRoPTEyMDkpIChhY3R1YWwg dGltZT0wLjE3OS4uNDEuMTU1IHJvd3M9MTAwMCBsb29wcz0xKQogIC0+ICBOZXN0ZWQgTG9vcCAg KGNvc3Q9MC4wMC4uMTU3Mjc5NDAuNDEgcm93cz02NjM4MDQ2IHdpZHRoPTEyMDkpIChhY3R1YWwg dGltZT0wLjE3NC4uMzguMzEyIHJvd3M9MTAwMCBsb29wcz0xKQogICAgICAgIC0+ICBOZXN0ZWQg TG9vcCAgKGNvc3Q9MC4wMC4uMTExNjU0ODMuNzUgcm93cz02NzAxMjI0IHdpZHRoPTExNDEpIChh Y3R1YWwgdGltZT0wLjEzNC4uMjYuNDIxIHJvd3M9MTAwMCBsb29wcz0xKQogICAgICAgICAgICAg IC0+ICBOZXN0ZWQgTG9vcCAgKGNvc3Q9MC4wMC4uNTc2Mzg0NC4wMyByb3dzPTY3NjUwMDQgd2lk dGg9NjM5KSAoYWN0dWFsIHRpbWU9MC4wNjYuLjE0LjM4OSByb3dzPTEwMDAgbG9vcHM9MSkKICAg ICAgICAgICAgICAgICAgICAtPiAgSW5kZXggU2NhbiB1c2luZyBwa19jb21wX20gb24gY29tcGFu eSB0aGlzXyAgKGNvc3Q9MC4wMC4uMTE1MjkzNi43NyByb3dzPTY4ODY1OTggd2lkdGg9NjIzKSAo YWN0dWFsIHRpbWU9MC4wMzguLjEuOTA4IHJvd3M9MTA1NCBsb29wcz0xKQogICAgICAgICAgICAg ICAgICAgICAgICAgIEluZGV4IENvbmQ6IChpZCA+IDUwMDAwMDAwKQogICAgICAgICAgICAgICAg ICAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIGNvbXBhbnlfdGFnX2NvbXBhbnlfaWRfaWR4IG9uIGNv bXBhbnlfdGFnIHRoaXNfMV8gIChjb3N0PTAuMDAuLjAuNjYgcm93cz0xIHdpZHRoPTE2KSAoYWN0 dWFsIHRpbWU9MC4wMDQuLjAuMDA2IHJvd3M9MSBsb29wcz0xMDU0KQogICAgICAgICAgICAgICAg ICAgICAgICAgIEluZGV4IENvbmQ6ICgodGhpc18xXy5jb21wYW55X2lkKTo6YmlnaW50ID0gdGhp c18uaWQpCiAgICAgICAgICAgICAgICAgICAgICAgICAgRmlsdGVyOiAoKHRoaXNfMV8udGFnX2lk KTo6YmlnaW50ID0gNykKICAgICAgICAgICAgICAtPiAgSW5kZXggU2NhbiB1c2luZyBjd3NfY29t cGFueV9kZXNjcl91bmlxdWUgb24gY29tcGFueV9kZXNjciBjZXMzXyAgKGNvc3Q9MC4wMC4uMC43 OSByb3dzPTEgd2lkdGg9NTAyKSAoYWN0dWFsIHRpbWU9MC4wMDQuLjAuMDA2IHJvd3M9MSBsb29w cz0xMDAwKQogICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6ICgoY2VzM18uY29tcGFueV9p ZCk6OmJpZ2ludCA9IHRoaXNfLmlkKQogICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIHBrX2Nv bXBfbXMgb24gY29tcGFueV9tZWFzdXJlcyBjb21wYW55bWVhMl8gIChjb3N0PTAuMDAuLjAuNjcg cm93cz0xIHdpZHRoPTY4KSAoYWN0dWFsIHRpbWU9MC4wMDQuLjAuMDA2IHJvd3M9MSBsb29wcz0x MDAwKQogICAgICAgICAgICAgIEluZGV4IENvbmQ6ICgoY29tcGFueW1lYTJfLmNvbXBhbnlfaWQp OjpiaWdpbnQgPSB0aGlzXy5pZCkKVG90YWwgcnVudGltZTogNDIuOTQwIG1zCgo= --0016364d1df1cd755504845a2f48--