Re: High CPU usage after partitioning

In PG 9.2 I’m getting “Index Only Scan Backward” for every partition in the first part of execution plan, when looking for MAX in partitioned table on a similar query:

" -> Index Only Scan Backward using pk_cycle_200610 on gp_cycle_200610 gp_cycle (cost=0.00..8.34 rows=5 width=8) (actual time=0.021..0.021 rows=1 loops=1)"
" Index Cond: (cycle_date_time IS NOT NULL)"
" Heap Fetches: 0"

May be you should upgrade to 9.2.

Regards,
Igor Neyman

From: rudi [mailto:rudolone(at)gmail(dot)com]
Sent: Tuesday, January 22, 2013 10:08 AM
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: High CPU usage after partitioning

On Tue, Jan 22, 2013 at 3:46 PM, Andrew Dunstan <andrew(at)dunslane(dot)net<mailto:andrew(at)dunslane(dot)net>> wrote:
The query is pretty simple and standard, the behaviour (and the plan) is totally different when it comes to a partitioned table.

Partioned table query => explain analyze SELECT "sb_logs".* FROM "sb_logs" WHERE (device_id = 901 AND date_taken = (SELECT MAX(date_taken) FROM sb_logs WHERE device_id = 901));

And there you have it. Constraint exclusion does not work in cases like this. It only works with static expressions (such as a literal date in this case).

Ok, but I would have expected same plant repeated 4 times. When the table is not partitioned, the plan is defintely smarter: it knows that index is reversed and looks for max with an index scan backward). When the table is partitioned, it scan forward and I guess it will always do a full index scan.

--
rd

This is the way the world ends.
Not with a bang, but a whimper.