Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 610402E0071 for ; Mon, 14 Apr 2008 19:16:02 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 74940-10 for ; Mon, 14 Apr 2008 19:15:59 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.181]) by developer.postgresql.org (Postfix) with ESMTP id 790182E007C for ; Mon, 14 Apr 2008 19:15:59 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id d37so2550237pye.29 for ; Mon, 14 Apr 2008 15:15:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:sender; bh=MOs7TCY6QsjeANv6aCA5SGuoQb+ZBgWEHdCXByqe37k=; b=OUKVOD4QvP7U6ITuoZ/6G0ry8qbRSWVDB5y7gptlQ1lXH1pSYdMRhC2Iyfg2aXwhJ/ul8+8rfYTYhL9eBJra7dKiVPIN5HutUpmTLLrNQvHFD4q39twMNeiwGsMMnigz5ieRp7CzHCQU5qtu7brLjB9OAk3tGaMpYXoX+txsob8= 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=tMBrVeoGGdX18hqzH52ZnhmgB/YekaJ3Kh7qwnV9WGsyXmA3ye7UIATIoKnlgEewtuF1Vi7hh+HAF9O+3rlxkg0MFQEafhvVTH18dUPNKhFdWxStl8QkOBJVXVH64HcV/nrAtfEu/fsDUkOJHmOaidgCzfl8vAsXcaIegfILkTs= Received: by 10.35.105.18 with SMTP id h18mr11479739pym.49.1208211357988; Mon, 14 Apr 2008 15:15:57 -0700 (PDT) Received: from cheetah.dev.us.cyberscience.com ( [66.250.49.67]) by mx.google.com with ESMTPS id w29sm8767372pyg.5.2008.04.14.15.15.56 (version=SSLv3 cipher=RC4-MD5); Mon, 14 Apr 2008 15:15:57 -0700 (PDT) Message-ID: <4803D7C9.1070803@starsilk.net> Date: Mon, 14 Apr 2008 16:16:41 -0600 From: Adam Gundy User-Agent: Thunderbird 2.0.0.12 (X11/20080227) MIME-Version: 1.0 To: Tom Lane CC: Richard Huxton , 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> <4803C8E9.2060907@starsilk.net> <23669.1208210476@sss.pgh.pa.us> In-Reply-To: <23669.1208210476@sss.pgh.pa.us> Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms090108000200080902050608" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200804/187 X-Sequence-Number: 29680 This is a cryptographically signed message in MIME format. --------------ms090108000200080902050608 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: > Adam Gundy writes: >> Tom Lane wrote: >>> Oh, it coerces the type all right, just not in the direction you'd like. > >> is there a reason it doesn't coerce to a type that's useful to the >> planner (ie varchar in my case), > > In this case I think the choice is probably semantically correct: > shouldn't a comparison of varchar (trailing space sensitive) and > char (trailing space INsensitive) follow trailing-space-insensitive > semantics? > > I wouldn't swear that the behavior is intentional ;-) as to going > that way rather than the other, but I'm disinclined to change it. ahh. I forgot about the trailing spaces. but you can always coerce a char to a varchar safely, which would have fixed my issue. you can't coerce the other way, as you say, because you'll lose the trailing spaces... alternatively, can the planner give warnings somehow? or suggestions? eg some messages from 'explain analyze' like: 'I could make your query go much faster IF ...' or 'suggestion: create an index on ...' 'suggestion: convert this index to ...' or 'warning: I'd really like to use this index, BUT ...' >> or the planner doesn't accept any type >> of string as a valid match for index scan? > > Can't. This equality operator doesn't have the same notion of equality > that that index does. > > The long and the short of it is that mixing char and varchar is > hazardous. no kidding. --------------ms090108000200080902050608 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 hvcNAQkFMQ8XDTA4MDQxNDIyMTY0MVowIwYJKoZIhvcNAQkEMRYEFDuOkd0t89pveQ6Lv9FZ hXq1RCGdMFIGCSqGSIb3DQEJDzFFMEMwCgYIKoZIhvcNAwcwDgYIKoZIhvcNAwICAgCAMA0G CCqGSIb3DQMCAgFAMAcGBSsOAwIHMA0GCCqGSIb3DQMCAgEoMIGFBgkrBgEEAYI3EAQxeDB2 MGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQu MSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQQIQPELjRNxo vPmkbB2BipQ/fDCBhwYLKoZIhvcNAQkQAgsxeKB2MGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQK ExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29u YWwgRnJlZW1haWwgSXNzdWluZyBDQQIQPELjRNxovPmkbB2BipQ/fDANBgkqhkiG9w0BAQEF AASCAQDJoc/opqPhrvS8Z60ac0avL52rim/eL8oDHHFiRHHCj93LyaH14Imd0jBl3XhQU4MG x12QFwk2M0QGLdX0nLYU2Ye24X4PZb5tj+pfAcIP6Sz05fTThFsB6isMdyvRUDvS08MjcOD0 SSihVhDX7LnG9nJE78tGNwKLDS2hHwLZSG16OETtBIi0o9uo0ymbXxrApM2LzW0tMCjzthNm VlRTxJg+GKrVfrburkXNPDRKpnL5LSZkTWrD9FSF3h+UjWOKpEj3r3lY8IJYhTyrR7JmlQHA vfv0hFDeIKI8L2a0o1c9CUfJHiT4cFDAg2McmYOIDSr27VcOY6CU/l0iN5BZAAAAAAAA --------------ms090108000200080902050608--