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=E5X/MaWIWviT2SVHnMCifAvTSzIMMigZ2HTMI+6sI0m5Dtso8hqzHLJNCeZhz+3Mz8n8Uo99gozeJZPRBtKHFGqKOqMsvqEzr0X221ubGNPSy8ljLv/28dDNkKJ9fSY3LQEIPgBJDzihvscz2DXW9NuFD355bKC4wS9gZwvtma8=
Message-ID: <4803C8E9.2060907@starsilk.net>
Date: Mon, 14 Apr 2008 15:13:13 -0600
From: Adam Gundy <adam@starsilk.net>
User-Agent: Thunderbird 2.0.0.12 (X11/20080227)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Richard Huxton <dev@archonet.com>, 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> <48038E21.50606@starsilk.net>
	<11229.1208195166@sss.pgh.pa.us>
In-Reply-To: <11229.1208195166@sss.pgh.pa.us>
Content-Type: multipart/signed; protocol="application/x-pkcs7-signature";
	micalg=sha1; boundary="------------ms050202060609080308030806"

This is a cryptographically signed message in MIME format.

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

Tom Lane wrote:
> Adam Gundy <adam@starsilk.net> writes:
>> 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.
> 
> Oh, it coerces the type all right, just not in the direction you'd like.
> 
> regression=# create table v (f1 varchar(32) primary key);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "v_pkey" for table "v"
> CREATE TABLE
> regression=# explain select * from v where f1 = 'abc'::varchar;
>                            QUERY PLAN                            
> -----------------------------------------------------------------
>  Index Scan using v_pkey on v  (cost=0.00..8.27 rows=1 width=34)
>    Index Cond: ((f1)::text = 'abc'::text)
> (2 rows)
> 
> regression=# explain select * from v where f1 = 'abc'::char(3);
>                     QUERY PLAN                     
> ---------------------------------------------------
>  Seq Scan on v  (cost=0.00..25.88 rows=1 width=34)
>    Filter: ((f1)::bpchar = 'abc'::character(3))
> (2 rows)

yeah. not terribly helpful.. you'd have to assume I'm not the only one 
this has bitten..

is there a reason it doesn't coerce to a type that's useful to the 
planner (ie varchar in my case), or the planner doesn't accept any type 
of string as a valid match for index scan? I would think the benefits of 
being able to index scan always outweigh the cost of type conversion...


hmm. I only saw this with stored procs, but it's obviously generic. I 
think the reason I didn't see it with straight SQL or views is that it 
seems to work correctly with string constants.. coercing them to the 
correct type for the index scan. with a stored proc, all the constants 
are passed in as args, with char() type (until I fixed it, obviously!)


--------------ms050202060609080308030806
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
hvcNAQkFMQ8XDTA4MDQxNDIxMTMxM1owIwYJKoZIhvcNAQkEMRYEFGtQDBVn8SZ4gKuDb2j9
uy+p04fdMFIGCSqGSIb3DQEJDzFFMEMwCgYIKoZIhvcNAwcwDgYIKoZIhvcNAwICAgCAMA0G
CCqGSIb3DQMCAgFAMAcGBSsOAwIHMA0GCCqGSIb3DQMCAgEoMIGFBgkrBgEEAYI3EAQxeDB2
MGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQu
MSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQQIQPELjRNxo
vPmkbB2BipQ/fDCBhwYLKoZIhvcNAQkQAgsxeKB2MGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQK
ExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29u
YWwgRnJlZW1haWwgSXNzdWluZyBDQQIQPELjRNxovPmkbB2BipQ/fDANBgkqhkiG9w0BAQEF
AASCAQBJK123rE0tZBncIiaP8Gz46EO2p/plrVO8r7gu2jSetRnph1Vhwwx/YtF1fHTtXak6
erOHn64SPNNbcSGTkL6zYcL9XSTRwORRvg6jBa6ZDULJCpe8GwxJ+sNNE5OpF8Aw0u4AX7+7
fc2iqOVRVDzmR7tzvB0lsHfBp+yg0h+UaqIPothZjGbzaeu5b+TAk7HH0rEzh/K72hKEuQ92
V0Lz5KlWq9zFoHG3S4KONOfxLmsMBz3C0E/n7Uf+bbM8dj/gPoxxU7g7cpauzj/jc9JCwVYb
yM4t1GbRvyzFShAspRkN9EdDFZ9mdkyHQ1BABu7w6qzeS8Zpp7jJ2heJPzWXAAAAAAAA
--------------ms050202060609080308030806--