On 6.12.2013 18:36, chidamparam muthusamy wrote: > hi, > Registered with PostgreSQL Help Forum to identify and resolve the > Postgres DB performance issue, received suggestions but could not > improve the speed/response time. Please help. > > Details: > Postgres Version 9.3.1 > Server configuration: > Processor: 2 x Intel Quad core E5620 @ 2.40GHz > RAM: 16 GB > > Postgres configuration: > Effective cache size = 10 GB > shared Buffer = 1250 MB > random page cost = 4 > > Table size = 60 GB > Number of records = 44 million > Carried out Vacuum Analyze after inserting new records and also after > creating Index, > 6 months data, every month around 10 GB will get added. Expecting good > performance with 3 years data. So, what is good performance? What times do you need to achieve for the queries you've posted? It's difficult to read the explain plans wrapped in the message, so I've pasted some of them into explain.depesz.com: http://explain.depesz.com/s/9SH http://explain.depesz.com/s/hFp The estimates are very accurate, and as Richard Huxton pointed out, the dominating part is the bitmap heap scan. I assume this is because or reading the data from disk. Can you check iostat/vmstat while the queries are running? Are you CPU or I/O bound? I'd guess the latter. In that case, adding more RAM / more powerful I/O is probably the easiest way to improve the performance. And a partitioning (but that depends on the queries, as it may improve some and hurt others). regards Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
