Re: postgres performance

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