I am testing partitioning of a large table. I am INHERITING child tables. It is using 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) -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
