Re: High CPU usage after partitioning

[Igor Neyman <ineyman@xxxxxxxxxxxxxx>]

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@xxxxxxxxx]
Sent: Tuesday, January 22, 2013 10:08 AM
To: pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: High CPU usage after partitioning

 

On Tue, Jan 22, 2013 at 3:46 PM, Andrew Dunstan <andrew@xxxxxxxxxxxx> 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.