批量创建分区表、索引

2019独角兽企业重金招聘Python工程师标准>>>

主表loan_data，按月分区loan_data_201701,索引建在分区表上  

1、先创建好主表 loan_data

2、 执行下面的语句

-- Function: public.create_loan_data_tables()
-- DROP FUNCTION public.create_loan_data_tables();
CREATE OR REPLACE FUNCTION public.create_loan_data_tables()
  RETURNS integer AS
$BODY$
DECLARE
    count int;
    tbname text;
    types text[];
    yearval text;
    codes text[];
    code text;
    checktext text;
BEGIN
    count := 0;
    types := array['2017', '2018', '2019', '2020', '2021', '2022', '2023', '2024', '2025', '2026', '2027', '2028', '2029', '2030', '2031', '2032', '2033', '2034', '2035', '2036'];
    codes := array['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'];
FOREACH yearval IN ARRAY types LOOP
    FOREACH code IN ARRAY codes LOOP
        tbname = 'loan_data_' || yearval || '' || code;
        checktext = ''|| yearval || '' || code;
            EXECUTE 'create table '||tbname ||'(
            check(year_month='||quote_literal(checktext)||')
            ) INHERITS (da_loan_data)';
            EXECUTE 'create index inx_'||tbname||'_loan_type on '||tbname||'(loan_type)';
            EXECUTE 'create index inx_'||tbname||'_date_loan on '||tbname||'(date_loan)';
            count := count + 1;
    END LOOP;
END LOOP;
RETURN count;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.create_loan_data_tables()
  OWNER TO postgres;

3、创建触发器函数，插入数据时，插入到不同的分区（rule这么高效不用它是因为我的分区表很多，不可能一个一个条件判断写）

-- Function: public.insert_before_to_partition()
-- DROP FUNCTION public.insert_before_to_partition();
CREATE OR REPLACE FUNCTION public.insert_before_to_partition()
  RETURNS trigger AS
$BODY$
BEGIN
 EXECUTE format('INSERT INTO %I SELECT ($1).*', TG_TABLE_NAME||'_'||NEW.year_month)
 USING NEW;
 RETURN NULL;
 EXCEPTION WHEN UNDEFINED_TABLE THEN
 RAISE EXCEPTION 'year_month out of range. please create table or check the data %ERROR:  % %SQL 状态: %',chr(10),SQLERRM,chr(10),SQLSTATE; 
 WHEN OTHERS THEN
 RAISE EXCEPTION '% %SQL 状态: %', SQLERRM,chr(10),SQLSTATE;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.insert_before_to_partition()
  OWNER TO postgres;

  240个分区表，一个函数就解决了，是不是太简单了

4、为主表创建触发器

CREATE TRIGGER tr_bf_insert_loan_data
  BEFORE INSERT
  ON public.loan_data
  FOR EACH ROW
  EXECUTE PROCEDURE public.insert_before_to_partition();

5、测试分区查询（查询条件一定要加上分区哦，分区支持 in/between/=/）

  explain analyze 查询语句;   

"Append  (cost=0.00..24.51 rows=4 width=784) (actual time=0.047..0.047 rows=0 loops=1)"
"  Buffers: shared hit=3"
"  ->  Seq Scan on public.loan_data  (cost=0.00..0.00 rows=1 width=784) (actual time=0.001..0.001 rows=0 loops=1)"
"        Output: loan_data.loan_data_id, loan_data.apply_no, loan_data.policy_no, loan_data.data_source, loan_data.product_code, loan_data.product_name, loan_data.loan_code, loan_data.loan_name, loan_data.loan_amt, loan_data (...)"
"        Filter: ((loan_data.year_month = ANY ('{201703,201701,201702}'::text[])) AND (loan_data.date_loan > to_date('2017-01-02'::text, 'yyyy-MM-dd'::text)) AND (loan_data.date_loan < to_date('2017-03-29'::text, 'yyyy-MM-dd'::text)))"
"  ->  Index Scan using inx_loan_data_201701_date_loan on public.loan_data_201701  (cost=0.15..8.17 rows=1 width=784) (actual time=0.029..0.029 rows=0 loops=1)"
"        Output: loan_data_201701.loan_data_id, loan_data_201701.apply_no, loan_data_201701.policy_no, loan_data_201701.data_source, loan_data_201701.product_code, loan_data_201701.product_name, loan_data_201701.loan_code, da_loan_da (...)"
"        Index Cond: ((loan_data_201701.date_loan > to_date('2017-01-02'::text, 'yyyy-MM-dd'::text)) AND (loan_data_201701.date_loan < to_date('2017-03-29'::text, 'yyyy-MM-dd'::text)))"
"        Filter: (loan_data_201701.year_month = ANY ('{201703,201701,201702}'::text[]))"
"        Buffers: shared hit=1"
"  ->  Index Scan using inx_loan_data_201702_date_loan on public.loan_data_201702  (cost=0.15..8.17 rows=1 width=784) (actual time=0.007..0.007 rows=0 loops=1)"
"        Output: loan_data_201702.loan_data_id, loan_data_201702.apply_no, loan_data_201702.policy_no, loan_data_201702.data_source, loan_data_201702.product_code, loan_data_201702.product_name, loan_data_201702.loan_code, da_loan_da (...)"
"        Index Cond: ((loan_data_201702.date_loan > to_date('2017-01-02'::text, 'yyyy-MM-dd'::text)) AND (loan_data_201702.date_loan < to_date('2017-03-29'::text, 'yyyy-MM-dd'::text)))"
"        Filter: (loan_data_201702.year_month = ANY ('{201703,201701,201702}'::text[]))"
"        Buffers: shared hit=1"
"  ->  Index Scan using inx_loan_data_201703_date_loan on public.loan_data_201703  (cost=0.15..8.17 rows=1 width=784) (actual time=0.005..0.005 rows=0 loops=1)"
"        Output: loan_data_201703.loan_data_id, loan_data_201703.apply_no, loan_data_201703.policy_no, loan_data_201703.data_source, loan_data_201703.product_code, loan_data_201703.product_name, loan_data_201703.loan_code, da_loan_da (...)"
"        Index Cond: ((loan_data_201703.date_loan > to_date('2017-01-02'::text, 'yyyy-MM-dd'::text)) AND (loan_data_201703.date_loan < to_date('2017-03-29'::text, 'yyyy-MM-dd'::text)))"
"        Filter: (loan_data_201703.year_month = ANY ('{201703,201701,201702}'::text[]))"
"        Buffers: shared hit=1"
"Planning time: 17.897 ms"
"Execution time: 0.197 ms"

很直观吧，postgreSQL的查询计划入门比oracle的查询计划要简单很多

转载于:https://my.oschina.net/duo8523/blog/821974