Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 7904363276B for ; Fri, 16 Apr 2010 09:50:00 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 57979-06 for ; Fri, 16 Apr 2010 12:49:49 +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 6A608632563 for ; Fri, 16 Apr 2010 09:49:49 -0300 (ADT) Received: by wwb24 with SMTP id 24so1125008wwb.19 for ; Fri, 16 Apr 2010 05:49:47 -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:to:cc:content-type; bh=gty7LZnyOH0vXiNPBeWKBA6nnvUEt3p93TPU6pNOF6k=; b=xDk0OqvkC+zwW0RQSzmWrK8OXfmsszBz+IRxZTIJvHTJbFfFS+Gm7yXXK+1pKo22+X /plFxuUQMOM6FKAeZq88356ArjCogFWtZZKsoF3JAuSCJon89IPVH768iHu8J/JCUJk8 e1UVKI8zeFZ4dy5EVUV0CdGuRZ3HwPBGGsuVA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=KJJlOAzLY1RnISbYDuludbYQUQIXurV1SV4q5jqzf2jGqm1sU1uHJu5bPKDv5UzAV5 65o1u/TwpPGQLzYb12nEshAQLoovXIIW+FHbjGSYXnqgqxvH/plpb7+qyTE5weQ5cftu IYSIE5A88sPNTmpdbjxAuXCEojhzF2VGgd4sU= MIME-Version: 1.0 Received: by 10.216.25.194 with HTTP; Fri, 16 Apr 2010 05:49:45 -0700 (PDT) In-Reply-To: <4BC8204A.7050904@gmail.com> References: <4BC8204A.7050904@gmail.com> Date: Fri, 16 Apr 2010 15:49:45 +0300 Received: by 10.216.174.76 with SMTP id w54mr1678557wel.213.1271422185690; Fri, 16 Apr 2010 05:49:45 -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==?= To: Yeb Havinga Cc: pgsql-performance@postgresql.org Content-Type: multipart/mixed; boundary=001485f1d92abcd86e04845a0b39 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.869 tagged_above=-10 required=5 tests=BAYES_40=-0.001, FREEMAIL_ENVFROM_END_DIGIT=1.553, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, SARE_MILLIONSOF=0.315 X-Spam-Level: * X-Archive-Number: 201004/200 X-Sequence-Number: 38450 --001485f1d92abcd86e04845a0b39 Content-Type: multipart/alternative; boundary=001485f1d92abcd86504845a0b37 --001485f1d92abcd86504845a0b37 Content-Type: text/plain; charset=KOI8-U Content-Transfer-Encoding: quoted-printable 16 =CB=D7=A6=D4=CE=D1 2010 =D2. 11:31 Yeb Havinga = =CE=C1=D0=C9=D3=C1=D7: > =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 companyme= a2_ on >> this_.id=3Dcompanymea2_.company_id left outer join company_descr ces3_ o= n >> this_.id=3Dces3_.company_id where this_1_.tag_id =3D 7 and this_.id>5000= 0000 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 companyme= a2_ on >> this_.id=3Dcompanymea2_.company_id left outer join company_descr ces3_ o= n >> this_.id=3Dces3_.company_id where this_1_.tag_id =3D 7 and this_.id>5000= 0000 and >> this_1_.company_id>50000000 >> and companymea2_.company_id>50000000 and ces3_.company_id>50000000 >> order by this_.id asc limit 1000; >> >> (plan2.txt) >> Total runtime: 27.547 ms >> >> I've thought and someone in this list've told me that this should be don= e >> automatically. >> > Yes, if you have in a query a=3Db and b=3Dc, then the optimizer figures o= ut > that a=3Dc as well. (a,b and c are then member of the same equivalence cl= ass). > > However both queries are not the same, since the joins you're using are > outer joins. In the first it's possible that records are returned for > company records with no matching ces3_ records, the ces3_ records is null= in > that case. In the second query no NULL ces3_ information may be returned. > OK, but when I move limit to join condition the query is still fast: 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 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 and this_1_.company_id>50000000 order by this_.id asc limit 1000; (plan3.txt), Total runtime: 26.327 ms BTW: Changing slow query to inner joins do not make it fast > > Another thing is it seems that the number of rows guessed is far off from > the actual number of rows, is the number 5000000 artificial or are you're > statistics old or too small histogram/mcv's? > Nope, I suppose this is because of limit. If I remove the limit, the estimations are quite correct. There are ~6 millions of row in each table. --001485f1d92abcd86504845a0b37 Content-Type: text/html; charset=KOI8-U Content-Transfer-Encoding: quoted-printable

16 =CB=D7=A6=D4=CE=D1 2010 =D2. 11:31 Ye= b Havinga <yeb= havinga@gmail.com> =CE=C1=D0=C9=D3=C1=D7:
=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 col= umn 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 qu= ery 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;

(plan2.txt)
Total runtime: 27.547 ms

I've thought and someone in this list've told me that this should b= e done automatically.
Yes, if you have in a query a=3Db and b=3Dc, then the optimizer figures out= that a=3Dc as well. (a,b and c are then member of the same equivalence cla= ss).

However both queries are not the same, since the joins you're using are= outer joins. In the first it's possible that records are returned for = company records with no matching ces3_ records, the ces3_ records is null i= n that case. In the second query no NULL ces3_ information may be returned.=

OK, but when I move limit to join conditio= n the query is still fast:

select * from comp= any this_ left outer join company_tag this_1_ on this_.id=3Dthis_1_.company= _id=9A
left outer join company_measures companymea2_ on this_.id=3Dcompanymea= 2_.company_id and companymea2_.company_id>50000000
left outer = join company_descr ces3_ on this_.id=3Dces3_.company_id and ces3_.company_i= d>50000000
where this_1_.tag_id =3D 7 and this_.id>50000000=9A
and t= his_1_.company_id>50000000
order by this_.id asc limit 1000;

(plan3.txt),
Total runtime: 26.327 = ms
BTW: Changing slow query to inner joins do not make it fast
= =9A

Another thing is it seems that the number of rows guessed is far off from t= he actual number of rows, is the number 5000000 artificial or are you'r= e statistics old or too small histogram/mcv's?

Nope, I sup= pose this is because of limit. If I remove the limit, the estimations are q= uite correct. There are ~6 millions of row in each table.

--001485f1d92abcd86504845a0b37-- --001485f1d92abcd86e04845a0b39 Content-Type: text/plain; charset=US-ASCII; name="plan3.txt" Content-Disposition: attachment; filename="plan3.txt" Content-Transfer-Encoding: base64 X-Attachment-Id: f_g82zrfb50 TGltaXQgIChjb3N0PTI5My40MC4uMTAyOC42MCByb3dzPTEwMDAgd2lkdGg9MTIwOSkgKGFjdHVh bCB0aW1lPTAuMTI4Li4yNC43NTEgcm93cz0xMDAwIGxvb3BzPTEpCiAgLT4gIE1lcmdlIExlZnQg Sm9pbiAgKGNvc3Q9MjkzLjQwLi40MTk3NzMxLjExIHJvd3M9NTcwOTI0MyB3aWR0aD0xMjA5KSAo YWN0dWFsIHRpbWU9MC4xMjQuLjIxLjk2OCByb3dzPTEwMDAgbG9vcHM9MSkKICAgICAgICBNZXJn ZSBDb25kOiAodGhpc18uaWQgPSAoY29tcGFueW1lYTJfLmNvbXBhbnlfaWQpOjpiaWdpbnQpCiAg ICAgICAgLT4gIE1lcmdlIExlZnQgSm9pbiAgKGNvc3Q9MjQ2LjU5Li4zNjgxMjMwLjEwIHJvd3M9 NTcwOTI0MyB3aWR0aD0xMTQxKSAoYWN0dWFsIHRpbWU9MC4wOTkuLjE1LjI4NCByb3dzPTEwMDAg bG9vcHM9MSkKICAgICAgICAgICAgICBNZXJnZSBDb25kOiAodGhpc18uaWQgPSAoY2VzM18uY29t cGFueV9pZCk6OmJpZ2ludCkKICAgICAgICAgICAgICAtPiAgTWVyZ2UgSm9pbiAgKGNvc3Q9Mzcu ODcuLjI0MzU1MzYuMDAgcm93cz01NzA5MjQzIHdpZHRoPTYzOSkgKGFjdHVhbCB0aW1lPTAuMDc0 Li44LjQ4NyByb3dzPTEwMDAgbG9vcHM9MSkKICAgICAgICAgICAgICAgICAgICBNZXJnZSBDb25k OiAodGhpc18uaWQgPSAodGhpc18xXy5jb21wYW55X2lkKTo6YmlnaW50KQogICAgICAgICAgICAg ICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIHBrX2NvbXBfbSBvbiBjb21wYW55IHRoaXNfICAo Y29zdD0wLjAwLi4yMTM5MzI0LjUzIHJvd3M9Njg4NjU5OCB3aWR0aD02MjMpIChhY3R1YWwgdGlt ZT0wLjAzNy4uMS44NjAgcm93cz0xMDU0IGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAgICAg ICAgSW5kZXggQ29uZDogKGlkID4gNTAwMDAwMDApCiAgICAgICAgICAgICAgICAgICAgLT4gIElu ZGV4IFNjYW4gdXNpbmcgY29tcGFueV90YWdfY29tcGFueV9pZF9pZHggb24gY29tcGFueV90YWcg dGhpc18xXyAgKGNvc3Q9MC4wMC4uMjA3Njc4Ljg1IHJvd3M9NTcwOTI0MyB3aWR0aD0xNikgKGFj dHVhbCB0aW1lPTAuMDI3Li4xLjc1OCByb3dzPTEwMDAgbG9vcHM9MSkKICAgICAgICAgICAgICAg ICAgICAgICAgICBJbmRleCBDb25kOiAoKHRoaXNfMV8uY29tcGFueV9pZCk6OmJpZ2ludCA+IDUw MDAwMDAwKQogICAgICAgICAgICAgICAgICAgICAgICAgIEZpbHRlcjogKCh0aGlzXzFfLnRhZ19p ZCk6OmJpZ2ludCA9IDcpCiAgICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgY3dzX2Nv bXBhbnlfZGVzY3JfdW5pcXVlIG9uIGNvbXBhbnlfZGVzY3IgY2VzM18gIChjb3N0PTAuMDAuLjEx Njk1MTEuOTIgcm93cz01NzU3MDY4IHdpZHRoPTUwMikgKGFjdHVhbCB0aW1lPTAuMDIwLi4xLjc4 OCByb3dzPTEwNTQgbG9vcHM9MSkKICAgICAgICAgICAgICAgICAgICBJbmRleCBDb25kOiAoKGNl czNfLmNvbXBhbnlfaWQpOjpiaWdpbnQgPiA1MDAwMDAwMCkKICAgICAgICAtPiAgSW5kZXggU2Nh biB1c2luZyBwa19jb21wX21zIG9uIGNvbXBhbnlfbWVhc3VyZXMgY29tcGFueW1lYTJfICAoY29z dD0wLjAwLi40NDA5NDUuNzkgcm93cz01Njg4ODg1IHdpZHRoPTY4KSAoYWN0dWFsIHRpbWU9MC4w MTkuLjEuNzI5IHJvd3M9MTA1NCBsb29wcz0xKQogICAgICAgICAgICAgIEluZGV4IENvbmQ6ICgo Y29tcGFueW1lYTJfLmNvbXBhbnlfaWQpOjpiaWdpbnQgPiA1MDAwMDAwMCkKVG90YWwgcnVudGlt ZTogMjYuMzI3IG1zCgo= --001485f1d92abcd86e04845a0b39--