Обсуждение: Partitioning and performance
I am testing partitioning of a large table. I am doing a range partitioning based on a sequence col, which also acts as the primary key. For inserts I am using a trigger which will redirect insert to the right table based on the value of the primary key. Based on my testing, I see that the insert speed is less than 10% different than a non partitioned table. I am using SET constraint_exclusion = on and I checked that via ANALYZE that the planner does not consider non qualifying child tables. yet, selects and updates based on the primary key show anywhere from 40 to 200% slowness as compared to non partition. One thing I notice is that, even with partition pruning, the planner scans the base table and the table matching the condition. Is that the additional overhead. I am attaching below the output of analyze. =========================== On a non partitioned table explain select count(*) from tstesting.account where account_row_inst = 101 ; Aggregate (cost=8.16..8.17 rows=1 width=0) -> Index Only Scan using account_pkey on account (cost=0.14..8.16 rows=1 width=0) Index Cond: (account_row_inst = 101) (3 rows) With partition pruning: Aggregate (cost=8.45..8.46 rows=1 width=0) -> Append (cost=0.00..8.44 rows=2 width=0) -> Seq Scan on account (cost=0.00..0.00 rows=1 width=0) Filter: (account_row_inst = 101) -> Index Only Scan using account_part1_pkey on account_part1 (cost=0.42..8.44 rows=1 width=0) Index Cond: (account_row_inst = 101) (6 rows) On a partitioned table, with no partition pruning. explain analyze select count(*) from tstesting.account where account_row_inst = 101 ; Aggregate (cost=29.77..29.78 rows=1 width=0) (actual time=0.032..0.032 rows=1 loops=1) -> Append (cost=0.00..29.76 rows=5 width=0) (actual time=0.029..0.029 rows=0 loops=1) -> Seq Scan on account (cost=0.00..0.00 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1) Filter: (account_row_inst = 101) -> Index Only Scan using account_part1_pkey on account_part1 (cost=0.42..4.44 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=1) Index Cond: (account_row_inst = 101) Heap Fetches: 0 -> Index Only Scan using account_part2_pkey on account_part2 (cost=0.42..8.44 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=1) Index Cond: (account_row_inst = 101) Heap Fetches: 0 -> Index Only Scan using account_part3_pkey on account_part3 (cost=0.42..8.44 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=1) Index Cond: (account_row_inst = 101) Heap Fetches: 0 -> Index Only Scan using account_part4_pkey on account_part4 (cost=0.42..8.44 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=1) Index Cond: (account_row_inst = 101) Heap Fetches: 0 Planning time: 0.635 ms Execution time: 0.137 ms (18 rows)
On Thu, May 28, 2015 at 10:31 AM, Ravi Krishna <sravikrishna3@gmail.com> wrote: > I am testing partitioning of a large table. I am doing a range Sorry I forgot to clarify. I am using INHERITS for partioning with check constraing built for range partitioning.
On 5/28/15 9:31 AM, Ravi Krishna wrote: > explain select count(*) from tstesting.account where account_row_inst = 101 ; > Aggregate (cost=8.16..8.17 rows=1 width=0) > -> Index Only Scan using account_pkey on account (cost=0.14..8.16 > rows=1 width=0) > Index Cond: (account_row_inst = 101) EXPLAIN only shows what the planner thinks a query will cost. For any real testing, you need EXPLAIN ANALYZE. Also, understand that partitioning isn't a magic bullet. It can make some operations drastically faster, but it's not going to help every scenario, and will actually slow some other operations down. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Data in Trouble? Get it in Treble! http://BlueTreble.com