Reprint: PostgreSQL Partitioning and optimization

--For partition table constraint_exclusion This parameter needs to be configured as partition or onpostgres=# show constraint_exclusion; Constraint_exclusion----------------------Partition--Create a parent-child table for storing partition data create TABLE t (ID int primary key); CREATE TABLE T 1 (like T including all) inherits (t), create TABLE T2 (like T including all) inherits (t), create table t3 (like t including all ) inherits (t); CREATE table t4 (like T including all) inherits (T), the constraints between--postgresql's child and child tables are not related, so there can be overlaps, that is, non-global constraints. ALTER TABLE t1 add constraint ck_t1_1 check (id<0); ALTER TABLE t2 add constraint ck_t2_1 check (id>=0 and id<100); ALTER TABLE t3 add constraint ck_t3_1 check (id>=100 and id<200);  ALTER TABLE T4 add constraint ck_t4_1 check (id>=200);                                 --The partition field is passed in constant, and the parent table and the constraint corresponding child table are scanned:p ostgres=# explain select * from t where id=10; QUERY PLAN----------------------------------------------------------------------------- Append (cost=0.00..8.17 rows=2 width=4), Seq Scan on t (cost=0.00..0.00 Rows=1 width=4) Filter: (id = ten), Index only Scan using T2_pkey on T2 (cost=0.15..8.17 ro Ws=1 width=4) Index Cond: (id = ten) (5 rows)--The partition field is passed in constant, the parent table and the child table corresponding to the constraint are scanned when executing; postgres=# prepare p_test as SELECT * FR Om t where id=$1;                                 preparepostgres=# explain execute p_test (1); QUERY PLAN-----------------------------------------------------------------------------    Append (cost=0.00..8.17 rows=2 width=4), Seq Scan on t (cost=0.00..0.00 Rows=1 width=4) Filter: (id = 1) Index only Scan using T2_pkey on T2 (cost=0.15..8.17 rows=1 width=4) index Cond: (id = 1) (5 rows)--clause query , the execution of the scan is the parent table and all child tables, note that the subquery used here is the query of the child table, should theoretically be the scan parent table and the child table postgres=# explain select * from T where id= (select id from T1 limit 1)                                 ; QUERY PLAN----------------------------------------------------------------------------- Append (cost=0.01.. 32.70 rows=5 width=4) Initplan 1 (returns $) (cost=0.00..0.01 Rows=1 width=4), Seq Sc A on T1 t1_1 (cost=0.00..34.00 rows=2400 width=4), Seq Scan on t (cost=0.00..0.00 Rows=1 width=4) filte   r: (id = $), index only Scan using T1_pkey on T1 (cost=0.15..8.17 rows=1 width=4) index Cond: (id = $) -Index only Scan using T2_pkey on T2 (cost=0.15..8.17 rows=1 width=4) index Cond: (id = $), Inde x only scan using T3_pkey on t3 (cost=0.15..8.17 rows=1 width=4) index Cond: (id = $), index only scan u Sing T4_pkey on t4 (cost=0.15..8.17 rows=1 width=4) Index Cond: (id = $)--the best thing to do when querying a partitioned table is to use literal constants rather than using sub- Queries such as complex sql--if a constraint is deleted on the child table, the PG has to add the child table of the delete constraint to the query (even if the child table can be ignored) ALTER TABLE T4 drop constraint ck_t4_1;postgres=# explain select                                    * from t where id=10; QUERY PLAN-----------------------------------------------------------------------------Append (cost=0.00..16.34 rows=3 width=4), Seq Scan on t (cost=0.00..0.00 Rows=1         width=4) Filter: (id = ten), Index only Scan using T2_pkey on T2 (cost=0.15..8.17 Rows=1 width=4) Index Cond: (id = ten), index only Scan using T4_pkey on t4 (cost=0.15..8.17 rows=1 width=4) index Cond: ( id = ten) (7 rows)--if constraint_exclusion is set to OFF,PG it has to perform a full table scan postgres=# set Constraint_exclusion=off;                                 setpostgres=# explain select * from t where id=10; QUERY PLAN----------------------------------------------------------------------------- Append (cost=0.00..32.69 rows=5 width=4), Seq Scan on t (cost=0.00..0.00 Rows=1 width=4) Filter: (id = 1 0) index only Scan using T1_pkey on T1 (cost=0.15..8.17 rows=1 width=4) index Cond: (id = ten)-I Ndex only Scan using T2_pkey on T2 (cost=0.15..8.17 rows=1 width=4) Index ConD: (id = ten), index only Scan using T3_pkey on t3 (cost=0.15..8.17 rows=1 width=4) index Cond: (id = 10) -Index only Scan using T4_pkey on t4 (cost=0.15..8.17 rows=1 width=4) index Cond: (id = ten) (rows)--partition table                                       Partitions are generally established relative to the partition index--the index built on the parent table is a global index, but if you do not have data for the table to query the child table, the partitioned table will be full table scan-The parent table establishes a global index postgres=# \d+ p              Table "PUBLIC.P" Column | Type | Modifiers | Storage | Stats Target | Description-----------+--------------------------------+-----------+---------+--------------+-------------City _id | Integer | NOT NULL |              Plain |  | LogTime | Timestamp (0) without time zone | NOT NULL |              Plain |  | Peaktemp |           Integer | |              Plain |  | Unitsales |           Integer | |              Plain | | Indexes: "idx_city_id" Btree (city_id) "Idx_p_logtime" Btree (logtime) Triggers:deleTe_p_trigger before DELETE on p for each ROW EXECUTE PROCEDURE p_delete_trigger () Insert_p_trigger before insert on p F              OR each ROW of EXECUTE PROCEDURE P_insert_trigger () child tables:p_201201, p_201202, p_201203,              p_201204, p_201205, p_201206, p_201207, p_201208, p_201209, p_201210, p_201211, p_201212, P_defaulthas oids:no--partition no cable                                          Citation, cannot use parent table index postgres=# explain select * from p_201202 where city_id=2 and Logtime=timestamp ' 2012-02-02 12:59:59 '; QUERY PLAN------------------------------------- ---------------------------------------------------------Seq Scan on p_201202 (cost=0.00..214.01 rows=2 width=20) Fil  ter: ((city_id = 2) and (LogTime = ' 2012-02-02 12:59:59 ':: Timestamp without time zone)) (2 rows)--To create a partitioned index, you can use a partitioned index postgres=# CREATE INDEX idx_p_201202_city_id on p_201202 (city_id);                                      CREATE indexpostgres=# Explain select * from p_201202 where city_id=2 and Logtime=timestamp ' 2012-02-02 12:59:59 '; QUERY PLAN------------------------------------------- -------------------------------------------Index Scan using idx_p_201202_city_id on p_201202 (cost=0.29..8.33 rows=2 WIDTH=20) Index Cond: (city_id = 2) Filter: (LogTime = ' 2012-02-02 12:59:59 ':: Timestamp without time zone)--you can also specify that only the parent table is queried Data postgres=# SELECT * from-only p; city_id | LogTime | Peaktemp | Unitsales---------+---------+----------+-----------(0 rows)--If a partitioned table no longer has an inheritance relationship between parent tables, the child table is no longer filtered when the parent table is queried postgres=# alter table t3 no inherit t;                            ALTER tablepostgres=# Explain select count (*) from T; QUERY PLAN------------------------------------------------------------------Aggregate (cost= 73.50..73.51 Rows=1 width=0)-Append (cost=0.00..62.80 rows=4281 width=0), SEQ scan on t (cost=0.00..0.00 Rows=1 width=0), SEQ scan on t1 (cost=0.00..31.40 rows=2 Width=0, Seq Scan on T2 (cost=0.00..31.40 rows=2140 width=0) (5 rows)--Add inheritance again, query parent table can filter to child table postgres=# alt      Er table t3 inherit t;                            ALTER tablepostgres=# Explain select count (*) from T; QUERY PLAN------------------------------------------------------------------Aggregate (cost= 110.25..110.26 Rows=1 width=0)-Append (cost=0.00..94.20 rows=6421 width=0), Seq Scan on t (cost=0 .00..0.00 rows=1 width=0), SEQ scan on t1 (cost=0.00..31.40 rows=2140 width=0), SEQ scan on t 2 (cost=0.00..31.40 rows=2140 width=0), Seq Scan on t3 (cost=0.00..31.40 rows=2140 width=0) (6 rows)--The following is the P table    Test data Code Create TABLE p (city_id int not NULL, logtime timestamp (0) is not NULL, Peaktemp int, Unitsales int); CREATE INDEXIdx_p_logtime on P (logtime); CREATE TABLE p_201201 (like P including all) INHERITS (p); CREATE TABLE p_201202 (like P including all) INHERITS (p); CREATE TABLE p_201203 (like P including all) INHERITS (p); CREATE TABLE p_201204 (like P including all) INHERITS (p); CREATE TABLE p_201205 (like P including all) INHERITS (p); CREATE TABLE p_201206 (like P including all) INHERITS (p); CREATE TABLE p_201207 (like P including all) INHERITS (p); CREATE TABLE p_201208 (like P including all) INHERITS (p); CREATE TABLE p_201209 (like P including all) INHERITS (p); CREATE TABLE p_201210 (like P including all) INHERITS (p); CREATE TABLE p_201211 (like P including all) INHERITS (p); CREATE TABLE p_201212 (like P including all) INHERITS (p); CREATE TABLE P_default (like P including all) INHERITS (p);  CREATE OR REPLACE FUNCTION p_insert_trigger () RETURNS trigger as $ $BEGIN IF (new.logtime >= DATE ' 2012-01-01 ' and    New.logtime < DATE ' 2012-02-01 ') then INSERT into p_201201 VALUES (new.*); ELSIF (new.logtime >= date ' 2012-02-01 ' and New.logtime < date ' 2012-03-01 ') then INSERT into p_201202 VALUES (    new.*); elsif (new.logtime >= date ' 2012-03-01 ' and New.logtime < date ' 2012-04-01 ') then INSERT into p_201203 VALU    ES (new.*); elsif (new.logtime >= date ' 2012-04-01 ' and New.logtime < date ' 2012-05-01 ') then INSERT into p_201204 VALU    ES (new.*); elsif (new.logtime >= date ' 2012-05-01 ' and New.logtime < date ' 2012-06-01 ') then INSERT into p_201205 VALU    ES (new.*); elsif (new.logtime >= date ' 2012-06-01 ' and New.logtime < date ' 2012-07-01 ') then INSERT into p_201206 VALU    ES (new.*); elsif (new.logtime >= date ' 2012-07-01 ' and New.logtime < date ' 2012-08-01 ') then INSERT into p_201207 VALU    ES (new.*); elsif (new.logtime >= date ' 2012-08-01 ' and New.logtime < date ' 2012-09-01 ') then INSERT into p_201208 VALU    ES (new.*); elsif (new.logtime >= DATE ' 2012-09-01 ' AND New.logtime < DATE ' 2012-10-01 ') then INSERT into p_201209 VALUES (new.*); elsif (new.logtime >= date ' 2012-10-01 ' and New.logtime < date ' 2012-11-01 ') then INSERT into p_201210 VALU    ES (new.*); elsif (new.logtime >= date ' 2012-11-01 ' and New.logtime < date ' 2012-12-01 ') then INSERT into p_201211 VALU    ES (new.*); elsif (new.logtime >= date ' 2012-12-01 ' and New.logtime < date ' 2013-01-01 ') then INSERT into p_201212 VALU    ES (new.*); elsif (new.logtime >= date ' 2013-01-01 ' OR new.logtime < date ' 2012-01-01 ') then INSERT into P_default VALU    ES (new.*);  ELSE RAISE EXCEPTION ' Date out of range.    Fix the P_insert_trigger () function! ';    END IF; RETURN NULL; end;$$ LANGUAGE Plpgsql;  CREATE OR REPLACE FUNCTION p_delete_trigger () RETURNS trigger as $ $BEGIN IF (old.logtime >= DATE ' 2012-01-01 ' and   Old.logtime < DATE ' 2012-02-01 ') then DELETE from p_201201 WHERE logtime=old.logtime; elsif (old.logtime >= date ' 2012-02-01 ' and Old.logtime < date ' 2012-03-01 ') then DELETE from p_201202 WHE    RE Logtime=old.logtime; elsif (old.logtime >= date ' 2012-03-01 ' and Old.logtime < date ' 2012-04-01 ') then DELETE from p_201203 wher    E Logtime=old.logtime; elsif (old.logtime >= date ' 2012-04-01 ' and Old.logtime < date ' 2012-05-01 ') then DELETE from p_201204 wher    E Logtime=old.logtime; elsif (old.logtime >= date ' 2012-05-01 ' and Old.logtime < date ' 2012-06-01 ') then DELETE from p_201205 wher    E Logtime=old.logtime; elsif (old.logtime >= date ' 2012-06-01 ' and Old.logtime < date ' 2012-07-01 ') then DELETE from p_201206 wher    E Logtime=old.logtime; elsif (old.logtime >= date ' 2012-07-01 ' and Old.logtime < date ' 2012-08-01 ') then DELETE from p_201207 wher    E Logtime=old.logtime; elsif (old.logtime >= date ' 2012-08-01 ' and Old.logtime < date ' 2012-09-01 ') then DELETE from P_201208 WHERE Logtime=old.logtime; elsif (old.logtime >= date ' 2012-09-01 ' and Old.logtime < date ' 2012-10-01 ') then DELETE from p_201209 wher    E Logtime=old.logtime; elsif (old.logtime >= date ' 2012-10-01 ' and Old.logtime < date ' 2012-11-01 ') then DELETE from p_201210 wher    E Logtime=old.logtime; elsif (old.logtime >= date ' 2012-11-01 ' and Old.logtime < date ' 2012-12-01 ') then DELETE from p_201211 wher    E Logtime=old.logtime; elsif (old.logtime >= date ' 2012-12-01 ' and Old.logtime < date ' 2013-01-01 ') then DELETE from p_201212 wher    E Logtime=old.logtime; elsif (old.logtime >= date ' 2013-01-01 ' OR old.logtime < date ' 2012-01-01 ') then DELETE from P_default wher    E Logtime=old.logtime;  ELSE RAISE EXCEPTION ' Date out of range.    Fix the P_insert_trigger () function! ';    END IF; RETURN NULL; end;$$ LANGUAGE Plpgsql; CREATE TRIGGER Insert_p_trigger before insert on p for each ROW EXECUTE proceduRE P_insert_trigger (); CREATE TRIGGER Delete_p_trigger before delete on p for each ROW EXECUTE PROCEDURE p_delete_trigger (), INSERT into P ( city_id, LogTime, Peaktemp, Unitsales) VALUES (1, timestamp ' 2012-01-02 12:59:59 ', +); INSERT into P (city_id, LogTime , Peaktemp, Unitsales) VALUES (2, timestamp ' 2012-02-02 12:59:59 ', +); INSERT into P (city_id, LogTime, peaktemp, unit Sales) VALUES (3, timestamp ' 2012-03-02 12:59:59 ', +); INSERT into P (city_id, LogTime, peaktemp, Unitsales) VALUES (4 , timestamp ' 2012-04-02 12:59:59 ', ', ', ') INSERT into P (city_id, LogTime, Peaktemp, Unitsales) VALUES (5, timestamp ' 201 2-05-02 12:59:59 ', (+); INSERT into P (city_id, LogTime, Peaktemp, Unitsales) VALUES (6, timestamp ' 2012-06-02 12:59:59 INSERT into P (city_id, LogTime, Peaktemp, Unitsales) VALUES (7, timestamp ' 2012-07-02 12:59:59 ', +); Inser T into P (city_id, LogTime, Peaktemp, Unitsales) VALUES (8, timestamp ' 2012-08-02 12:59:59 ', +); INSERT into P (city_i D LogTime, Peaktemp, Unitsales) VALUES (9, timestamp ' 2012-09-02 12:59:59 ', +); INSERT into P (city_id, LogTime, Peakte MP, Unitsales) VALUES (ten, timestamp ' 2012-10-02 12:59:59 ', +); INSERT into P (city_id, LogTime, Peaktemp, Unitsales) VALUES (one, timestamp ' 2012-11-02 12:59:59 ', +); INSERT into P (city_id, LogTime, peaktemp, Unitsales) VALUES (Tim Estamp ' 2012-12-02 12:59:59 ', + (+); INSERT into P (city_id, LogTime, Peaktemp, unitsales) VALUES (timestamp ' 2013-01 -02 12:59:59 ', (+), INSERT into P (city_id, LogTime, Peaktemp, unitsales) VALUES (timestamp ' 2011-12-02 12:59:59 '), INSERT into P (city_id, LogTime, Peaktemp, unitsales) Select M, timestamp ' 2012-02-02 12:59:59 ', + from Gener Ate_series (1,10000) m;explain select * from p_201202 where city_id=2 and Logtime=timestamp ' 2012-02-02 12:59:59 ';

Reprint: https://yq.aliyun.com/articles/2637?spm=5176.100240.searchblog.12.59Jibq#

Reprint: PostgreSQL Partitioning and optimization