Can dml realize the partition table's rule and make good execution plan? - Mailing list pgsql-general
| From | 高健 |
|---|---|
| Subject | Can dml realize the partition table's rule and make good execution plan? |
| Date | |
| Msg-id | CAL454F1we0J3pTKSrGfK-CiDG_NAVPzePmZE1CqdAnhYzRkzpw@mail.gmail.com Whole thread Raw |
| Responses |
Re: Can dml realize the partition table's rule and make
good execution plan?
(Craig Ringer <craig@2ndQuadrant.com>)
Re: Can dml realize the partition table's rule and make good execution plan? (Craig Ringer <craig@2ndQuadrant.com>) |
| List | pgsql-general |
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
pgsql-general by date: