From: | Ravi Krishna <sravikrishna3(at)gmail(dot)com> |
---|---|
To: | Jan Lentfer <Jan(dot)Lentfer(at)web(dot)de> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Partitioning and performance |
Date: | 2015-05-28 16:25:42 |
Message-ID: | CACER=P1tywffzivJg=JxTpkW-6Hc593KWo6L7v7dy7FKXu2kSQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Melvin Davidson | 2015-05-28 16:42:23 | Re: Partitioning and performance |
Previous Message | Jan Lentfer | 2015-05-28 16:21:38 | Re: Partitioning and performance |