Am 28. Mai 2015 18:25:42 MESZ, schrieb Ravi Krishna <sravikrish...@gmail.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
