Hi all: I made partition tables: postgres=# create table ptest(id integer, name varchar(20)); CREATE TABLE postgres=# create table ctest01(CHECK(id<5000000)) inherits (ptest); CREATE TABLE postgres=# create table ctest02(CHECK(id>=5000000)) inherits (ptest); CREATE TABLE postgres=# postgres=# create index on ctest01(id); CREATE INDEX postgres=# create index on ctest02(id); CREATE INDEX postgres=# postgres=#
postgres=# CREATE OR REPLACE FUNCTION ptest_insert_trigger() RETURNS TRIGGER AS $$ postgres$# postgres$# BEGIN postgres$# postgres$# IF ( NEW.id <5000000 ) THEN postgres$# INSERT INTO ctest01 VALUES (NEW.*); postgres$# ELSIF ( NEW.id >= 5000000 ) THEN postgres$# INSERT INTO ctest02 VALUES (NEW.*); postgres$# ELSE postgres$# RAISE EXCEPTION 'Error while inserting data'; postgres$# END IF; postgres$# postgres$# RETURN NULL; postgres$# END; $$ LANGUAGE plpgsql; CREATE FUNCTION postgres=# postgres=# CREATE TRIGGER insert_ptest_trigger BEFORE INSERT ON ptest FOR EACH ROW postgres-# EXECUTE PROCEDURE ptest_insert_trigger(); CREATE TRIGGER postgres=# And when executing sql statement , I got the following plan: postgres=# explain select * from ptest where id=5000 or id=6000000; QUERY PLAN ----------------------------------------------------------------------------------------------- Result (cost=0.00..54.93 rows=5 width=20) -> Append (cost=0.00..54.93 rows=5 width=20) -> Seq Scan on ptest (cost=0.00..0.00 rows=1 width=62) Filter: ((id = 5000) OR (id = 6000000)) -> Bitmap Heap Scan on ctest01 ptest (cost=19.49..27.46 rows=2 width=9) Recheck Cond: ((id = 5000) OR (id = 6000000)) -> BitmapOr (cost=19.49..19.49 rows=2 width=0) -> Bitmap Index Scan on ctest01_id_idx (cost=0.00..9.74 rows=1 width=0) Index Cond: (id = 5000) -> Bitmap Index Scan on ctest01_id_idx (cost=0.00..9.74 rows=1 width=0) Index Cond: (id = 6000000) -> Bitmap Heap Scan on ctest02 ptest (cost=19.49..27.46 rows=2 width=9) Recheck Cond: ((id = 5000) OR (id = 6000000)) -> BitmapOr (cost=19.49..19.49 rows=2 width=0) -> Bitmap Index Scan on ctest02_id_idx (cost=0.00..9.74 rows=1 width=0) Index Cond: (id = 5000) -> Bitmap Index Scan on ctest02_id_idx (cost=0.00..9.74 rows=1 width=0) Index Cond: (id = 6000000) (18 rows) postgres=# The selection used where condition for every partition table, which is not what I want. my rule is just for id column value. And my select sql statement's where condition is also for id column value. Is there any method to let the database to realize my rule of parent table when creating execution plan? Thanks in advance