Re: High CPU usage after partitioning

On Tue, Jan 22, 2013 at 3:04 PM, Merlin Moncure <mmoncure@gmail.com> 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));

Plain table query => explain analyze SELECT  "iv_logs".* FROM "iv_logs"  WHERE (device_id = 1475 AND date_taken = (SELECT MAX(date_taken) FROM iv_logs WHERE device_id = 1475));

sb_logs and iv_logs have identical index structure and similar cardinality (about ~12.000.000 rows the first, ~9.000.000 rows the second).

sb_logs PLAN:

  InitPlan 1 (returns $0)

    ->  Aggregate  (cost=339424.47..339424.48 rows=1 width=8) (actual time=597.742..597.742 rows=1 loops=1)

          ->  Append  (cost=0.00..339381.68 rows=17114 width=8) (actual time=42.791..594.001 rows=19024 loops=1)

                ->  Seq Scan on sb_logs  (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)

                      Filter: (device_id = 901)

                ->  Index Scan using sb_logs_2012_on_date_taken_and_device_id on sb_logs_2012 sb_logs  (cost=0.00..319430.51 rows=16003 width=8) (actual time=42.789..559.165 rows=17817 loops=1)

                      Index Cond: (device_id = 901)

                ->  Index Scan using sb_logs_2013_on_date_taken_and_device_id on sb_logs_2013 sb_logs  (cost=0.00..19932.46 rows=1106 width=8) (actual time=0.037..31.699 rows=1207 loops=1)

                      Index Cond: (device_id = 901)

                ->  Bitmap Heap Scan on sb_logs_2014 sb_logs  (cost=10.25..18.71 rows=4 width=8) (actual time=0.012..0.012 rows=0 loops=1)

                      Recheck Cond: (device_id = 901)

                      ->  Bitmap Index Scan on sb_logs_2014_on_date_taken_and_device_id  (cost=0.00..10.25 rows=4 width=0) (actual time=0.010..0.010 rows=0 loops=1)

                            Index Cond: (device_id = 901)

  ->  Append  (cost=0.00..26.86 rows=4 width=86) (actual time=597.808..597.811 rows=1 loops=1)

        ->  Seq Scan on sb_logs  (cost=0.00..0.00 rows=1 width=90) (actual time=0.022..0.022 rows=0 loops=1)

              Filter: ((device_id = 901) AND (date_taken = $0))

        ->  Index Scan using sb_logs_2012_on_date_taken_and_device_id on sb_logs_2012 sb_logs  (cost=0.00..10.20 rows=1 width=90) (actual time=597.773..597.773 rows=0 loops=1)

              Index Cond: ((date_taken = $0) AND (device_id = 901))

        ->  Index Scan using sb_logs_2013_on_date_taken_and_device_id on sb_logs_2013 sb_logs  (cost=0.00..8.39 rows=1 width=91) (actual time=0.011..0.011 rows=1 loops=1)

              Index Cond: ((date_taken = $0) AND (device_id = 901))

        ->  Index Scan using sb_logs_2014_on_date_taken_and_device_id on sb_logs_2014 sb_logs  (cost=0.00..8.27 rows=1 width=72) (actual time=0.003..0.003 rows=0 loops=1)

              Index Cond: ((date_taken = $0) AND (device_id = 901))

Total runtime: 598.049 ms

iv_logs PLAN: