Thread: sequential scan on child partition tables
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
Anj Adu <fotographs@gmail.com> writes:
> This query is doing a sequential scan on the child partitions even
> though indexes on all constrained columns are present
It looks to me like it's doing exactly what it is supposed to, ie,
indexscan on the partitions where it would help and seqscans on the
partitions where it wouldn't. Indexscan is not better than seqscan
for retrieving all or most of a table.
regards, tom lane
This appears to be a bug in the optimizer with resepct to planning
queries involving child partitions. It is clear that "any" index is
being ignored even if the selectivity is high. I had to re-write the
same query by explicitly "union-all" ' ing the queries for individual
partitions.
On Wed, Oct 14, 2009 at 11:02 PM, Anj Adu <fotographs@gmail.com> wrote:
> That..however is not how the data is distributed...the query is doing
> a sequential scan on "every" partition that is within the date
> constraint specified...i.e 2009-10-07 thru 2009-10-13......there is no
> data from 2009-10-14 onwards. The constraints when applied account for
> less than 25% of the data.
>
> When I replace the query with a "union all" of all specific
> partitions..the query runs very quickly. Below is the explain plan for
> the "union-all" version of the query.
>
>
> HashAggregate (cost=59285.14..59285.93 rows=63 width=56) (actual
> time=276141.218..276141.378 rows=185 loops=1)
> -> Append (cost=8496.41..59283.73 rows=63 width=32) (actual
> time=1012.844..276140.866 rows=185 loops=1)
> -> Subquery Scan "*SELECT* 1" (cost=8496.41..8496.61 rows=9
> width=32) (actual time=1012.843..1012.910 rows=28 loops=1)
> -> HashAggregate (cost=8496.41..8496.52 rows=9
> width=32) (actual time=1012.839..1012.865 rows=28 loops=1)
> -> Hash Join (cost=1250.35..8496.29 rows=9
> width=32) (actual time=97.599..990.893 rows=10316 loops=1)
> Hash Cond: ("outer".sentryid_id =
> "inner".sentryid_id)
> -> Nested Loop (cost=1234.84..8470.84
> rows=1971 width=32) (actual time=97.492..975.741 rows=17602 loops=1)
> -> Unique (cost=1218.94..1219.05
> rows=1 width=8) (actual time=44.352..44.653 rows=29 loops=1)
> -> Sort
> (cost=1218.94..1219.00 rows=21 width=8) (actual time=44.351..44.469
> rows=307 loops=1)
> Sort Key: ssa_tr_dy.source_ip_num
> -> Bitmap Heap Scan on
> ssa_tr_dy (cost=98.52..1218.48 rows=21 width=8) (actual
> time=33.170..43.848 rows=307 loops=1)
> Recheck Cond:
> (firstoccurrence > (now() - '8 days'::interval))
> Filter:
> ((sentryid)::text ~~ 'edmc%'::text)
> -> Bitmap Index
> Scan on ssa_dn_tr_idx1 (cost=0.00..98.52 rows=4148 width=0) (actual
> time=16.999..16.999 rows=1230 loops=1)
> Index Cond:
> (firstoccurrence > (now() - '8 days'::interval))
> -> Bitmap Heap Scan on
> pix_tr_dy_dimension_2009_10_07 a (cost=15.90..7227.15 rows=1971
> width=32) (actual time=26.373..31.319 rows=607 loops=29)
> Recheck Cond: (a.source_ip_num
> = "outer".source_ip_num)
> -> Bitmap Index Scan on
> pix_tr_dy_dimension_2009_10_07_source_ip_num (cost=0.00..15.90
> rows=1971 width=0) (actual time=26.179..26.179 rows=607 loops=29)
> Index Cond:
> (a.source_ip_num = "outer".source_ip_num)
> -> Hash (cost=15.50..15.50 rows=2
> width=4) (actual time=0.087..0.087 rows=3 loops=1)
> -> Seq Scan on pix_tr_sentryid sent
> (cost=0.00..15.50 rows=2 width=4) (actual time=0.061..0.067 rows=3
> loops=1)
> Filter: ((sentryid)::text ~~
> 'edmc%'::text)
> -> Subquery Scan "*SELECT* 2" (cost=9219.21..9219.44
> rows=10 width=32) (actual time=54867.609..54867.673 rows=30 loops=1)
> -> HashAggregate (cost=9219.21..9219.34 rows=10
> width=32) (actual time=54867.605..54867.636 rows=30 loops=1)
> -> Hash Join (cost=1251.08..9219.09 rows=10
> width=32) (actual time=36.722..54826.062 rows=12975 loops=1)
> Hash Cond: ("outer".sentryid_id =
> "inner".sentryid_id)
> -> Nested Loop (cost=1235.58..9192.58
> rows=2180 width=32) (actual time=36.661..54800.027 rows=19624 loops=1)
> -> Unique (cost=1218.94..1219.05
> rows=1 width=8) (actual time=1.807..2.153 rows=29 loops=1)
> -> Sort
> (cost=1218.94..1219.00 rows=21 width=8) (actual time=1.805..1.939
> rows=307 loops=1)
> Sort Key: ssa_tr_dy.source_ip_num
> -> Bitmap Heap Scan on
> ssa_tr_dy (cost=98.52..1218.48 rows=21 width=8) (actual
> time=0.739..1.478 rows=307 loops=1)
> Recheck Cond:
> (firstoccurrence > (now() - '8 days'::interval))
> Filter:
> ((sentryid)::text ~~ 'edmc%'::text)
> -> Bitmap Index
> Scan on ssa_dn_tr_idx1 (cost=0.00..98.52 rows=4148 width=0) (actual
> time=0.709..0.709 rows=1230 loops=1)
> Index Cond:
> (firstoccurrence > (now() - '8 days'::interval))
> -> Bitmap Heap Scan on
> pix_tr_dy_dimension_2009_10_08 a (cost=16.63..7946.27 rows=2180
> width=32) (actual time=28.162..1888.539 rows=677 loops=29)
> Recheck Cond: (a.source_ip_num
> = "outer".source_ip_num)
> -> Bitmap Index Scan on
> pix_tr_dy_dimension_2009_10_08_source_ip_num (cost=0.00..16.63
> rows=2180 width=0) (actual time=24.044..24.044 rows=677 loops=29)
> Index Cond:
> (a.source_ip_num = "outer".source_ip_num)
> -> Hash (cost=15.50..15.50 rows=2
> width=4) (actual time=0.040..0.040 rows=3 loops=1)
> -> Seq Scan on pix_tr_sentryid sent
> (cost=0.00..15.50 rows=2 width=4) (actual time=0.029..0.036 rows=3
> loops=1)
> Filter: ((sentryid)::text ~~
> 'edmc%'::text)
> -> Subquery Scan "*SELECT* 3" (cost=8814.11..8814.31 rows=9
> width=32) (actual time=51634.494..51634.547 rows=24 loops=1)
> -> HashAggregate (cost=8814.11..8814.22 rows=9
> width=32) (actual time=51634.490..51634.519 rows=24 loops=1)
> -> Hash Join (cost=1250.68..8814.00 rows=9
> width=32) (actual time=65.658..51599.190 rows=10962 loops=1)
> Hash Cond: ("outer".sentryid_id =
> "inner".sentryid_id)
> -> Nested Loop (cost=1235.18..8788.07
> rows=2067 width=32) (actual time=65.596..51574.491 rows=20261 loops=1)
> -> Unique (cost=1218.94..1219.05
> rows=1 width=8) (actual time=1.781..2.104 rows=29 loops=1)
> -> Sort
> (cost=1218.94..1219.00 rows=21 width=8) (actual time=1.779..1.896
> rows=307 loops=1)
> Sort Key: ssa_tr_dy.source_ip_num
> -> Bitmap Heap Scan on
> ssa_tr_dy (cost=98.52..1218.48 rows=21 width=8) (actual
> time=0.730..1.455 rows=307 loops=1)
> Recheck Cond:
> (firstoccurrence > (now() - '8 days'::interval))
> Filter:
> ((sentryid)::text ~~ 'edmc%'::text)
> -> Bitmap Index
> Scan on ssa_dn_tr_idx1 (cost=0.00..98.52 rows=4148 width=0) (actual
> time=0.699..0.699 rows=1230 loops=1)
> Index Cond:
> (firstoccurrence > (now() - '8 days'::interval))
> -> Bitmap Heap Scan on
> pix_tr_dy_dimension_2009_10_09 a (cost=16.23..7543.17 rows=2067
> width=32) (actual time=27.327..1777.293 rows=699 loops=29)
> Recheck Cond: (a.source_ip_num
> = "outer".source_ip_num)
> -> Bitmap Index Scan on
> pix_tr_dy_dimension_2009_10_09_source_ip_num (cost=0.00..16.23
> rows=2067 width=0) (actual time=23.784..23.784 rows=699 loops=29)
> Index Cond:
> (a.source_ip_num = "outer".source_ip_num)
> -> Hash (cost=15.50..15.50 rows=2
> width=4) (actual time=0.040..0.040 rows=3 loops=1)
> -> Seq Scan on pix_tr_sentryid sent
> (cost=0.00..15.50 rows=2 width=4) (actual time=0.029..0.036 rows=3
> loops=1)
> Filter: ((sentryid)::text ~~
> 'edmc%'::text)
> -> Subquery Scan "*SELECT* 4" (cost=9686.21..9686.45
> rows=11 width=32) (actual time=33707.854..33707.900 rows=24 loops=1)
> -> HashAggregate (cost=9686.21..9686.34 rows=11
> width=32) (actual time=33707.851..33707.874 rows=24 loops=1)
> -> Hash Join (cost=1252.67..9686.07 rows=11
> width=32) (actual time=37.055..33679.711 rows=7580 loops=1)
> Hash Cond: ("outer".sentryid_id =
> "inner".sentryid_id)
> -> Nested Loop (cost=1237.17..9658.71
> rows=2349 width=32) (actual time=37.001..33662.042 rows=11414 loops=1)
> -> Unique (cost=1218.94..1219.05
> rows=1 width=8) (actual time=1.903..2.273 rows=29 loops=1)
> -> Sort
> (cost=1218.94..1219.00 rows=21 width=8) (actual time=1.901..2.045
> rows=307 loops=1)
> Sort Key: ssa_tr_dy.source_ip_num
> -> Bitmap Heap Scan on
> ssa_tr_dy (cost=98.52..1218.48 rows=21 width=8) (actual
> time=0.788..1.570 rows=307 loops=1)
> Recheck Cond:
> (firstoccurrence > (now() - '8 days'::interval))
> Filter:
> ((sentryid)::text ~~ 'edmc%'::text)
> -> Bitmap Index
> Scan on ssa_dn_tr_idx1 (cost=0.00..98.52 rows=4148 width=0) (actual
> time=0.755..0.755 rows=1230 loops=1)
> Index Cond:
> (firstoccurrence > (now() - '8 days'::interval))
> -> Bitmap Heap Scan on
> pix_tr_dy_dimension_2009_10_10 a (cost=18.22..8410.29 rows=2349
> width=32) (actual time=14.229..1160.004 rows=394 loops=29)
> Recheck Cond: (a.source_ip_num
> = "outer".source_ip_num)
> -> Bitmap Index Scan on
> pix_tr_dy_dimension_2009_10_10_source_ip_num (cost=0.00..18.22
> rows=2349 width=0) (actual time=10.351..10.351 rows=394 loops=29)
> Index Cond:
> (a.source_ip_num = "outer".source_ip_num)
> -> Hash (cost=15.50..15.50 rows=2
> width=4) (actual time=0.037..0.037 rows=3 loops=1)
> -> Seq Scan on pix_tr_sentryid sent
> (cost=0.00..15.50 rows=2 width=4) (actual time=0.028..0.033 rows=3
> loops=1)
> Filter: ((sentryid)::text ~~
> 'edmc%'::text)
> -> Subquery Scan "*SELECT* 5" (cost=7840.14..7840.32 rows=8
> width=32) (actual time=27276.689..27276.734 rows=22 loops=1)
> -> HashAggregate (cost=7840.14..7840.24 rows=8
> width=32) (actual time=27276.685..27276.713 rows=22 loops=1)
> -> Hash Join (cost=1248.86..7840.04 rows=8
> width=32) (actual time=109.205..27254.938 rows=6134 loops=1)
> Hash Cond: ("outer".sentryid_id =
> "inner".sentryid_id)
> -> Nested Loop (cost=1233.35..7815.29
> rows=1832 width=32) (actual time=109.142..27240.746 rows=9592 loops=1)
> -> Unique (cost=1218.94..1219.05
> rows=1 width=8) (actual time=1.784..2.128 rows=29 loops=1)
> -> Sort
> (cost=1218.94..1219.00 rows=21 width=8) (actual time=1.782..1.922
> rows=307 loops=1)
> Sort Key: ssa_tr_dy.source_ip_num
> -> Bitmap Heap Scan on
> ssa_tr_dy (cost=98.52..1218.48 rows=21 width=8) (actual
> time=0.724..1.457 rows=307 loops=1)
> Recheck Cond:
> (firstoccurrence > (now() - '8 days'::interval))
> Filter:
> ((sentryid)::text ~~ 'edmc%'::text)
> -> Bitmap Index
> Scan on ssa_dn_tr_idx1 (cost=0.00..98.52 rows=4148 width=0) (actual
> time=0.694..0.694 rows=1230 loops=1)
> Index Cond:
> (firstoccurrence > (now() - '8 days'::interval))
> -> Bitmap Heap Scan on
> pix_tr_dy_dimension_2009_10_11 a (cost=14.41..6573.33 rows=1832
> width=32) (actual time=19.473..938.665 rows=331 loops=29)
> Recheck Cond: (a.source_ip_num
> = "outer".source_ip_num)
> -> Bitmap Index Scan on
> pix_tr_dy_dimension_2009_10_11_source_ip_num (cost=0.00..14.41
> rows=1832 width=0) (actual time=16.342..16.342 rows=331 loops=29)
> Index Cond:
> (a.source_ip_num = "outer".source_ip_num)
> -> Hash (cost=15.50..15.50 rows=2
> width=4) (actual time=0.039..0.039 rows=3 loops=1)
> -> Seq Scan on pix_tr_sentryid sent
> (cost=0.00..15.50 rows=2 width=4) (actual time=0.028..0.034 rows=3
> loops=1)
> Filter: ((sentryid)::text ~~
> 'edmc%'::text)
> -> Subquery Scan "*SELECT* 6" (cost=7850.10..7850.28 rows=8
> width=32) (actual time=62773.885..62773.954 rows=27 loops=1)
> -> HashAggregate (cost=7850.10..7850.20 rows=8
> width=32) (actual time=62773.880..62773.917 rows=27 loops=1)
> -> Hash Join (cost=1248.80..7850.00 rows=8
> width=32) (actual time=62.896..62719.489 rows=15348 loops=1)
> Hash Cond: ("outer".sentryid_id =
> "inner".sentryid_id)
> -> Nested Loop (cost=1233.30..7825.34
> rows=1815 width=32) (actual time=62.814..62687.076 rows=20370 loops=1)
> -> Unique (cost=1218.94..1219.05
> rows=1 width=8) (actual time=1.912..2.330 rows=29 loops=1)
> -> Sort
> (cost=1218.94..1219.00 rows=21 width=8) (actual time=1.912..2.063
> rows=307 loops=1)
> Sort Key: ssa_tr_dy.source_ip_num
> -> Bitmap Heap Scan on
> ssa_tr_dy (cost=98.52..1218.48 rows=21 width=8) (actual
> time=0.765..1.583 rows=307 loops=1)
> Recheck Cond:
> (firstoccurrence > (now() - '8 days'::interval))
> Filter:
> ((sentryid)::text ~~ 'edmc%'::text)
> -> Bitmap Index
> Scan on ssa_dn_tr_idx1 (cost=0.00..98.52 rows=4148 width=0) (actual
> time=0.733..0.733 rows=1230 loops=1)
> Index Cond:
> (firstoccurrence > (now() - '8 days'::interval))
> -> Bitmap Heap Scan on
> pix_tr_dy_dimension_2009_10_12 a (cost=14.35..6583.60 rows=1815
> width=32) (actual time=39.032..2160.324 rows=702 loops=29)
> Recheck Cond: (a.source_ip_num
> = "outer".source_ip_num)
> -> Bitmap Index Scan on
> pix_tr_dy_dimension_2009_10_12_source_ip_num (cost=0.00..14.35
> rows=1815 width=0) (actual time=30.805..30.805 rows=702 loops=29)
> Index Cond:
> (a.source_ip_num = "outer".source_ip_num)
> -> Hash (cost=15.50..15.50 rows=2
> width=4) (actual time=0.046..0.046 rows=3 loops=1)
> -> Seq Scan on pix_tr_sentryid sent
> (cost=0.00..15.50 rows=2 width=4) (actual time=0.035..0.041 rows=3
> loops=1)
> Filter: ((sentryid)::text ~~
> 'edmc%'::text)
> -> Subquery Scan "*SELECT* 7" (cost=7376.13..7376.31 rows=8
> width=32) (actual time=44866.931..44866.996 rows=30 loops=1)
> -> HashAggregate (cost=7376.13..7376.23 rows=8
> width=32) (actual time=44866.927..44866.969 rows=30 loops=1)
> -> Hash Join (cost=1248.32..7376.03 rows=8
> width=32) (actual time=77.172..44826.884 rows=11881 loops=1)
> Hash Cond: ("outer".sentryid_id =
> "inner".sentryid_id)
> -> Nested Loop (cost=1232.81..7352.06
> rows=1677 width=32) (actual time=77.098..44803.266 rows=16665 loops=1)
> -> Unique (cost=1218.94..1219.05
> rows=1 width=8) (actual time=1.816..2.149 rows=29 loops=1)
> -> Sort
> (cost=1218.94..1219.00 rows=21 width=8) (actual time=1.815..1.940
> rows=307 loops=1)
> Sort Key: ssa_tr_dy.source_ip_num
> -> Bitmap Heap Scan on
> ssa_tr_dy (cost=98.52..1218.48 rows=21 width=8) (actual
> time=0.718..1.491 rows=307 loops=1)
> Recheck Cond:
> (firstoccurrence > (now() - '8 days'::interval))
> Filter:
> ((sentryid)::text ~~ 'edmc%'::text)
> -> Bitmap Index
> Scan on ssa_dn_tr_idx1 (cost=0.00..98.52 rows=4148 width=0) (actual
> time=0.687..0.687 rows=1230 loops=1)
> Index Cond:
> (firstoccurrence > (now() - '8 days'::interval))
> -> Bitmap Heap Scan on
> pix_tr_dy_dimension_2009_10_13 a (cost=13.87..6112.04 rows=1677
> width=32) (actual time=27.864..1543.963 rows=575 loops=29)
> Recheck Cond: (a.source_ip_num
> = "outer".source_ip_num)
> -> Bitmap Index Scan on
> pix_tr_dy_dimension_2009_10_13_source_ip_num (cost=0.00..13.87
> rows=1677 width=0) (actual time=23.339..23.339 rows=575 loops=29)
> Index Cond:
> (a.source_ip_num = "outer".source_ip_num)
> -> Hash (cost=15.50..15.50 rows=2
> width=4) (actual time=0.050..0.050 rows=3 loops=1)
> -> Seq Scan on pix_tr_sentryid sent
> (cost=0.00..15.50 rows=2 width=4) (actual time=0.039..0.044 rows=3
> loops=1)
> Filter: ((sentryid)::text ~~
> 'edmc%'::text)
> Total runtime: 276143.006 ms
>
>
> On Wed, Oct 14, 2009 at 10:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Anj Adu <fotographs@gmail.com> writes:
>>> This query is doing a sequential scan on the child partitions even
>>> though indexes on all constrained columns are present
>>
>> It looks to me like it's doing exactly what it is supposed to, ie,
>> indexscan on the partitions where it would help and seqscans on the
>> partitions where it wouldn't. Indexscan is not better than seqscan
>> for retrieving all or most of a table.
>>
>> regards, tom lane
>>
>
On Thu, Oct 15, 2009 at 2:51 PM, Anj Adu <fotographs@gmail.com> wrote: > This appears to be a bug in the optimizer with resepct to planning > queries involving child partitions. It is clear that "any" index is > being ignored even if the selectivity is high. I had to re-write the > same query by explicitly "union-all" ' ing the queries for individual > partitions. So, did adjusting cost parameters help at all?
The actual data returned is a tiny fraction of the total table volume. Is there a way to force an index scan on the partitions in a guaranteed manner without resorting to re-writing queries with the union all on partitions. Thank you Sriram On Wed, Oct 14, 2009 at 10:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Anj Adu <fotographs@gmail.com> writes: >> This query is doing a sequential scan on the child partitions even >> though indexes on all constrained columns are present > > It looks to me like it's doing exactly what it is supposed to, ie, > indexscan on the partitions where it would help and seqscans on the > partitions where it wouldn't. Indexscan is not better than seqscan > for retrieving all or most of a table. > > regards, tom lane >