Re: Partitioning and performance

Am 28. Mai 2015 18:25:42 MESZ, schrieb Ravi Krishna <sravikrishna3(at)gmail(dot)com>:
>> 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.
>
>Yes. each child table is defined as follows
>
>CREATE TABLE TSTESTING.ACCOUNT_PART1
>
> ( CHECK (ACCOUNT_ROW_INST BETWEEN 1001 and 271660))
>
> INHERITS (TSTESTING.ACCOUNT);
>
>ALTER TABLE TSTESTING.ACCOUNT_PART1 ADD CONSTRAINT ACCOUNT_PART1_PKEY
>PRIMARY KEY (ACCOUNT_ROW_INST);
>
>Perhaps I was not clear. The planner is excluding partitions which can
>not contain the rows looked up in the WHERE clause. However it is
>still scanning the parent table.
>
>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)

You can have a look at pg_partman. It makes setting up partitioning quite easy and provides a tool to easily move existing data from parent to child tables.

Jan