Postgres 8.4.9 on CentOS
I partitioned some tables over the weekend by month using a date field as the
partitioning column. Table inheritance was used and all indexes on the parent
were created on the partitions. constraint_exclustion = partition.
My question is, are partitions really getting pruned or are locks showing up
just due to inheritance.
When I run an explain of the query it shows partition pruning.
select * from voice.daily_nbr_list where item_date = '2013-03-17' and market =
'Chicago'
Result (cost=0.00..154563.67 rows=99468 width=210) (actual
time=557.634..2174.804 rows=104042 loops=1)
-> Append (cost=0.00..154563.67 rows=99468 width=210) (actual
time=557.631..2109.153 rows=104042 loops=1)
-> Seq Scan on daily_nbr_list (cost=0.00..15.10 rows=2 width=209) (actual
time=0.000..0.000 rows=0 loops=1)
Filter: ((item_date = '2013-03-17'::date) AND ((market)::text =
'Chicago'::text))
-> Bitmap Heap Scan on daily_nbr_list _201303 daily_nbr_list
(cost=2844.72..154548.57 rows=99466 width=210) (actual time=557.629..2098.579
rows=104042 loops=1)
Recheck Cond: (item_date = '2013-03-17'::date)
Filter: ((market)::text = 'Chicago'::text)
-> Bitmap Index Scan on daily_nbr_list_idx _201303 (cost=0.00..2819.86
rows=152412 width=0) (actual time=167.538..167.538 rows=153963 loops=1)
Index Cond: (item_date = '2013-03-17'::date)
Total runtime: 2181.130 ms
However, when I run the query and view the locks it’s using it shows all
partitions having locks on them.
select * from voice.daily_nbr_list where item_date = '2013-03-17' and market =
'Chicago'; voice.daily_nbr_list; AccessShareLock
select * from voice.daily_nbr_list where item_date = '2013-03-17' and market =
'Chicago'; voice.daily_nbr_list_201206; AccessShareLock
select * from voice.daily_nbr_list where item_date = '2013-03-17' and market =
'Chicago'; voice.daily_nbr_list_201207; AccessShareLock
select * from voice.daily_nbr_list where item_date = '2013-03-17' and market =
'Chicago'; voice.daily_nbr_list_201208; AccessShareLock
select * from voice.daily_nbr_list where item_date = '2013-03-17' and market =
'Chicago'; voice.daily_nbr_list_201209; AccessShareLock
select * from voice.daily_nbr_list where item_date = '2013-03-17' and market =
'Chicago'; voice.daily_nbr_list_201210; AccessShareLock
select * from voice.daily_nbr_list where item_date = '2013-03-17' and market =
'Chicago'; voice.daily_nbr_list_201211; AccessShareLock
select * from voice.daily_nbr_list where item_date = '2013-03-17' and market =
'Chicago'; voice.daily_nbr_list_201212; AccessShareLock
select * from voice.daily_nbr_list where item_date = '2013-03-17' and market =
'Chicago'; voice.daily_nbr_list_201301; AccessShareLock
select * from voice.daily_nbr_list where item_date = '2013-03-17' and market =
'Chicago'; voice.daily_nbr_list_201302; AccessShareLock
select * from voice.daily_nbr_list where item_date = '2013-03-17' and market =
'Chicago'; voice.daily_nbr_list_201303; AccessShareLock
select * from voice.daily_nbr_list where item_date = '2013-03-17' and market =
'Chicago'; voice.daily_nbr_list_201304; AccessShareLock
select * from voice.daily_nbr_list where item_date = '2013-03-17' and market =
'Chicago'; voice.daily_nbr_list_201305; AccessShareLock
I noticed row exclusive locks being held on all partitions for procedure calls
that update the data.
Thanks,
Robert
