DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=YRjw+T0ueSQRa+06tsk1mOeova2v4zeNNmlVri9IAoYidLOwuJp2zy52bCbddd5YCK
	UcpCSaj5JGylUdY4pv6+rDS2+c1O32ADrHLDLWX4ByAC7+A0bB4QWTUhpkfmmnskujyo
	wUd6sEPydCqtU65TomzE97npgf5g7XXTV1WDI=
MIME-Version: 1.0
Date: Fri, 16 Apr 2010 11:02:06 +0300
Message-ID: <m2y331e40661004160102tff445f9eg165a04efe3b409ed@mail.gmail.com>
Subject: Planner not using column limit specified for one column for another
	column equal to first
From: =?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= <tivv00@gmail.com>
To: pgsql-performance@postgresql.org
Content-Type: multipart/mixed; boundary=0016365eec82009cfe048456070a

--0016365eec82009cfe048456070a
Content-Type: multipart/alternative; boundary=0016365eec82009ced0484560708

--0016365eec82009ced0484560708
Content-Type: text/plain; charset=ISO-8859-1

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;

(plan2.txt)
Total runtime: 27.547 ms

I've thought and someone in this list've told me that this should be done
automatically. But I have pretty recent server:
PostgreSQL 8.4.2 on amd64-portbld-freebsd8.0, compiled by GCC cc (GCC) 4.2.1
20070719  [FreeBSD], 64-bit
and it still do not work

Do I misunderstand something or this feature don't work in such a query?

Best regards, Vitalii Tymchyshyn

--0016365eec82009ced0484560708
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hello.<div><br></div><div>I have a query that performs very poor because th=
ere is a limit on join column that is not applied to other columns:</div><d=
iv><div><br></div><div>select * from company this_ left outer join company_=
tag this_1_ on this_.id=3Dthis_1_.company_id left outer join company_measur=
es companymea2_ on this_.id=3Dcompanymea2_.company_id left outer join compa=
ny_descr ces3_ on this_.id=3Dces3_.company_id where this_1_.tag_id =3D 7 an=
d this_.id&gt;50000000=A0</div>
<div>and this_1_.company_id&gt;50000000</div><div>order by this_.id asc lim=
it 1000;</div></div><div><br></div><div><div>(plan1.txt)</div><div>Total ru=
ntime: 7794.692 ms</div></div><div><br></div><div>At the same time if I app=
ly the limit (&gt;50000000) to other columns in query itself it works like =
a charm:</div>
<div><br></div><div><div>select * from company this_ left outer join compan=
y_tag this_1_ on this_.id=3Dthis_1_.company_id left outer join company_meas=
ures companymea2_ on this_.id=3Dcompanymea2_.company_id left outer join com=
pany_descr ces3_ on this_.id=3Dces3_.company_id where this_1_.tag_id =3D 7 =
and this_.id&gt;50000000=A0</div>
<div>and this_1_.company_id&gt;50000000</div><div>and companymea2_.company_=
id&gt;50000000 and ces3_.company_id&gt;50000000</div><div>order by this_.id=
 asc limit 1000;</div></div><div><br></div><div>(plan2.txt)</div><div>Total=
 runtime: 27.547 ms</div>
<div><br></div><div>I&#39;ve thought and someone in this list&#39;ve told m=
e that this should be done automatically. But I have pretty recent server:<=
/div><div>PostgreSQL 8.4.2 on amd64-portbld-freebsd8.0, compiled by GCC cc =
(GCC) 4.2.1 20070719 =A0[FreeBSD], 64-bit</div>
<div>and it still do not work</div><div><br></div><div>Do I misunderstand s=
omething or this feature don&#39;t work in such a query?</div><div><br></di=
v><div>Best regards, Vitalii Tymchyshyn</div>

--0016365eec82009ced0484560708--

--0016365eec82009cfe048456070a
Content-Type: text/plain; charset=US-ASCII; name="plan1.txt"
Content-Disposition: attachment; filename="plan1.txt"
Content-Transfer-Encoding: base64
X-Attachment-Id: f_g82plxhc0

TGltaXQgIChjb3N0PTQ5Ny40Ni4uMTQ2NC41MCByb3dzPTEwMDAgd2lkdGg9NjkzKSAoYWN0dWFs
IHRpbWU9Nzc2Ny43MjEuLjc3OTMuMDQ3IHJvd3M9MTAwMCBsb29wcz0xKQogIC0+ICBNZXJnZSBM
ZWZ0IEpvaW4gIChjb3N0PTQ5Ny40Ni4uNTUyMTYxMi42NCByb3dzPTU3MDkyNDMgd2lkdGg9Njkz
KSAoYWN0dWFsIHRpbWU9Nzc2Ny43MTcuLjc3OTAuMjc0IHJvd3M9MTAwMCBsb29wcz0xKQogICAg
ICAgIE1lcmdlIENvbmQ6ICh0aGlzXy5pZCA9IChjb21wYW55bWVhMl8uY29tcGFueV9pZCk6OmJp
Z2ludCkKICAgICAgICAtPiAgTWVyZ2UgTGVmdCBKb2luICAoY29zdD00MDQuMzEuLjQ1NDQ1MDgu
NTQgcm93cz01NzA5MjQzIHdpZHRoPTYyNSkgKGFjdHVhbCB0aW1lPTQyMTEuNTAxLi40MjI3LjIx
NSByb3dzPTEwMDAgbG9vcHM9MSkKICAgICAgICAgICAgICBNZXJnZSBDb25kOiAodGhpc18uaWQg
PSAoY2VzM18uY29tcGFueV9pZCk6OmJpZ2ludCkKICAgICAgICAgICAgICAtPiAgTWVyZ2UgSm9p
biAgKGNvc3Q9MzcuODcuLjI0MzU1MzYuMDAgcm93cz01NzA5MjQzIHdpZHRoPTEyMykgKGFjdHVh
bCB0aW1lPTAuMDY5Li44LjU4NCByb3dzPTEwMDAgbG9vcHM9MSkKICAgICAgICAgICAgICAgICAg
ICBNZXJnZSBDb25kOiAodGhpc18uaWQgPSAodGhpc18xXy5jb21wYW55X2lkKTo6YmlnaW50KQog
ICAgICAgICAgICAgICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIHBrX2NvbXBfbSBvbiBjb21w
YW55IHRoaXNfICAoY29zdD0wLjAwLi4yMTM5MzI0LjUzIHJvd3M9Njg4NjU5OCB3aWR0aD0xMDcp
IChhY3R1YWwgdGltZT0wLjAzMS4uMS44NzYgcm93cz0xMDU0IGxvb3BzPTEpCiAgICAgICAgICAg
ICAgICAgICAgICAgICAgSW5kZXggQ29uZDogKGlkID4gNTAwMDAwMDApCiAgICAgICAgICAgICAg
ICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgY29tcGFueV90YWdfY29tcGFueV9pZF9pZHggb24g
Y29tcGFueV90YWcgdGhpc18xXyAgKGNvc3Q9MC4wMC4uMjA3Njc4Ljg1IHJvd3M9NTcwOTI0MyB3
aWR0aD0xNikgKGFjdHVhbCB0aW1lPTAuMDI3Li4xLjc1NyByb3dzPTEwMDAgbG9vcHM9MSkKICAg
ICAgICAgICAgICAgICAgICAgICAgICBJbmRleCBDb25kOiAoKHRoaXNfMV8uY29tcGFueV9pZCk6
OmJpZ2ludCA+IDUwMDAwMDAwKQogICAgICAgICAgICAgICAgICAgICAgICAgIEZpbHRlcjogKCh0
aGlzXzFfLnRhZ19pZCk6OmJpZ2ludCA9IDcpCiAgICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4g
dXNpbmcgY29tcGFueV9kZXNjcnMgb24gY29tcGFueV9kZXNjciBjZXMzXyAgKGNvc3Q9MC4wMC4u
MjA3MzUyNi44OSByb3dzPTE5OTY2MTIgd2lkdGg9NTAyKSAoYWN0dWFsIHRpbWU9MC4wMTQuLjI1
NzYuMDEzIHJvd3M9MTA5NzA5NiBsb29wcz0xKQogICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5n
IHBrX2NvbXBfbXMgb24gY29tcGFueV9tZWFzdXJlcyBjb21wYW55bWVhMl8gIChjb3N0PTAuMDAu
Ljg4OTQyNy44MSByb3dzPTY4MjE2NzIgd2lkdGg9NjgpIChhY3R1YWwgdGltZT0wLjAyMC4uMTk0
Ni4yNTUgcm93cz0xMDk3MDk2IGxvb3BzPTEpClRvdGFsIHJ1bnRpbWU6IDc3OTQuNjkyIG1zCgo=
--0016365eec82009cfe048456070a
Content-Type: text/plain; charset=US-ASCII; name="plan2.txt"
Content-Disposition: attachment; filename="plan2.txt"
Content-Transfer-Encoding: base64
X-Attachment-Id: f_g82pm2mp1

TGltaXQgIChjb3N0PTMzMC41Ny4uNDg4OC45NyByb3dzPTEwMDAgd2lkdGg9NjkzKSAoYWN0dWFs
IHRpbWU9MC4xMjUuLjI2LjAxMSByb3dzPTEwMDAgbG9vcHM9MSkKICAtPiAgTWVyZ2UgSm9pbiAg
KGNvc3Q9MzMwLjU3Li41MjYwNjUxLjI5IHJvd3M9MTE1Mzk4NiB3aWR0aD02OTMpIChhY3R1YWwg
dGltZT0wLjEyMi4uMjMuMjE1IHJvd3M9MTAwMCBsb29wcz0xKQogICAgICAgIE1lcmdlIENvbmQ6
ICh0aGlzXy5pZCA9ICh0aGlzXzFfLmNvbXBhbnlfaWQpOjpiaWdpbnQpCiAgICAgICAgLT4gIE1l
cmdlIEpvaW4gIChjb3N0PTI5Mi43MS4uNTAyMzcyOC45OSByb3dzPTEzOTE5NjAgd2lkdGg9Njc3
KSAoYWN0dWFsIHRpbWU9MC4wOTAuLjE2LjYxNSByb3dzPTEwNTQgbG9vcHM9MSkKICAgICAgICAg
ICAgICBNZXJnZSBDb25kOiAoKGNvbXBhbnltZWEyXy5jb21wYW55X2lkKTo6YmlnaW50ID0gdGhp
c18uaWQpCiAgICAgICAgICAgICAgLT4gIE1lcmdlIEpvaW4gIChjb3N0PTAuMDAuLjI4NDY3Njku
ODkgcm93cz0xNjg1MDE3IHdpZHRoPTU3MCkgKGFjdHVhbCB0aW1lPTAuMDYzLi45LjUzNCByb3dz
PTEwNTQgbG9vcHM9MSkKICAgICAgICAgICAgICAgICAgICBNZXJnZSBDb25kOiAoKGNvbXBhbnlt
ZWEyXy5jb21wYW55X2lkKTo6YmlnaW50ID0gKGNlczNfLmNvbXBhbnlfaWQpOjpiaWdpbnQpCiAg
ICAgICAgICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgcGtfY29tcF9tcyBvbiBjb21w
YW55X21lYXN1cmVzIGNvbXBhbnltZWEyXyAgKGNvc3Q9MC4wMC4uODQ4MzEyLjAwIHJvd3M9NTY4
ODg4NSB3aWR0aD02OCkgKGFjdHVhbCB0aW1lPTAuMDMzLi4xLjk3MyByb3dzPTEwNTQgbG9vcHM9
MSkKICAgICAgICAgICAgICAgICAgICAgICAgICBJbmRleCBDb25kOiAoKGNvbXBhbnlfaWQpOjpi
aWdpbnQgPiA1MDAwMDAwMCkKICAgICAgICAgICAgICAgICAgICAtPiAgSW5kZXggU2NhbiB1c2lu
ZyBjb21wYW55X2Rlc2NycyBvbiBjb21wYW55X2Rlc2NyIGNlczNfICAoY29zdD0wLjAwLi4xOTYz
MTcyLjk2IHJvd3M9MTY4NTAxNyB3aWR0aD01MDIpIChhY3R1YWwgdGltZT0wLjAyMC4uMi4xNTIg
cm93cz0xMDU0IGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAgICAgICAgSW5kZXggQ29uZDog
KChjZXMzXy5jb21wYW55X2lkKTo6YmlnaW50ID4gNTAwMDAwMDApCiAgICAgICAgICAgICAgLT4g
IEluZGV4IFNjYW4gdXNpbmcgcGtfY29tcF9tIG9uIGNvbXBhbnkgdGhpc18gIChjb3N0PTAuMDAu
LjIxMzkzMjQuNTMgcm93cz02ODg2NTk4IHdpZHRoPTEwNykgKGFjdHVhbCB0aW1lPTAuMDIyLi4x
Ljg1NSByb3dzPTEwNTQgbG9vcHM9MSkKICAgICAgICAgICAgICAgICAgICBJbmRleCBDb25kOiAo
dGhpc18uaWQgPiA1MDAwMDAwMCkKICAgICAgICAtPiAgSW5kZXggU2NhbiB1c2luZyBjb21wYW55
X3RhZ19jb21wYW55X2lkX2lkeCBvbiBjb21wYW55X3RhZyB0aGlzXzFfICAoY29zdD0wLjAwLi4y
MDc2NzguODUgcm93cz01NzA5MjQzIHdpZHRoPTE2KSAoYWN0dWFsIHRpbWU9MC4wMjUuLjEuNzQy
IHJvd3M9MTAwMCBsb29wcz0xKQogICAgICAgICAgICAgIEluZGV4IENvbmQ6ICgodGhpc18xXy5j
b21wYW55X2lkKTo6YmlnaW50ID4gNTAwMDAwMDApCiAgICAgICAgICAgICAgRmlsdGVyOiAoKHRo
aXNfMV8udGFnX2lkKTo6YmlnaW50ID0gNykKVG90YWwgcnVudGltZTogMjcuNTQ3IG1zCgo=
--0016365eec82009cfe048456070a--