On 11/16/2011 04:53 PM, Tory M Blue wrote:
Linux F12 64bit Postgres 8.4.4 16 proc / 32GB 8 disk 15KRPM SAS/Raid 5 (I know!)shared_buffers = 6000MB #temp_buffers = 8MB max_prepared_transactions = 0 work_mem = 250MB maintenance_work_mem = 1000MB We now have about 180mill records in that table. The database size is about 580GB and the userstats table which is the biggest one and the one we query the most is 83GB. Just a basic query takes 4 minutes: For e.g. select count(distinct uid) from userstats where log_date>'11/7/2011'
How'd you feel about keeping a monthly summary table? Update it daily, with only a days worth of stats, then you could query the summary table much faster. That's what I do for my website stats. I log details for a month, then summarize everything into a summary table, and blow away the details. You wouldn't have to delete the details if you wanted them, just keeping the summary table updated would be enough. -Andy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
