Message-ID: <4EA68447.7090606@comodo.com>
Date: Tue, 25 Oct 2011 15:11:27 +0530
From: Robins Tharakan <robins.tharakan@comodo.com>
Organization: Comodo
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.23) Gecko/20110922 Thunderbird/3.1.15
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Query running a lot faster with enable_nestloop=false
References: 
 <CAK6uQa9wNKmsukj6-StadxJ1HN8sFV6OOZ-q8R5fnmtBDjVhPA@mail.gmail.com>
In-Reply-To: 
 <CAK6uQa9wNKmsukj6-StadxJ1HN8sFV6OOZ-q8R5fnmtBDjVhPA@mail.gmail.com>
Content-Type: multipart/signed; protocol="application/pkcs7-signature";
	micalg=sha1; boundary="------------ms030409020800030105030402"

This is a cryptographically signed message in MIME format.

--------------ms030409020800030105030402
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

Hi Mohanaraj,

One thing you should certainly try is to increase the=20
default_statistics_target value from 50 up to say about 1000 for the=20
larger tables. Large tables tend to go off on estimates with smaller=20
values here.

I guess I am not helping here, but apart from your query, those=20
estimates on Machine B seem odd, coz they shoot up from 10k to the order =

of billions without any big change in row-count. Beats me.

--
Robins Tharakan

> 1. For Machine A, what can I do to make the planner choose the faster
> plan without setting enable_nestloop=3Dfalse ?
>
> 2. From the research I have done it seems to be that the reason the
> planner is choosing the unoptimal query is because of the huge
> difference between the estimated and actual rows. How can I get this
> figure closer ?
>
> 3. If I should rewrite the query, what should I change ?
>
> 4. Why is it that the planner seems to be doing the right thing for
> Machine B without setting enable_nestloop=3Dfalse. What should I be
> comparing in both the machines to understand the difference in choice
> that the planner made ?
>
> I have tried reading through the manual section "55.1. Row Estimation
> Examples", "14.2. Statistics Used by the Planner". I am still trying
> to fully apply the information to my specific case above and hence any
> help or pointers would be greatly appreciated.
>
> In a last ditch effort we also tried upgrading Machine A to
> PostgresSQL 9.1 and that did not rectify the issue. We have reverted
> the upgrade for now.
>
> Thank you for your time.


--------------ms030409020800030105030402
Content-Type: application/pkcs7-signature; name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="smime.p7s"
Content-Description: S/MIME Cryptographic Signature

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIVdjCC
BIowggNyoAMCAQICECf06hH0eobEbp27bqkXBwcwDQYJKoZIhvcNAQEFBQAwbzELMAkGA1UE
BhMCU0UxFDASBgNVBAoTC0FkZFRydXN0IEFCMSYwJAYDVQQLEx1BZGRUcnVzdCBFeHRlcm5h
bCBUVFAgTmV0d29yazEiMCAGA1UEAxMZQWRkVHJ1c3QgRXh0ZXJuYWwgQ0EgUm9vdDAeFw0w
NTA2MDcwODA5MTBaFw0yMDA1MzAxMDQ4MzhaMIGuMQswCQYDVQQGEwJVUzELMAkGA1UECBMC
VVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5l
dHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQGA1UEAxMtVVRO
LVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsMIIBIjANBgkqhkiG
9w0BAQEFAAOCAQ8AMIIBCgKCAQEAsjmFpPJ9q0E7YkY3rs3BYHW8OWX5ShpHornMSMxqmNVN
NRm5pELlzkniii8efNIxB8dOtINknS4p1aJkxIW9hVE1eaROaJB7HHqkkqgX8pgV8pPMyaQy
lbsMTzC9mKALi+VuG6JG+ni8om+rWV6lL8/K2m2qL+usobNqqrcuZzWLeeEeaYji5kbNoKXq
vgvOdjp6Dpvq/NonWz1zHyLmSGHGTPNpsaguG7bUMSAsvIKKjqQOpdeJQ/wWWq8dcdcRWdq6
hw2v+vPhwvCkxWeM1tZUOt4KpLoDd7NlyP0e03RiqhjKaJMeoYV+9Udly/hNVyh00jT/MLbu
9mIwFIws6wIDAQABo4HhMIHeMB8GA1UdIwQYMBaAFK29mHo0tCb3+sQmVO8DveAky1QaMB0G
A1UdDgQWBBSJgmd9xJ0mcABLtFBIfN49rgRufTAOBgNVHQ8BAf8EBAMCAQYwDwYDVR0TAQH/
BAUwAwEB/zB7BgNVHR8EdDByMDigNqA0hjJodHRwOi8vY3JsLmNvbW9kb2NhLmNvbS9BZGRU
cnVzdEV4dGVybmFsQ0FSb290LmNybDA2oDSgMoYwaHR0cDovL2NybC5jb21vZG8ubmV0L0Fk
ZFRydXN0RXh0ZXJuYWxDQVJvb3QuY3JsMA0GCSqGSIb3DQEBBQUAA4IBAQAZ2IkRbyispgCi
54fBm5AD236hEv0e8+LwAamUVEJrmgnEoG3XkJIEA2Z5Q3H8+G+v23ZF4jcaPd3kWQR4rBz0
g0bzes9bhHIt5UbBuhgRKfPLSXmHPLptBZ2kbWhPrXIUNqi5sf2/z3/wpGqUNVCPz4FtVbHd
WTBK322gnGQfSXzvNrv042n0+DmPWq1LhTq3Du3Tzw1EovsEv+QvcI4l+1pUBrPQxLxtjftz
Mizpm4QkLdZ/kXpoAlAfDj9N6cz1u2fo3BwuO/xOzf4CjuOoEwqlJkRl6RDyTVKnrtw+ymsy
XEFs/vVdoOr/0fqbhlhtPZZH5f4ulQTCAMyOofK7MIIFGjCCBAKgAwIBAgIQbRnqpxlPajMi
5iIyeqpx3jANBgkqhkiG9w0BAQUFADCBrjELMAkGA1UEBhMCVVMxCzAJBgNVBAgTAlVUMRcw
FQYDVQQHEw5TYWx0IExha2UgQ2l0eTEeMBwGA1UEChMVVGhlIFVTRVJUUlVTVCBOZXR3b3Jr
MSEwHwYDVQQLExhodHRwOi8vd3d3LnVzZXJ0cnVzdC5jb20xNjA0BgNVBAMTLVVUTi1VU0VS
Rmlyc3QtQ2xpZW50IEF1dGhlbnRpY2F0aW9uIGFuZCBFbWFpbDAeFw0xMTA0MjgwMDAwMDBa
Fw0yMDA1MzAxMDQ4MzhaMIGTMQswCQYDVQQGEwJHQjEbMBkGA1UECBMSR3JlYXRlciBNYW5j
aGVzdGVyMRAwDgYDVQQHEwdTYWxmb3JkMRowGAYDVQQKExFDT01PRE8gQ0EgTGltaXRlZDE5
MDcGA1UEAxMwQ09NT0RPIENsaWVudCBBdXRoZW50aWNhdGlvbiBhbmQgU2VjdXJlIEVtYWls
IENBMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAkoSEW0tXmNReL4uk4UDIo1NY
X2Zl8TJO958yfVXQeExVt0KU4PkncQfFxmmkuTLE8UAakMwnVmJ/F7Vxaa7lIBvky2NeYMqi
QfZq4aP/uN8fSG1lQ4wqLitjOHffsReswtqCAtbUMmrUZ28gE49cNfrlVICv2HEKHTcKAlBT
bJUdqRAUtJmVWRIx/wmi0kzcUtve4kABW0ho3cVKtODtJB86r3FfB+OsvxQ7sCVxaD30D9YX
WEYVgTxoi4uDD216IVfmNLDbMn7jSuGlUnJkJpFOpZIP/+CxYP0ab2hRmWONGoulzEKbm30i
Y9OpoPzOnpDfRBn0XFs1uhbzp5v/wQIDAQABo4IBSzCCAUcwHwYDVR0jBBgwFoAUiYJnfcSd
JnAAS7RQSHzePa4Ebn0wHQYDVR0OBBYEFHoTTgB0W8Z4Y2QnwS/ioFu8ecV7MA4GA1UdDwEB
/wQEAwIBBjASBgNVHRMBAf8ECDAGAQH/AgEAMBEGA1UdIAQKMAgwBgYEVR0gADBYBgNVHR8E
UTBPME2gS6BJhkdodHRwOi8vY3JsLnVzZXJ0cnVzdC5jb20vVVROLVVTRVJGaXJzdC1DbGll
bnRBdXRoZW50aWNhdGlvbmFuZEVtYWlsLmNybDB0BggrBgEFBQcBAQRoMGYwPQYIKwYBBQUH
MAKGMWh0dHA6Ly9jcnQudXNlcnRydXN0LmNvbS9VVE5BZGRUcnVzdENsaWVudF9DQS5jcnQw
JQYIKwYBBQUHMAGGGWh0dHA6Ly9vY3NwLnVzZXJ0cnVzdC5jb20wDQYJKoZIhvcNAQEFBQAD
ggEBAIXWvnhXVW0zf0RS/kLVBqgBA4CK+w2y/Uq/9q9BSfUbWsXSrRtzbj7pJnzmTJjBMCjf
y/tCPKElPgp11tA9OYZm0aGbtU2bb68obB2v5ep0WqjascDxdXovnrqTecr+4pEeVnSy+I3T
4ENyG+2P/WA5IEf7i686ZUg8mD2lJb+972DgSeUWyOs/Q4Pw4O4NwdPNM1+b0L1garM7/vrU
yTo8H+2b/5tJM75CKTmD7jNpLoKdRU2oadqAGx490hpdfEeZpZsIbRKZhtZdVwcbpzC+S0lE
uJB+ytF5OOu0M/qgOl0mWJ5hVRi0IdWZ1eBDQEIwvuql55TSsP7zdfl/bucwggXhMIIEyaAD
AgECAhBfecn+BRfRwsBoBq9kPMYvMA0GCSqGSIb3DQEBBQUAMIGTMQswCQYDVQQGEwJHQjEb
MBkGA1UECBMSR3JlYXRlciBNYW5jaGVzdGVyMRAwDgYDVQQHEwdTYWxmb3JkMRowGAYDVQQK
ExFDT01PRE8gQ0EgTGltaXRlZDE5MDcGA1UEAxMwQ09NT0RPIENsaWVudCBBdXRoZW50aWNh
dGlvbiBhbmQgU2VjdXJlIEVtYWlsIENBMB4XDTExMDgzMDAwMDAwMFoXDTE0MDgyOTIzNTk1
OVowgcAxDjAMBgNVBBETBTA3MzEwMRowGAYDVQQKExFDb21vZG8gR3JvdXAgSW5jLjEdMBsG
A1UECRMUNTI1IFdhc2hpbmd0b24gQmx2ZC4xCzAJBgNVBAgTAk5KMRQwEgYDVQQHEwtKZXJz
ZXkgQ2l0eTELMAkGA1UEBhMCVVMxGDAWBgNVBAMTD1JvYmlucyBUaGFyYWthbjEpMCcGCSqG
SIb3DQEJARYacm9iaW5zLnRoYXJha2FuQGNvbW9kby5jb20wggEiMA0GCSqGSIb3DQEBAQUA
A4IBDwAwggEKAoIBAQCckvPmEZ6upE2ch9jYGwz+P/qRpSqy5EYOQ0W7T2k6SZJ8c6MosxCR
Dhp3wIVNjgKTrOh5fsDZnrFm8ct+QGOXTscKi0KG12HSmZJW8ACxOXCaEGrjKX8xL629Gf9w
JEnoRKrkm4lvZV7j9LTxhSfMLE+CQdakQaNM7+C9xZvazFU9VdmiB2NX1OAR5bBsJcJk4vWv
55buaVqRQqTTE7J7N7ZHPPYeNA2TKD6a6q/2gwpi5OUKXK13x/2T5V4STYcN5rVjO6uJik+y
ZUf5OyAuGIt50Y3IPVLHOpn7muh7/Z9hV+6OyjnJEu00emZjT/f5KbtWUJh5itJgKoln+Csj
AgMBAAGjggIAMIIB/DAfBgNVHSMEGDAWgBR6E04AdFvGeGNkJ8Ev4qBbvHnFezAdBgNVHQ4E
FgQUBvR8jLJLDqVVQXdVWsc/n7XQK9AwDgYDVR0PAQH/BAQDAgWgMAwGA1UdEwEB/wQCMAAw
HQYDVR0lBBYwFAYIKwYBBQUHAwQGCCsGAQUFBwMCMEYGA1UdIAQ/MD0wOwYMKwYBBAGyMQEC
AQMFMCswKQYIKwYBBQUHAgEWHWh0dHBzOi8vc2VjdXJlLmNvbW9kby5uZXQvQ1BTMFcGA1Ud
HwRQME4wTKBKoEiGRmh0dHA6Ly9jcmwuY29tb2RvY2EuY29tL0NPTU9ET0NsaWVudEF1dGhl
bnRpY2F0aW9uYW5kU2VjdXJlRW1haWxDQS5jcmwwgYgGCCsGAQUFBwEBBHwwejBSBggrBgEF
BQcwAoZGaHR0cDovL2NydC5jb21vZG9jYS5jb20vQ09NT0RPQ2xpZW50QXV0aGVudGljYXRp
b25hbmRTZWN1cmVFbWFpbENBLmNydDAkBggrBgEFBQcwAYYYaHR0cDovL29jc3AuY29tb2Rv
Y2EuY29tMFEGA1UdEQRKMEigKgYKKwYBBAGCNxQCA6AcDBpyb2JpbnMudGhhcmFrYW5AY29t
b2RvLmNvbYEacm9iaW5zLnRoYXJha2FuQGNvbW9kby5jb20wDQYJKoZIhvcNAQEFBQADggEB
AHoaVJ/H1rq8vKCcgKtpJjc0V4zJ+MJpQo0piYBxHIn8oHYv2WccGfzeR+2r8HfEbkwuCr21
ILj/XKfcT4JCxbNNTNMVLnyYgC89meCKHdS1Huj8eN39oLulImQ1Lbfbklm8wS2ox1iq7ucO
RIA3Dyz+fX/HNUlLRL+hevMXz4IvmcKCC/15Pd2xBuXfdulhzpZiCiLNDqfFwAYa9gVxruDH
W2IMgU/Qfg7W9revhOTjEiBXDMKq5YXXWzr3Wjyk5J2vmfaX35JCIhL8a/64io6kAmCTh1Tj
fe9Wwo5zs2A8yKOBeZToA07+koyM+NRpREvDULLjamrIGz48ghvgSMswggXhMIIEyaADAgEC
AhBfecn+BRfRwsBoBq9kPMYvMA0GCSqGSIb3DQEBBQUAMIGTMQswCQYDVQQGEwJHQjEbMBkG
A1UECBMSR3JlYXRlciBNYW5jaGVzdGVyMRAwDgYDVQQHEwdTYWxmb3JkMRowGAYDVQQKExFD
T01PRE8gQ0EgTGltaXRlZDE5MDcGA1UEAxMwQ09NT0RPIENsaWVudCBBdXRoZW50aWNhdGlv
biBhbmQgU2VjdXJlIEVtYWlsIENBMB4XDTExMDgzMDAwMDAwMFoXDTE0MDgyOTIzNTk1OVow
gcAxDjAMBgNVBBETBTA3MzEwMRowGAYDVQQKExFDb21vZG8gR3JvdXAgSW5jLjEdMBsGA1UE
CRMUNTI1IFdhc2hpbmd0b24gQmx2ZC4xCzAJBgNVBAgTAk5KMRQwEgYDVQQHEwtKZXJzZXkg
Q2l0eTELMAkGA1UEBhMCVVMxGDAWBgNVBAMTD1JvYmlucyBUaGFyYWthbjEpMCcGCSqGSIb3
DQEJARYacm9iaW5zLnRoYXJha2FuQGNvbW9kby5jb20wggEiMA0GCSqGSIb3DQEBAQUAA4IB
DwAwggEKAoIBAQCckvPmEZ6upE2ch9jYGwz+P/qRpSqy5EYOQ0W7T2k6SZJ8c6MosxCRDhp3
wIVNjgKTrOh5fsDZnrFm8ct+QGOXTscKi0KG12HSmZJW8ACxOXCaEGrjKX8xL629Gf9wJEno
RKrkm4lvZV7j9LTxhSfMLE+CQdakQaNM7+C9xZvazFU9VdmiB2NX1OAR5bBsJcJk4vWv55bu
aVqRQqTTE7J7N7ZHPPYeNA2TKD6a6q/2gwpi5OUKXK13x/2T5V4STYcN5rVjO6uJik+yZUf5
OyAuGIt50Y3IPVLHOpn7muh7/Z9hV+6OyjnJEu00emZjT/f5KbtWUJh5itJgKoln+CsjAgMB
AAGjggIAMIIB/DAfBgNVHSMEGDAWgBR6E04AdFvGeGNkJ8Ev4qBbvHnFezAdBgNVHQ4EFgQU
BvR8jLJLDqVVQXdVWsc/n7XQK9AwDgYDVR0PAQH/BAQDAgWgMAwGA1UdEwEB/wQCMAAwHQYD
VR0lBBYwFAYIKwYBBQUHAwQGCCsGAQUFBwMCMEYGA1UdIAQ/MD0wOwYMKwYBBAGyMQECAQMF
MCswKQYIKwYBBQUHAgEWHWh0dHBzOi8vc2VjdXJlLmNvbW9kby5uZXQvQ1BTMFcGA1UdHwRQ
ME4wTKBKoEiGRmh0dHA6Ly9jcmwuY29tb2RvY2EuY29tL0NPTU9ET0NsaWVudEF1dGhlbnRp
Y2F0aW9uYW5kU2VjdXJlRW1haWxDQS5jcmwwgYgGCCsGAQUFBwEBBHwwejBSBggrBgEFBQcw
AoZGaHR0cDovL2NydC5jb21vZG9jYS5jb20vQ09NT0RPQ2xpZW50QXV0aGVudGljYXRpb25h
bmRTZWN1cmVFbWFpbENBLmNydDAkBggrBgEFBQcwAYYYaHR0cDovL29jc3AuY29tb2RvY2Eu
Y29tMFEGA1UdEQRKMEigKgYKKwYBBAGCNxQCA6AcDBpyb2JpbnMudGhhcmFrYW5AY29tb2Rv
LmNvbYEacm9iaW5zLnRoYXJha2FuQGNvbW9kby5jb20wDQYJKoZIhvcNAQEFBQADggEBAHoa
VJ/H1rq8vKCcgKtpJjc0V4zJ+MJpQo0piYBxHIn8oHYv2WccGfzeR+2r8HfEbkwuCr21ILj/
XKfcT4JCxbNNTNMVLnyYgC89meCKHdS1Huj8eN39oLulImQ1Lbfbklm8wS2ox1iq7ucORIA3
Dyz+fX/HNUlLRL+hevMXz4IvmcKCC/15Pd2xBuXfdulhzpZiCiLNDqfFwAYa9gVxruDHW2IM
gU/Qfg7W9revhOTjEiBXDMKq5YXXWzr3Wjyk5J2vmfaX35JCIhL8a/64io6kAmCTh1Tjfe9W
wo5zs2A8yKOBeZToA07+koyM+NRpREvDULLjamrIGz48ghvgSMsxggQMMIIECAIBATCBqDCB
kzELMAkGA1UEBhMCR0IxGzAZBgNVBAgTEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UEBxMH
U2FsZm9yZDEaMBgGA1UEChMRQ09NT0RPIENBIExpbWl0ZWQxOTA3BgNVBAMTMENPTU9ETyBD
bGllbnQgQXV0aGVudGljYXRpb24gYW5kIFNlY3VyZSBFbWFpbCBDQQIQX3nJ/gUX0cLAaAav
ZDzGLzAJBgUrDgMCGgUAoIICODAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3
DQEJBTEPFw0xMTEwMjUwOTQxMjdaMCMGCSqGSIb3DQEJBDEWBBS+5+xZyLmtG/vmSms0SEZ1
4k9FfDBfBgkqhkiG9w0BCQ8xUjBQMAsGCWCGSAFlAwQBAjAKBggqhkiG9w0DBzAOBggqhkiG
9w0DAgICAIAwDQYIKoZIhvcNAwICAUAwBwYFKw4DAgcwDQYIKoZIhvcNAwICASgwgbkGCSsG
AQQBgjcQBDGBqzCBqDCBkzELMAkGA1UEBhMCR0IxGzAZBgNVBAgTEkdyZWF0ZXIgTWFuY2hl
c3RlcjEQMA4GA1UEBxMHU2FsZm9yZDEaMBgGA1UEChMRQ09NT0RPIENBIExpbWl0ZWQxOTA3
BgNVBAMTMENPTU9ETyBDbGllbnQgQXV0aGVudGljYXRpb24gYW5kIFNlY3VyZSBFbWFpbCBD
QQIQX3nJ/gUX0cLAaAavZDzGLzCBuwYLKoZIhvcNAQkQAgsxgauggagwgZMxCzAJBgNVBAYT
AkdCMRswGQYDVQQIExJHcmVhdGVyIE1hbmNoZXN0ZXIxEDAOBgNVBAcTB1NhbGZvcmQxGjAY
BgNVBAoTEUNPTU9ETyBDQSBMaW1pdGVkMTkwNwYDVQQDEzBDT01PRE8gQ2xpZW50IEF1dGhl
bnRpY2F0aW9uIGFuZCBTZWN1cmUgRW1haWwgQ0ECEF95yf4FF9HCwGgGr2Q8xi8wDQYJKoZI
hvcNAQEBBQAEggEAZ5w46O2IWXV/0SMY6UMTcHvnlbMN9AlIlRVHvNvI19Zk13P6Xd5EEwfJ
RW5k1sztWUlnA7xVFgaxD6e2Ww+QTpdoU0xrI1iIlZid9Ohf9qsyM1JOexaKxSs+cEVPakc0
7ZQOQHCRzVcgzd+p0WZJ2OkwIeSbAnW6QtSirgluBkBFnhJFWXthIvjgl47iurlwjvlYgiWr
4IyrocMz9u9fy7HjG8aVV5OClUA4OaGrtu01ndwP9ypk/EsqyaL98YqAyN/OHCaZiJLMeRes
q28PilBpNSnHoK+2ywQ+dlY69/3bz9Ck9De5mhUhKiPdoZ6a5AdA+gW3kyP4HXkz95eoYAAA
AAAAAA==
--------------ms030409020800030105030402--