DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:sender;
	b=Ows0ifPqdkIrH6aYhZlD6md/X0GVgseGkt7JiOxJYTp5Dzb41UdKhPELMw1zqIK1+7bI41H3WZixvHqutgiFLdEjmdaR5LxHxQr9Vy5FI+NhPUNJYBemXZXd8xMXqlALOCsyLYUg0ugjKhTzUPKtfJ0D5n14j33mT+QwYft304g=
Message-ID: <48038E21.50606@starsilk.net>
Date: Mon, 14 Apr 2008 11:02:25 -0600
From: Adam Gundy <adam@starsilk.net>
User-Agent: Thunderbird 2.0.0.12 (X11/20080227)
MIME-Version: 1.0
To: Richard Huxton <dev@archonet.com>
CC: pgsql-performance@postgresql.org
Subject: Re: varchar index joins not working?
References: <6f55f1270804092013n73ee1f81t2c6d72cee49655a3@mail.gmail.com>	
	<47FDD3D1.9070208@archonet.com> <47FE29AF.6010906@starsilk.net>
	<6f55f1270804101154t3d4a01eco2b7bff7a0053af38@mail.gmail.com>
	<48030439.9040309@archonet.com>
In-Reply-To: <48030439.9040309@archonet.com>
Content-Type: multipart/signed; protocol="application/x-pkcs7-signature";
	micalg=sha1; boundary="------------ms000602060003080903010002"

This is a cryptographically signed message in MIME format.

--------------ms000602060003080903010002
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Richard Huxton wrote:
> Adam Gundy wrote:
>> On Thu, Apr 10, 2008 at 8:52 AM, Adam Gundy <adam@starsilk.net> wrote:
>>> Richard Huxton wrote:
>>>> How many distinct values do you have in groups.groupid and
>>> group_access.group_id?
>>>  for the small database (since it shows the same problem):
>>>
>>>  group_access: 280/268
>>>  groups: 2006/139
>>>
>>>  for the large database:
>>>
>>>  group_access: same
>>>  groups: 1712647/140
>>>
>>>  the groupid key is an MD5 hash, so it should be uniformly distributed.
>>> maybe that throws the stats? but, again, char works, varchar doesn't :-(
>>
>> OK, I'm thinking the varchar/char part is not the issue.
> 
> Good, because it's not :-)

hmm. unfortunately it did turn out to be (part) of the issue. I've 
discovered that mixing char and varchar in a stored procedure does not 
coerce the types, and ends up doing seq scans all the time.

changing something like this:

proc x ( y char(32) )
{
    select * from groups where groupid = y
}

into this:

proc x ( y varchar(32) )
{
    select * from groups where groupid = y
}

and suddenly postgres does index lookups in the stored proc... way faster.

>> I *think* that when I analyze using char instead of varchar, it is
>> recording a stat for the large group, but for some reason with varchar
>> doesn't add a stat for that one.
>>
>> so, the real question is, how do I fix this? I can turn the stats way
>> up to 1000, but that doesn't guarantee that I'll get a stat for the
>> large group :-(
> 
> Well, by default it will be tracking the 10 most common values (and how 
> often they occur). As you say, this can be increased to 1000 (although 
> it obviously takes longer to check 1000 rather than 10).
> 
> We can have a look at the stats with something like:
> SELECT * FROM pg_stats WHERE tablename='group_access' AND attname='uid';
> You'll be interested in n_distinct, most_common_vals and most_common_freqs.
> 
> However, I think the problem may be that PG doesn't track cross-column 
> stats, so it doesn't know that a particular uid implies one or more 
> particular groupid values.

I doubt we could get stats stable enough for this. the number of groups 
will hopefully be much larger at some point.

it's a shame the index entries can't be used to provide information to 
the planner, eg a rough count of the number of entries for a given key 
(or subset). it would be nice to be able to create eg a counted btree 
when you know you have this kind of data as a hint to the planner.

>> can I turn the statistics off completely for this column? I'm guessing
>> that if I can, that will mean it takes a guess based on the number of
>> distinct values in the groups table, which is still large number of
>> records, possibly enough to trigger the seqscan anyway.
> 
> No - can't disable stats. Besides, you want it the other way around - 
> index scans for all groups except the largest.

actually, disabling seqscan at the server level gives extremely good 
response times. I ended up rewriting a few queries that were scanning 
the whole group for no good reason, and bitmap index hashing seems to 
take care of things nicely.

queries have gone from 30+ seconds to < 0.1 seconds.

>> does postgres have a way of building a 'counted index' that the
>> planner can use for it's record counts? some way of forcibly
>> maintaining a stat for every group?
> 
> No, but let's see what's in pg_stats.

no real help there. either it hits the group being read, and does a good 
plan, or it doesn't, and tries to seqscan (unless I disable it). even 
forcing stats to 1000 only bandaids the situation, given the number of 
groups will eventually exceed that..


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

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIJAzCC
AtwwggJFoAMCAQICEDxC40TcaLz5pGwdgYqUP3wwDQYJKoZIhvcNAQEFBQAwYjELMAkGA1UE
BhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMT
I1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA4MDEyMjIwNTY1OFoX
DTA5MDEyMTIwNTY1OFowQzEfMB0GA1UEAxMWVGhhd3RlIEZyZWVtYWlsIE1lbWJlcjEgMB4G
CSqGSIb3DQEJARYRYWRhbUBzdGFyc2lsay5uZXQwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAw
ggEKAoIBAQDKsMgPTN7C3CAyNUoKepQuQnrXiEOVyA27lGOw+o5rnO1yDzKoR4YgJku89zxq
/Pk4Lc4tr61kJgh7+QopOWtvp/iDGwBEkqiHWjcDgZCwzzG0jkmwWE+6p7MTqSzJ+6EYquOV
BAtvPbnIj+QSrGG7NX/FJZubypVWFStWzvjYxHzC+at5LEeN27+6dOIcNq6z9K1rtEF49ynh
MQYuPY3oqtEpDNyk4GboG1fyI4R0+yqT9dAsIUKCQKNeTZsvyB1FW6QzxnrijE0qS6Q0S5xs
Np3txcCnR2iXvWSJvwYqIS7WIzTAxxIulEFo40JldiSGkR09YzLhkDu4/mMQt/ORAgMBAAGj
LjAsMBwGA1UdEQQVMBOBEWFkYW1Ac3RhcnNpbGsubmV0MAwGA1UdEwEB/wQCMAAwDQYJKoZI
hvcNAQEFBQADgYEAJ058YAh/XRW+46OcHqymS2d+fVhHw1DE4i3j778ameUNhW69gYHXuABU
qR2MPNsmldHciqK2BNkh4rTLXUS/Fkk7tgu7LBsYADVEKbekrNIX5kKaJUIq4M2GIKmkapKG
IfwsTbv4xRDl6uxSo/zD4IqQs/TBFZypGaIVqnSrMdowggLcMIICRaADAgECAhA8QuNE3Gi8
+aRsHYGKlD98MA0GCSqGSIb3DQEBBQUAMGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3
dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJl
ZW1haWwgSXNzdWluZyBDQTAeFw0wODAxMjIyMDU2NThaFw0wOTAxMjEyMDU2NThaMEMxHzAd
BgNVBAMTFlRoYXd0ZSBGcmVlbWFpbCBNZW1iZXIxIDAeBgkqhkiG9w0BCQEWEWFkYW1Ac3Rh
cnNpbGsubmV0MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAyrDID0zewtwgMjVK
CnqULkJ614hDlcgNu5RjsPqOa5ztcg8yqEeGICZLvPc8avz5OC3OLa+tZCYIe/kKKTlrb6f4
gxsARJKoh1o3A4GQsM8xtI5JsFhPuqezE6ksyfuhGKrjlQQLbz25yI/kEqxhuzV/xSWbm8qV
VhUrVs742MR8wvmreSxHjdu/unTiHDaus/Sta7RBePcp4TEGLj2N6KrRKQzcpOBm6BtX8iOE
dPsqk/XQLCFCgkCjXk2bL8gdRVukM8Z64oxNKkukNEucbDad7cXAp0dol71kib8GKiEu1iM0
wMcSLpRBaONCZXYkhpEdPWMy4ZA7uP5jELfzkQIDAQABoy4wLDAcBgNVHREEFTATgRFhZGFt
QHN0YXJzaWxrLm5ldDAMBgNVHRMBAf8EAjAAMA0GCSqGSIb3DQEBBQUAA4GBACdOfGAIf10V
vuOjnB6spktnfn1YR8NQxOIt4++/GpnlDYVuvYGB17gAVKkdjDzbJpXR3IqitgTZIeK0y11E
vxZJO7YLuywbGAA1RCm3pKzSF+ZCmiVCKuDNhiCppGqShiH8LE27+MUQ5ersUqP8w+CKkLP0
wRWcqRmiFap0qzHaMIIDPzCCAqigAwIBAgIBDTANBgkqhkiG9w0BAQUFADCB0TELMAkGA1UE
BhMCWkExFTATBgNVBAgTDFdlc3Rlcm4gQ2FwZTESMBAGA1UEBxMJQ2FwZSBUb3duMRowGAYD
VQQKExFUaGF3dGUgQ29uc3VsdGluZzEoMCYGA1UECxMfQ2VydGlmaWNhdGlvbiBTZXJ2aWNl
cyBEaXZpc2lvbjEkMCIGA1UEAxMbVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIENBMSswKQYJ
KoZIhvcNAQkBFhxwZXJzb25hbC1mcmVlbWFpbEB0aGF3dGUuY29tMB4XDTAzMDcxNzAwMDAw
MFoXDTEzMDcxNjIzNTk1OVowYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25z
dWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJ
c3N1aW5nIENBMIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDEpjxVc1X7TrnKmVoeaMB1
BHCd3+n/ox7svc31W/Iadr1/DDph8r9RzgHU5VAKMNcCY1osiRVwjt3J8CuFWqo/cVbLrzwL
B+fxH5E2JCoTzyvV84J3PQO+K/67GD4Hv0CAAmTXp6a7n2XRxSpUhQ9IBH+nttE8YQRAHmQZ
cmC3+wIDAQABo4GUMIGRMBIGA1UdEwEB/wQIMAYBAf8CAQAwQwYDVR0fBDwwOjA4oDagNIYy
aHR0cDovL2NybC50aGF3dGUuY29tL1RoYXd0ZVBlcnNvbmFsRnJlZW1haWxDQS5jcmwwCwYD
VR0PBAQDAgEGMCkGA1UdEQQiMCCkHjAcMRowGAYDVQQDExFQcml2YXRlTGFiZWwyLTEzODAN
BgkqhkiG9w0BAQUFAAOBgQBIjNFQg+oLLswNo2asZw9/r6y+whehQ5aUnX9MIbj4Nh+qLZ82
L8D0HFAgk3A8/a3hYWLD2ToZfoSxmRsAxRoLgnSeJVCUYsfbJ3FXJY3dqZw5jowgT2Vfldr3
94fWxghOrvbqNOUQGls1TXfjViF4gtwhGTXeJLHTHUb/XV9lTzGCA2QwggNgAgEBMHYwYjEL
MAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAq
BgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhA8QuNE3Gi8+aRs
HYGKlD98MAkGBSsOAwIaBQCgggHDMBgGCSqGSIb3DQEJAzELBgkqhkiG9w0BBwEwHAYJKoZI
hvcNAQkFMQ8XDTA4MDQxNDE3MDIyNVowIwYJKoZIhvcNAQkEMRYEFAFggVdiKyKb23sYXmaD
v04qNuIpMFIGCSqGSIb3DQEJDzFFMEMwCgYIKoZIhvcNAwcwDgYIKoZIhvcNAwICAgCAMA0G
CCqGSIb3DQMCAgFAMAcGBSsOAwIHMA0GCCqGSIb3DQMCAgEoMIGFBgkrBgEEAYI3EAQxeDB2
MGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQu
MSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQQIQPELjRNxo
vPmkbB2BipQ/fDCBhwYLKoZIhvcNAQkQAgsxeKB2MGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQK
ExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29u
YWwgRnJlZW1haWwgSXNzdWluZyBDQQIQPELjRNxovPmkbB2BipQ/fDANBgkqhkiG9w0BAQEF
AASCAQC6T9TOkva1diCyKj1feIctZZNtcgKOaBMJEXWZCX4Lb5SRUwc2Xs3rzLvJFme/niQw
S+h7x087hBg+UmDBzrwNoI0yu+WhmQMEzuz6xYxVzv5YL2Qb3x4jYUhtIwXBTcmzBApwTaF1
H0JCYOqClmV/W3jPJioewx++RzWD9zNQX9kzNJzTwA1ozUIFqH0b0JHewvg0u9hu0NaiMHBd
qGQT+mgSkbzSNBlj8ehKb6bIKO3Y4+K5m+Qdjc9hsvlnGJsZkmkLe4okxBOpbOSwNqbsCtsJ
WYUBchCqacwfcVQP2A5IoKISDNqoBzcUi/+JYQZzeHfoGyVj3F8CQjAH3eCfAAAAAAAA
--------------ms000602060003080903010002--