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