Re: Partitioning and performance

Am 28. Mai 2015 17:15:22 MESZ, schrieb Ravi Krishna <sravikrishna3(at)gmail(dot)com>:
>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(at)postgresql(dot)org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general

Have you set up constraints on the partitions? The planner needs to know what is in the child tables so it can avoid scanning them.

Jan

Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet.