Re: High CPU usage after partitioning

On Mon, Jan 21, 2013 at 9:05 AM, rudi <rudolone(at)gmail(dot)com> wrote:
> Hello,
>
> I'm running postgresl 9.0. After partitioning a big table, CPU usage raised
> from average 5-10% to average 70-80%.
>
> - the table contains about 20.000.000 rows
> - partitions are selected using a trigger, based on an indexed field, a date
> (IF date_taken >= x AND date_taken < y)
> - I created 5 partitions, the 2012 one now contains most of the rows. The
> 2013 partition is the "live" partition, mostly insert, a few select based on
> the above indexed field. The 2013, 2014, 2015 partitions are empty
> - constraint execution is on.
>
> I have 2 weeks CPU usage reports and the pattern definately changed after I
> made the partitions. Any idea?

First thing that jumps to mind is you have some seq-scan heavy plans
that were not seq-scan before. Could be due to query fooling CE
mechanism or some other CE (probably fixable issue). To diagnose we
need to see some explain analyze plans of queries that are using
higher than expected cpu usage.

Second possible cause is trigger overhead from inserts. Not likely to
cause so much of a jump, but if this is the issue suggested
optimization path is to insert directly to the partition.

merlin