sequential scan on child partition tables - Mailing list pgsql-performance

Hi

This query is doing a sequential scan on the child partitions even
though indexes on all constrained columns are present

The box is very lightly loaded (8 core 15K 6x300G Raid 10 disks)



 explain analyze
 select thedate,sent.theboxid_id,sub_box_id,box_num,sum(summcount) as
event_count,'ACC'
       from masterdevice_type_daily_warehousedim a,
masterdevice_tr_theboxid sent where thedate > '2009-10-06' and box_num
in (
                       select distinct box_num from
themodule_type_daily where theboxid like 'val%' and thedate >
current_timestamp - interval '8 days')
       and a.theboxid_id = sent.theboxid_id and sent.theboxid_id in
(select theboxid_id from masterdevice_tr_theboxid where theboxid like
'val%')
       group by thedate,sent.theboxid_id,sub_box_id,box_num;


HashAggregate  (cost=2066083.46..2066101.39 rows=1434 width=32)
(actual time=230503.792..230503.979 rows=185 loops=1)
  ->  Hash IN Join  (cost=1246.83..2066065.54 rows=1434 width=32)
(actual time=109.903..230257.929 rows=75196 loops=1)
        Hash Cond: ("outer".box_num = "inner".box_num)
        ->  Hash Join  (cost=28.86..2063399.16 rows=286815 width=32)
(actual time=31.437..4619416.805 rows=19430638 loops=1)
              Hash Cond: ("outer".theboxid_id = "inner".theboxid_id)
              ->  Append  (cost=1.67..1745007.76 rows=63099210
width=32) (actual time=25.792..-17410926.763 rows=63095432 loops=1)
                    ->  Bitmap Heap Scan on
masterdevice_type_daily_warehousedim a  (cost=1.67..14.04 rows=190
width=32) (actual time=0.064..0.064 rows=0 loops=1)
                          Recheck Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                          ->  Bitmap Index Scan on
masterdevice_type_daily_dim_idx1  (cost=0.00..1.67 rows=190 width=0)
(actual time=0.060..0.060 rows=0 loops=1)
                                Index Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Index Scan using
masterdevice_type_daily_warehousedim_2009_10_06_thedate on
masterdevice_type_daily_warehousedim_2009_10_06 a  (cost=0.00..2.01
rows=1 width=32) (actual time=22.933..22.933 rows=0 loops=1)
                          Index Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Seq Scan on
masterdevice_type_daily_warehousedim_2009_10_07 a
(cost=0.00..296412.20 rows=10698736 width=32) (actual
time=2.792..4426510.569 rows=10700096 loops=1)
                          Filter: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Seq Scan on
masterdevice_type_daily_warehousedim_2009_10_08 a
(cost=0.00..293246.17 rows=10584814 width=32) (actual
time=11.525..22032843.754 rows=10585494 loops=1)
                          Filter: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Seq Scan on
masterdevice_type_daily_warehousedim_2009_10_09 a
(cost=0.00..283875.83 rows=10247586 width=32) (actual
time=7.859..16916.509 rows=10246536 loops=1)
                          Filter: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Seq Scan on
masterdevice_type_daily_warehousedim_2009_10_10 a
(cost=0.00..233267.99 rows=8427839 width=32) (actual
time=0.036..4411163.299 rows=8426631 loops=1)
                          Filter: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Seq Scan on
masterdevice_type_daily_warehousedim_2009_10_11 a
(cost=0.00..188678.36 rows=6844269 width=32) (actual
time=0.042..8808635.489 rows=6845416 loops=1)
                          Filter: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Seq Scan on
masterdevice_type_daily_warehousedim_2009_10_12 a
(cost=0.00..224034.12 rows=8123690 width=32) (actual
time=0.035..13873.990 rows=8123671 loops=1)
                          Filter: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Seq Scan on
masterdevice_type_daily_warehousedim_2009_10_13 a
(cost=0.00..225224.31 rows=8168665 width=32) (actual
time=0.047..12855.608 rows=8167588 loops=1)
                          Filter: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Bitmap Heap Scan on
masterdevice_type_daily_warehousedim_2009_10_14 a  (cost=1.67..14.04
rows=190 width=32) (actual time=0.047..0.047 rows=0 loops=1)
                          Recheck Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                          ->  Bitmap Index Scan on
masterdevice_type_daily_warehousedim_2009_10_14_thedate
(cost=0.00..1.67 rows=190 width=0) (actual time=0.043..0.043 rows=0
loops=1)
                                Index Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Bitmap Heap Scan on
masterdevice_type_daily_warehousedim_2009_10_15 a  (cost=1.67..14.04
rows=190 width=32) (actual time=0.021..0.021 rows=0 loops=1)
                          Recheck Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                          ->  Bitmap Index Scan on
masterdevice_type_daily_warehousedim_2009_10_15_thedate
(cost=0.00..1.67 rows=190 width=0) (actual time=0.019..0.019 rows=0
loops=1)
                                Index Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Bitmap Heap Scan on
masterdevice_type_daily_warehousedim_2009_10_16 a  (cost=1.67..14.04
rows=190 width=32) (actual time=0.031..0.031 rows=0 loops=1)
                          Recheck Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                          ->  Bitmap Index Scan on
masterdevice_type_daily_warehousedim_2009_10_16_thedate
(cost=0.00..1.67 rows=190 width=0) (actual time=0.030..0.030 rows=0
loops=1)
                                Index Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Bitmap Heap Scan on
masterdevice_type_daily_warehousedim_2009_10_17 a  (cost=1.67..14.04
rows=190 width=32) (actual time=0.020..0.020 rows=0 loops=1)
                          Recheck Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                          ->  Bitmap Index Scan on
masterdevice_type_daily_warehousedim_2009_10_17_thedate
(cost=0.00..1.67 rows=190 width=0) (actual time=0.019..0.019 rows=0
loops=1)
                                Index Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Bitmap Heap Scan on
masterdevice_type_daily_warehousedim_2009_10_18 a  (cost=1.67..14.04
rows=190 width=32) (actual time=0.020..0.020 rows=0 loops=1)
                          Recheck Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                          ->  Bitmap Index Scan on
masterdevice_type_daily_warehousedim_2009_10_18_thedate
(cost=0.00..1.67 rows=190 width=0) (actual time=0.019..0.019 rows=0
loops=1)
                                Index Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Bitmap Heap Scan on
masterdevice_type_daily_warehousedim_2009_10_19 a  (cost=1.67..14.04
rows=190 width=32) (actual time=0.023..0.023 rows=0 loops=1)
                          Recheck Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                          ->  Bitmap Index Scan on
masterdevice_type_daily_warehousedim_2009_10_19_thedate
(cost=0.00..1.67 rows=190 width=0) (actual time=0.022..0.022 rows=0
loops=1)
                                Index Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Bitmap Heap Scan on
masterdevice_type_daily_warehousedim_2009_10_20 a  (cost=1.67..14.04
rows=190 width=32) (actual time=0.024..0.024 rows=0 loops=1)
                          Recheck Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                          ->  Bitmap Index Scan on
masterdevice_type_daily_warehousedim_2009_10_20_thedate
(cost=0.00..1.67 rows=190 width=0) (actual time=0.023..0.023 rows=0
loops=1)
                                Index Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Bitmap Heap Scan on
masterdevice_type_daily_warehousedim_2009_10_21 a  (cost=1.67..14.04
rows=190 width=32) (actual time=0.018..0.018 rows=0 loops=1)
                          Recheck Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                          ->  Bitmap Index Scan on
masterdevice_type_daily_warehousedim_2009_10_21_thedate
(cost=0.00..1.67 rows=190 width=0) (actual time=0.016..0.016 rows=0
loops=1)
                                Index Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Bitmap Heap Scan on
masterdevice_type_daily_warehousedim_2009_10_22 a  (cost=1.67..14.04
rows=190 width=32) (actual time=0.019..0.019 rows=0 loops=1)
                          Recheck Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                          ->  Bitmap Index Scan on
masterdevice_type_daily_warehousedim_2009_10_22_thedate
(cost=0.00..1.67 rows=190 width=0) (actual time=0.017..0.017 rows=0
loops=1)
                                Index Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Bitmap Heap Scan on
masterdevice_type_daily_warehousedim_2009_10_23 a  (cost=1.67..14.04
rows=190 width=32) (actual time=0.020..0.020 rows=0 loops=1)
                          Recheck Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                          ->  Bitmap Index Scan on
masterdevice_type_daily_warehousedim_2009_10_23_thedate
(cost=0.00..1.67 rows=190 width=0) (actual time=0.019..0.019 rows=0
loops=1)
                                Index Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Bitmap Heap Scan on
masterdevice_type_daily_warehousedim_2009_10_24 a  (cost=1.67..14.04
rows=190 width=32) (actual time=0.025..0.025 rows=0 loops=1)
                          Recheck Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                          ->  Bitmap Index Scan on
masterdevice_type_daily_warehousedim_2009_10_24_thedate
(cost=0.00..1.67 rows=190 width=0) (actual time=0.023..0.023 rows=0
loops=1)
                                Index Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Bitmap Heap Scan on
masterdevice_type_daily_warehousedim_2009_10_25 a  (cost=1.67..14.04
rows=190 width=32) (actual time=0.020..0.020 rows=0 loops=1)
                          Recheck Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                          ->  Bitmap Index Scan on
masterdevice_type_daily_warehousedim_2009_10_25_thedate
(cost=0.00..1.67 rows=190 width=0) (actual time=0.018..0.018 rows=0
loops=1)
                                Index Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Bitmap Heap Scan on
masterdevice_type_daily_warehousedim_2009_10_26 a  (cost=1.67..14.04
rows=190 width=32) (actual time=0.029..0.029 rows=0 loops=1)
                          Recheck Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                          ->  Bitmap Index Scan on
masterdevice_type_daily_warehousedim_2009_10_26_thedate
(cost=0.00..1.67 rows=190 width=0) (actual time=0.028..0.028 rows=0
loops=1)
                                Index Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Bitmap Heap Scan on
masterdevice_type_daily_warehousedim_2009_10_27 a  (cost=1.67..14.04
rows=190 width=32) (actual time=0.019..0.019 rows=0 loops=1)
                          Recheck Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                          ->  Bitmap Index Scan on
masterdevice_type_daily_warehousedim_2009_10_27_thedate
(cost=0.00..1.67 rows=190 width=0) (actual time=0.017..0.017 rows=0
loops=1)
                                Index Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Bitmap Heap Scan on
masterdevice_type_daily_warehousedim_2009_10_28 a  (cost=1.67..14.04
rows=190 width=32) (actual time=0.052..0.052 rows=0 loops=1)
                          Recheck Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                          ->  Bitmap Index Scan on
masterdevice_type_daily_warehousedim_2009_10_28_thedate
(cost=0.00..1.67 rows=190 width=0) (actual time=0.050..0.050 rows=0
loops=1)
                                Index Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Bitmap Heap Scan on
masterdevice_type_daily_warehousedim_2009_10_29 a  (cost=1.67..14.04
rows=190 width=32) (actual time=0.019..0.019 rows=0 loops=1)
                          Recheck Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                          ->  Bitmap Index Scan on
masterdevice_type_daily_warehousedim_2009_10_29_thedate
(cost=0.00..1.67 rows=190 width=0) (actual time=0.017..0.017 rows=0
loops=1)
                                Index Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Bitmap Heap Scan on
masterdevice_type_daily_warehousedim_2009_10_30 a  (cost=1.67..14.04
rows=190 width=32) (actual time=0.020..0.020 rows=0 loops=1)
                          Recheck Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                          ->  Bitmap Index Scan on
masterdevice_type_daily_warehousedim_2009_10_30_thedate
(cost=0.00..1.67 rows=190 width=0) (actual time=0.018..0.018 rows=0
loops=1)
                                Index Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                    ->  Bitmap Heap Scan on
masterdevice_type_daily_warehousedim_2009_10_31 a  (cost=1.67..14.04
rows=190 width=32) (actual time=0.020..0.020 rows=0 loops=1)
                          Recheck Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
                          ->  Bitmap Index Scan on
masterdevice_type_daily_warehousedim_2009_10_31_thedate
(cost=0.00..1.67 rows=190 width=0) (actual time=0.018..0.018 rows=0
loops=1)
                                Index Cond: (thedate > '2009-10-06
00:00:00'::timestamp without time zone)
              ->  Hash  (cost=27.19..27.19 rows=2 width=8) (actual
time=5.609..5.609 rows=3 loops=1)
                    ->  Nested Loop  (cost=15.51..27.19 rows=2
width=8) (actual time=5.568..5.597 rows=3 loops=1)
                          ->  Unique  (cost=15.51..15.52 rows=2
width=4) (actual time=0.157..0.165 rows=3 loops=1)
                                ->  Sort  (cost=15.51..15.52 rows=2
width=4) (actual time=0.156..0.158 rows=3 loops=1)
                                      Sort Key:
masterdevice_tr_theboxid.theboxid_id
                                      ->  Seq Scan on
masterdevice_tr_theboxid  (cost=0.00..15.50 rows=2 width=4) (actual
time=0.061..0.065 rows=3 loops=1)
                                            Filter: ((theboxid)::text
~~ 'val%'::text)
                          ->  Index Scan using
masterdevice_tr_theboxid_pk on masterdevice_tr_theboxid sent
(cost=0.00..5.82 rows=1 width=4) (actual time=1.804..1.805 rows=1
loops=3)
                                Index Cond: (sent.theboxid_id =
"outer".theboxid_id)
        ->  Hash  (cost=1217.97..1217.97 rows=1 width=8) (actual
time=70.198..70.198 rows=29 loops=1)
              ->  Unique  (cost=1217.85..1217.96 rows=1 width=8)
(actual time=69.909..70.176 rows=29 loops=1)
                    ->  Sort  (cost=1217.85..1217.90 rows=21 width=8)
(actual time=69.907..70.015 rows=325 loops=1)
                          Sort Key: themodule_type_daily.box_num
                          ->  Bitmap Heap Scan on
themodule_type_daily  (cost=98.51..1217.39 rows=21 width=8) (actual
time=43.474..69.360 rows=325 loops=1)
                                Recheck Cond: (thedate > (now() - '8
days'::interval))
                                Filter: ((theboxid)::text ~~ 'val%'::text)
                                ->  Bitmap Index Scan on
themodule_dn_tr_idx1  (cost=0.00..98.51 rows=4144 width=0) (actual
time=25.753..25.753 rows=1230 loops=1)
                                      Index Cond: (thedate > (now() -
'8 days'::interval))
 Total runtime: 230512.670 ms