Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 1D22664FD1A for ; Wed, 20 Aug 2008 14:35:05 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 54036-06 for ; Wed, 20 Aug 2008 14:34:56 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 74A5D650070 for ; Wed, 20 Aug 2008 14:34:57 -0300 (ADT) Received: from [68.241.108.232] (68-241-108-232.area5.spcsdns.net [68.241.108.232]) (using TLSv1 with cipher AES128-SHA (128/128 bits)) (No client certificate requested) by noel.decibel.org (Postfix) with ESMTP id 7D13A565DE; Wed, 20 Aug 2008 12:34:48 -0500 (CDT) In-Reply-To: <48A841E4.3000701@alteeve.com> References: <48A5CCA4.2040206@alteeve.com> <48A841E4.3000701@alteeve.com> Mime-Version: 1.0 (Apple Message framework v753.1) Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-4-425722734; protocol="application/pkcs7-signature" Message-Id: <0763302B-A289-409A-8EDF-6D86FCEB2E82@decibel.org> Cc: pgsql-performance@postgresql.org From: Decibel! Subject: Re: Optimizing a VIEW Date: Wed, 20 Aug 2008 11:51:31 -0500 To: Madison Kelly X-Mailer: Apple Mail (2.753.1) 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: 200808/228 X-Sequence-Number: 31109 --Apple-Mail-4-425722734 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed On Aug 17, 2008, at 10:21 AM, Madison Kelly wrote: > Truth be told, I sort of expected this would be what I had to do. I > think I asked this more in hoping that there might be some "magic" > I didn't know about, but I see now that's not the case. :) > > As my data points grow to 500,000+, the time it took to return > these results grew to well over 10 minutes on a decent server and > the DB size was growing rapidly, as you spoke of. > > So I did just as you suggested and took the variable names I knew > about specifically and created a table for them. These are the ones > that are being most often updated (hourly per customer) and made > each column an 'int' or 'real' where possible and ditched the > tracking of the adding/modifying user and time stamp. I added those > out of habit, more than anything. This data will always come from a > system app though, so... > > Given that my DB is in development and how very long and intensive > it would have been to pull out the existing data, I have started > over and am now gathering new data. In a week or so I should have > the same amount of data as I had before and I will be able to do a > closer comparison test. > > However, I already suspect the growth of the database will be > substantially slower and the queries will return substantially faster. I strongly recommend you also re-think using EAV at all for this. It plain and simple does not scale well. I won't go so far as to say it can never be used (we're actually working on one right now, but it will only be used to occasionally pull up single entities), but you have to be really careful with it. I don't see it working very well for what it sounds like you're trying to do. -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 --Apple-Mail-4-425722734 Content-Transfer-Encoding: base64 Content-Type: application/pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGtTCCAz8w ggKooAMCAQICAQ0wDQYJKoZIhvcNAQEFBQAwgdExCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0 ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEaMBgGA1UEChMRVGhhd3RlIENvbnN1bHRpbmcx KDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2VydmljZXMgRGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0 ZSBQZXJzb25hbCBGcmVlbWFpbCBDQTErMCkGCSqGSIb3DQEJARYccGVyc29uYWwtZnJlZW1haWxA dGhhd3RlLmNvbTAeFw0wMzA3MTcwMDAwMDBaFw0xMzA3MTYyMzU5NTlaMGIxCzAJBgNVBAYTAlpB MSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUg UGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEA xKY8VXNV+065yplaHmjAdQRwnd/p/6Me7L3N9VvyGna9fww6YfK/Uc4B1OVQCjDXAmNaLIkVcI7d yfArhVqqP3FWy688Cwfn8R+RNiQqE88r1fOCdz0Dviv+uxg+B79AgAJk16emu59l0cUqVIUPSAR/ p7bRPGEEQB5kGXJgt/sCAwEAAaOBlDCBkTASBgNVHRMBAf8ECDAGAQH/AgEAMEMGA1UdHwQ8MDow OKA2oDSGMmh0dHA6Ly9jcmwudGhhd3RlLmNvbS9UaGF3dGVQZXJzb25hbEZyZWVtYWlsQ0EuY3Js MAsGA1UdDwQEAwIBBjApBgNVHREEIjAgpB4wHDEaMBgGA1UEAxMRUHJpdmF0ZUxhYmVsMi0xMzgw DQYJKoZIhvcNAQEFBQADgYEASIzRUIPqCy7MDaNmrGcPf6+svsIXoUOWlJ1/TCG4+DYfqi2fNi/A 9BxQIJNwPP2t4WFiw9k6GX6EsZkbAMUaC4J0niVQlGLH2ydxVyWN3amcOY6MIE9lX5Xa9/eH1sYI Tq726jTlEBpbNU1341YheILcIRk13iSx0x1G/11fZU8wggNuMIIC16ADAgECAhACpH6ZVSGg5xZh 5O/wRN/4MA0GCSqGSIb3DQEBBQUAMGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUgQ29u c3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgSXNz dWluZyBDQTAeFw0wNzEwMjkxOTE0NDdaFw0wODEwMjgxOTE0NDdaMIGqMQ4wDAYDVQQEEwVOYXNi eTETMBEGA1UEKhMKSmFtZXMgQ2FybDEZMBcGA1UEAxMQSmFtZXMgQ2FybCBOYXNieTEcMBoGCSqG SIb3DQEJARYNamltQG5hc2J5Lm5ldDEiMCAGCSqGSIb3DQEJARYTZGVjaWJlbEBkZWNpYmVsLm9y ZzEmMCQGCSqGSIb3DQEJARYXZGVjaWJlbEBkaXN0cmlidXRlZC5uZXQwggEiMA0GCSqGSIb3DQEB AQUAA4IBDwAwggEKAoIBAQDSgNcbvYYycE3FBX+z9i9zXtQxBalsoeDP92/jSG30Pq6HMlir3+lu rgZd5wBHRt/DMj+5ZfUDY9zQ7hfUio/WKQxRkXH46deV5xh9KNFf8kgxLiLKOBpesWFaXvNlnxU9 JKEpNZUnWeE8N/2mMWWs9L0Rfq0AxYa8TPfsbklALy1n9fY2yU1dNoT/s5tjRQK0UvzElimzMlOK 5PQuZ4atL9Jl6WCZb8811Qr+VUCFWz3e7HCgYDygwL+lBHlXEOLXPJ/lB8l61ugeooGjvuVdsaNf pM30pi5mwRECfOxo9zUL6uCHT6f4Dir6DsOIsTLb4N/vQjz+SGrLwtavSwRTAgMBAAGjWDBWMEYG A1UdEQQ/MD2BDWppbUBuYXNieS5uZXSBE2RlY2liZWxAZGVjaWJlbC5vcmeBF2RlY2liZWxAZGlz dHJpYnV0ZWQubmV0MAwGA1UdEwEB/wQCMAAwDQYJKoZIhvcNAQEFBQADgYEApk/O70t8/38Jkp0M 9ywgSU2kn7w+Z3JN39pH3PnrLjmYV7D//2zJavR3zrZ4AeH2TDc/ioQTk1unfOYroYl+jUKD8NvU 8hbelR8pr+4hKMDx3+6plhKL0ZI9Zbw9DaJcZ3o8QK5fNGQooiz5+aCntbxdD81qG7+lkjQUUcvC wI0xggMQMIIDDAIBATB2MGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGlu ZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBD QQIQAqR+mVUhoOcWYeTv8ETf+DAJBgUrDgMCGgUAoIIBbzAYBgkqhkiG9w0BCQMxCwYJKoZIhvcN AQcBMBwGCSqGSIb3DQEJBTEPFw0wODA4MjAxNjUxMzFaMCMGCSqGSIb3DQEJBDEWBBTuY2teeJm2 K3umDvElqPkHCNG37DCBhQYJKwYBBAGCNxAEMXgwdjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMc VGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZy ZWVtYWlsIElzc3VpbmcgQ0ECEAKkfplVIaDnFmHk7/BE3/gwgYcGCyqGSIb3DQEJEAILMXigdjBi MQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoG A1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECEAKkfplVIaDnFmHk7/BE 3/gwDQYJKoZIhvcNAQEBBQAEggEADeVdh1GzZBxWRVUoUbQ9/+l+bQpO5CE3WQbnSlJEOA/0JEYI wgNK7r/CO1SxyvNXkZJTyu3NF1r4jVzsOfI7D6LS+1H/5fR0I8HwPE30HbyiZ1RnBtc2d7tDcRK2 CBL0WfgOEChqK0J9NBKOWnlj28LI1mb6a/PY66oObGu9bTxd/XmelZH0ngFe0skfgS3ej035lOTy E5Pwa6bRHOvfBoVTVraL4W1XtmNt4krDawlJ5HR3oUODskUATyURzdofhnTSCCXqoVRHRkNNhJfL Cjz2/Dskoqd+iOk49udZPwuEs/NE1G4NLV8bNTeI+0yp/69LKrgoi6LLc2JrS0xNiwAAAAAAAA== --Apple-Mail-4-425722734--