Search Postgresql Archives

Are partitions getting pruned?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux