Re: Partitioning and performance

On Thu, May 28, 2015 at 12:50 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Sure, because you don't have a constraint forbidding the parent from
> having a matching row, no?

As suggested by you, I included a bogus condition in the parent table
which will prevent any row addition in the parent table and made the
constraint NO INHERIT.

i run this

SET constraint_exclusion = on;
explain select * from tstesting.account where account_row_inst = 1001 ;

Append (cost=0.14..8.16 rows=1 width=832)
-> Index Scan using account_part1_pkey on account_part1
(cost=0.14..8.16 rows=1 width=832)
Index Cond: (account_row_inst = 1001)
(3 rows)

The planner shows this for the non partitioned table

Index Scan using account_pkey on account (cost=0.14..8.16 rows=1 width=832)
Index Cond: (account_row_inst = 1001)
(2 rows)

So cost wise they both look same, still when i run the sql in a loop
in large numbers, it takes rougly 1.8 to 2 times more than non
partitioned table.