Postgres Inheritance based partition scanning all the partitions

Question

I wanted to implement partitioning via inheritance in Postgres. I implemented the below steps by referring to the Postgres article :-

Created a master table called "test_table"

CREATE TABLE kirana_customer.test_table
(
    col1 bigint NOT NULL DEFAULT nextval('kirana_customer."testTable_col1_seq"'::regclass),
    col2 bigint NOT NULL,
    col3 integer,
    CONSTRAINT "testTable_pkey" PRIMARY KEY (col1)
)

Created the child/inherited table

CREATE TABLE kirana_customer.test_table_1
(
    -- Inherited from table kirana_customer.test_table: col1 bigint NOT NULL DEFAULT nextval('kirana_customer."testTable_col1_seq"'::regclass),
    -- Inherited from table kirana_customer.test_table: col2 bigint NOT NULL,
    -- Inherited from table kirana_customer.test_table: col3 integer,
    CONSTRAINT check_col3 CHECK (col3 = 1)
)
    INHERITS (kirana_customer.test_table)

Attached a "BEFORE INSERT" trigger to the master table for inserting data based on column "col3" to the correct partition table

DECLARE

    v_col3 bigint;
BEGIN
    v_col3 := NEW.col3;
    
    
    EXECUTE 'INSERT INTO kirana_customer.test_table_'||v_col3||' VALUES ($1.*)' USING NEW;

RETURN NULL;
END;

After completing all these steps i am able to insert my entries into the correct partition , but while analysing the select statements i found that Postgres is scanning all the partitions

explain select * from  kirana_customer.test_table where col3 = 1

This gives the below output

"Append  (cost=0.00..34.42 rows=9 width=20)"
"  ->  Seq Scan on test_table  (cost=0.00..3.12 rows=1 width=20)"
"        Filter: (col3 = 1)"
"  ->  Seq Scan on test_table_1  (cost=0.00..31.25 rows=8 width=20)"
"        Filter: (col3 = 1)"

So, did I miss something? Or is this the way Postgres partitioning works?

"*I wanted to implement partitioning via inheritance*" - why? With Postres 12 you should be using declarative partitioning. Much more efficient and much better integrated into the query optimizer — , Aug 26 '21 at 12:52
Declarative partition wasn't allowing me to create partition if my partition key ins't a primary key or a composite primary key . I wasn't also able to use foreign keys . So , i chose to use inheritance base partitioning instead — animo3991, Aug 26 '21 at 13:28
You can't use foreign keys with inheritance based partitioning either. And you can't really have a proper primary key across all inherited tables as well. — , Aug 26 '21 at 13:29
But i still can use a partition key which isn't a PK or a composite PK . I know declarative partitioning is new and quite easy to work with . But , shouldn't inheritance based partitioning work as well ? — animo3991, Aug 26 '21 at 13:32
Inheritance based partitioning always was a poor man's partitioning implementation. Not well integrated into the query optimizer and it never worked as good as it should be. True, you might be able to use a partition key that is not part of the PK - but the PK itself isn't really a (global) PK to begin with. — , Aug 26 '21 at 13:36

score 3 · Accepted Answer · answered Aug 26 '21 at 15:46

You can't really draw conclusions with a sample size of 1. And you only have one child table.

There is no constraint that the root table cannot contain rows where col3=1, so it needs to be scanned. Scanning an empty able is not a big deal, but if you did want to avoid it you could add a constraint:

alter table kirana_customer.test_table add constraint whatever
   check (col3 is null) no inherit;

Also, your reason for not wanting to use declarative partitioning doesn't make any sense. Maybe you should ask a question with an example about whatever misconception you have about that.

After adding the constraint , it's not scanning the parent table . Thanks !!! — animo3991, Aug 26 '21 at 18:17

score 1 · Answer 2 · answered Aug 26 '21 at 15:42

1

You have to set constraint_exclusion to on or partition for that to work.

answered Aug 26 '21 at 15:42

Laurenz Albe

209,280
17
206
263

constraint_exclusion is already set to partition . It stopped scanning the parent table , once i put a constraint as explained in the previous answer . Anyways , thanks for your help !! – animo3991 Aug 26 '21 at 18:19

Postgres Inheritance based partition scanning all the partitions

2 Answers2