X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 280F811F6CE0 for ; Sat, 1 Apr 2006 13:51:22 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 12213-03 for ; Sat, 1 Apr 2006 13:51:20 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.clickspace.com (router2.clickspace.com [65.110.166.227]) by postgresql.org (Postfix) with ESMTP id 9701911F6C6F for ; Sat, 1 Apr 2006 13:51:19 -0400 (AST) Received: from [10.0.1.4] ([68.147.204.179]) (authenticated user brendan@clickspace.com) by mail.clickspace.com (using TLSv1/SSLv3 with cipher RC4-SHA (128 bits)); Sat, 1 Apr 2006 10:51:15 -0700 In-Reply-To: <20060401153247.GI49405@pervasive.com> References: <2F1712F1-E5E2-455E-A291-06F300A66549@clickspace.com> <20060331155911.GP49405@pervasive.com> <3c1395330603311723i368092f2jf0c2291ab033ae2a@mail.gmail.com> <20060401153247.GI49405@pervasive.com> Mime-Version: 1.0 (Apple Message framework v749.3) Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-1-250431641; protocol="application/pkcs7-signature" Message-Id: <95C1A48B-A76F-4F9B-B5EE-0BFAF8873067@clickspace.com> Cc: chris smith , PostgreSQL Performance From: Brendan Duddridge Subject: Re: Query using SeqScan instead of IndexScan Date: Sat, 1 Apr 2006 10:51:12 -0700 To: "Jim C. Nasby" X-Mailer: Apple Mail (2.749.3) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.108 required=5 tests=[AWL=0.108] X-Spam-Score: 0.108 X-Spam-Level: X-Archive-Number: 200604/3 X-Sequence-Number: 18087 --Apple-Mail-1-250431641 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Hi Jim, from SELECT * FROM pg_stats WHERE tablename='table' AND attname='category_id' I find correlation on category_product for category_id is 0.643703 Would setting the index on category_id to be clustered help with this? Thanks, ____________________________________________________________________ Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com On Apr 1, 2006, at 8:32 AM, Jim C. Nasby wrote: > On Sat, Apr 01, 2006 at 11:23:37AM +1000, chris smith wrote: >> On 4/1/06, Brendan Duddridge wrote: >>> Hi Jim, >>> >>> I'm not quite sure what you mean by the correlation of category_id? >> >> It means how many distinct values does it have (at least that's my >> understanding of it ;) ). > > Your understanding is wrong. :) What you're discussing is n_distinct. > > http://www.postgresql.org/docs/8.1/interactive/view-pg-stats.html > > correlation: "Statistical correlation between physical row ordering > and > logical ordering of the column values. This ranges from -1 to +1. When > the value is near -1 or +1, an index scan on the column will be > estimated to be cheaper than when it is near zero, due to reduction of > random access to the disk. (This column is NULL if the column data > type > does not have a < operator.)" > > In other words, the following will have a correlation of 1: > > 1 > 2 > 3 > ... > 998 > 999 > 1000 > > And this is -1... > > 1000 > 999 > ... > 2 > 1 > > While this would have a very low correlation: > > 1 > 1000 > 2 > 999 > ... > > The lower the correlation, the more expensive an index scan is, > because > it's more random. As I mentioned, I believe that the current index > scan > cost estimator is flawed though, because it will bias heavily against > correlations that aren't close to 1 or -1. > > So, what does > > SELECT * FROM pg_stats WHERE tablename='table' AND > attname='category_id'; > > show? > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > --Apple-Mail-1-250431641 Content-Transfer-Encoding: base64 Content-Type: application/pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGIDCCAtkw ggJCoAMCAQICAw84ujANBgkqhkiG9w0BAQQFADBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhh d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVt YWlsIElzc3VpbmcgQ0EwHhcNMDUwNzI5MjAxMTQzWhcNMDYwNzI5MjAxMTQzWjBIMR8wHQYDVQQD ExZUaGF3dGUgRnJlZW1haWwgTWVtYmVyMSUwIwYJKoZIhvcNAQkBFhZicmVuZGFuQGNsaWNrc3Bh Y2UuY29tMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAqWfMCdFgKzXroGNqJuU3eyDC K/Gxts3mtmIdgcHGmUjRKdlBSfkmcCBUDPJIt4QQc91sY3h8Itg0EAsF+1yFECU6afn/1SEAHSDK 6Q86PclF58/Dux2FYNGpeIAw/lisZ2UCyIYoUiWtEDKCBq5jDuYzcGkFS5Csz+/rEL+BHTXQVqnS nUdUaXu9xdZn4wgjB/n65UEYRqr0LGN53CGsFcA9uC+ViS7WyBtzJcP02LMNT+cAZ7TKHw4Q/ZeG 5ptPgQHLTD2wza3GMbPQ4fYK6aNPA+lVzslLSCjyQpg55gVyQQlV0k/5zU5Q5m48ZtqwOqN+fPYd eIB7H5y1SFLd/wIDAQABozMwMTAhBgNVHREEGjAYgRZicmVuZGFuQGNsaWNrc3BhY2UuY29tMAwG A1UdEwEB/wQCMAAwDQYJKoZIhvcNAQEEBQADgYEAT+6hGvStHoRO0OCHlJev31BpUlvPSpbYVKJN i5kCMF164sSX7j0IRvcyU6DfUuW7samTluXJbANyuX1ZIpXulGen5EEc2NV7eU/rMz6ExklMShTh ++azcvmMjOzTuqXi3ZsIjwzBBhqCB0U++kYlMlHEYla2qKLFbXY6PiH0a8swggM/MIICqKADAgEC AgENMA0GCSqGSIb3DQEBBQUAMIHRMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBl MRIwEAYDVQQHEwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYDVQQL Ex9DZXJ0aWZpY2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVyc29u YWwgRnJlZW1haWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRoYXd0ZS5j b20wHhcNMDMwNzE3MDAwMDAwWhcNMTMwNzE2MjM1OTU5WjBiMQswCQYDVQQGEwJaQTElMCMGA1UE ChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFs IEZyZWVtYWlsIElzc3VpbmcgQ0EwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAMSmPFVzVftO ucqZWh5owHUEcJ3f6f+jHuy9zfVb8hp2vX8MOmHyv1HOAdTlUAow1wJjWiyJFXCO3cnwK4Vaqj9x VsuvPAsH5/EfkTYkKhPPK9Xzgnc9A74r/rsYPge/QIACZNenprufZdHFKlSFD0gEf6e20TxhBEAe ZBlyYLf7AgMBAAGjgZQwgZEwEgYDVR0TAQH/BAgwBgEB/wIBADBDBgNVHR8EPDA6MDigNqA0hjJo dHRwOi8vY3JsLnRoYXd0ZS5jb20vVGhhd3RlUGVyc29uYWxGcmVlbWFpbENBLmNybDALBgNVHQ8E BAMCAQYwKQYDVR0RBCIwIKQeMBwxGjAYBgNVBAMTEVByaXZhdGVMYWJlbDItMTM4MA0GCSqGSIb3 DQEBBQUAA4GBAEiM0VCD6gsuzA2jZqxnD3+vrL7CF6FDlpSdf0whuPg2H6otnzYvwPQcUCCTcDz9 reFhYsPZOhl+hLGZGwDFGguCdJ4lUJRix9sncVcljd2pnDmOjCBPZV+V2vf3h9bGCE6u9uo05RAa WzVNd+NWIXiC3CEZNd4ksdMdRv9dX2VPMYIC5zCCAuMCAQEwaTBiMQswCQYDVQQGEwJaQTElMCMG A1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNv bmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECAw84ujAJBgUrDgMCGgUAoIIBUzAYBgkqhkiG9w0BCQMx CwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wNjA0MDExNzUxMTJaMCMGCSqGSIb3DQEJBDEW BBTZ20a8WFs7mfgEB2ASPvvVivN1rjB4BgkrBgEEAYI3EAQxazBpMGIxCzAJBgNVBAYTAlpBMSUw IwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVy c29uYWwgRnJlZW1haWwgSXNzdWluZyBDQQIDDzi6MHoGCyqGSIb3DQEJEAILMWugaTBiMQswCQYD VQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMj VGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECAw84ujANBgkqhkiG9w0BAQEFAASC AQBCn5nLmjB+3q0zHFnVCkgRrM8OoE+ojC2aZKXc7467oviNBwCXiN3lfLRwbRDBu42U2UvZUkJX n2c8s+uB1QkBVYKqdkjRAzmTLrs+l8/lgC6CzBcziYCyJCHLTAF92K1Sc9vw/N2VPYiFfRyC11Z7 nfAZq5qgZXDJREQiaEV/qXG/Ne/VZ+pkNjHeCDrYxQezZBRbOVdGmpLENskNEJ/aARfyArafEmzo A9m8nIpcGRy1AaEuWhjDQtSvXm0gN5GJ8g+SD2OrX7XO59K2WyJ4uR3AOKGjWyFfD29u+wXwJmd4 jlE2wgEu21Y7Kl8A1bpkW/+xpVGqMo9uXYCCTxRmAAAAAAAA --Apple-Mail-1-250431641--