From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Ravi Krishna <sravikrishna3(at)gmail(dot)com> |
Cc: | Jan Lentfer <Jan(dot)Lentfer(at)web(dot)de>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Partitioning and performance |
Date: | 2015-05-28 16:42:23 |
Message-ID: | CANu8FixLUZJ9fT4mLrQQba6iKreNBP43JiDwyEAw_bXUcWEOOg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Generally, when you partition, data should only be in child tables, and the
parent table should be empty, otherwise you defeat the purpose of
parttioning.`
On Thu, May 28, 2015 at 12:25 PM, Ravi Krishna <sravikrishna3(at)gmail(dot)com>
wrote:
> > 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)
>
>
> --
> 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
>
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Lentfer | 2015-05-28 16:49:40 | Re: Partitioning and performance |
Previous Message | Ravi Krishna | 2015-05-28 16:25:42 | Re: Partitioning and performance |