Re: Partitioning and performance

On Thu, May 28, 2015 at 12:50 PM, Tom Lane <tgl@sss.pgh.pa.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.