PostgreSQL のクエリーリライトはプラン(実行計画)を生成する前に一定のルールで書き換えられる。
リライタのエントリポイントは、pg_rewrite_queries() であり、クエリの木のリストをもらってクエリ木のリストを返す。pg_rewrite_queries() の中からリライトモジュールの QueryRewrite() を呼び出し、1つずつクエリ木を処理する。
PostgreSQL では、VIEW や RULE をクエリを書き換えることによって実装しています。 もし必要ならばこの段階でクエリを書き換えます。 ここでの処理はリライト処理と呼ばれ、リライト処理を行うモジュールをリライタ (rewriter) と呼びます。 リライト処理のエントリポイントは QueryRewrite (rewrite/rewriteHandler.c) です。
PostgreSQL の構造とソースツリー | Let's Postgres
ソースコードを確認する
ちょっと見てみただけ。
- [https://www.postgresql.org/ftp/source/v10.4/ から postgresql-10.4.tar.bz2 をダウンロードする。
- 解凍する
$ tar xfvJ postgresql-10.4.tar.bz2
- src/backend/rewrite/rewriteHandler.c
/*------------------------------------------------------------------------- * * rewriteHandler.c * Primary module of query rewriter. * * Portions Copyright (c) 1996-2017, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION * src/backend/rewrite/rewriteHandler.c * * NOTES * Some of the terms used in this file are of historic nature: "retrieve" * was the PostQUEL keyword for what today is SELECT. "RIR" stands for * "Retrieve-Instead-Retrieve", that is an ON SELECT DO INSTEAD SELECT rule * (which has to be unconditional and where only one rule can exist on each * relation). * *------------------------------------------------------------------------- */ #include "postgres.h" #include "access/sysattr.h" #include "catalog/dependency.h" #include "catalog/pg_type.h" #include "commands/trigger.h" #include "foreign/fdwapi.h" #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" #include "parser/analyze.h" #include "parser/parse_coerce.h" #include "parser/parsetree.h" #include "rewrite/rewriteDefine.h" #include "rewrite/rewriteHandler.h" #include "rewrite/rewriteManip.h" #include "rewrite/rowsecurity.h" #include "utils/builtins.h" #include "utils/lsyscache.h" #include "utils/rel.h" /* We use a list of these to detect recursion in RewriteQuery */ typedef struct rewrite_event { Oid relation; /* OID of relation having rules */ CmdType event; /* type of rule being fired */ } rewrite_event; typedef struct acquireLocksOnSubLinks_context { bool for_execute; /* AcquireRewriteLocks' forExecute param */ } acquireLocksOnSubLinks_context; static bool acquireLocksOnSubLinks(Node *node, acquireLocksOnSubLinks_context *context); static Query *rewriteRuleAction(Query *parsetree, Query *rule_action, Node *rule_qual, int rt_index, CmdType event, bool *returning_flag); static List *adjustJoinTreeList(Query *parsetree, bool removert, int rt_index); static List *rewriteTargetListIU(List *targetList, CmdType commandType, OverridingKind override, Relation target_relation, int result_rti, List **attrno_list); static TargetEntry *process_matched_tle(TargetEntry *src_tle, TargetEntry *prior_tle, const char *attrName); static Node *get_assignment_input(Node *node); static void rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation, List *attrnos); static void markQueryForLocking(Query *qry, Node *jtnode, LockClauseStrength strength, LockWaitPolicy waitPolicy, bool pushedDown); static List *matchLocks(CmdType event, RuleLock *rulelocks, int varno, Query *parsetree, bool *hasUpdate); static Query *fireRIRrules(Query *parsetree, List *activeRIRs, bool forUpdatePushedDown); static bool view_has_instead_trigger(Relation view, CmdType event); static Bitmapset *adjust_view_column_set(Bitmapset *cols, List *targetlist); (以下略)
- src/include/rewrite/rewriteDefine.h
/*------------------------------------------------------------------------- * * rewriteHandler.h * External interface to query rewriter. * * * Portions Copyright (c) 1996-2017, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * * src/include/rewrite/rewriteHandler.h * *------------------------------------------------------------------------- */ #ifndef REWRITEHANDLER_H #define REWRITEHANDLER_H #include "utils/relcache.h" #include "nodes/parsenodes.h" extern List *QueryRewrite(Query *parsetree); extern void AcquireRewriteLocks(Query *parsetree, bool forExecute, bool forUpdatePushedDown); extern Node *build_column_default(Relation rel, int attrno); extern void rewriteTargetListUD(Query *parsetree, RangeTblEntry *target_rte, Relation target_relation); extern Query *get_view_query(Relation view); extern const char *view_query_is_auto_updatable(Query *viewquery, bool check_cols); extern int relation_is_updatable(Oid reloid, bool include_triggers, Bitmapset *include_cols) #endif /* REWRITEHANDLER_H */
検証
テーブルの件数に関わらずビューの外で指定したフィルタ句がビューの中にプッシュダウンされることを確認した。
- EC2 に psql と pgbench をインストールする。
$ sudo yum -y install postgresql $ sudo yum -y install postgresql-contrib
- データベースのセットアップ(テーブル作成、データ投入)
$ pgbench -i -s 100 -U awsuser -h ******.******.ap-northeast-1.rds.amazonaws.com -d mydb
- 統計情報を確認する
$ psql "host=******.*******.ap-northeast-1.rds.amazonaws.com user=awsuser dbname=mydb port=5432" select attname, n_distinct, most_common_vals from pg_stats where tablename = 'pgbench_accounts'; postgres-m4xl awsuser 16:44 => analyze pgbench_accounts; ANALYZE Time: 6832.807 ms
- ビューを作成する
postgres-m4xl awsuser 16:46 => create view v_pgbench_accounts as select * from pgbench_accounts; CREATE VIEW Time: 3.670 ms
- 統計情報を確認する。
postgres-m4xl awsuser 16:46 => \x Expanded display is on. postgres-m4xl awsuser 16:46 => select * from pg_stat_user_tables where relname = 'pgbench_accounts'; -[ RECORD 1 ]-------+------------------------------ relid | 16408 schemaname | public relname | pgbench_accounts seq_scan | 2 seq_tup_read | 100000010 idx_scan | 6 idx_tup_fetch | 3 n_tup_ins | 100000000 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 100000045 n_dead_tup | 0 n_mod_since_analyze | 0 last_vacuum | 2018-09-20 06:57:49.009272+00 last_autovacuum | last_analyze | 2018-09-24 16:45:24.449195+00 last_autoanalyze | vacuum_count | 1 autovacuum_count | 0 analyze_count | 2 autoanalyze_count | 0 Time: 12.534 ms
- 出力結果の表示を元に戻す。
postgres-m4xl awsuser 16:47 => \x Expanded display is off.
- テーブルにクエリを実行する。
postgres-m4xl awsuser 16:47 => explain analyze select * from pgbench_accounts where aid = 1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.57..8.59 rows=1 width=97) (actual time=0.038..0.039 rows=1 loops=1) Index Cond: (aid = 1) Planning time: 0.125 ms Execution time: 0.064 ms (4 rows) Time: 0.704 ms
- ビューにクエリを実行する。
postgres-m4xl awsuser 16:47 => explain analyze select * from v_pgbench_accounts where aid = 1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.57..8.59 rows=1 width=97) (actual time=0.009..0.010 rows=1 loops=1) Index Cond: (aid = 1) Planning time: 0.058 ms Execution time: 0.032 ms (4 rows) Time: 0.605 ms
- truncate する。
postgres-m4xl awsuser 16:47 => truncate table pgbench_accounts; TRUNCATE TABLE Time: 1262.388 ms
- 統計情報を収集する。
postgres-m4xl awsuser 16:49 => analyze pgbench_accounts; ANALYZE Time: 0.538 ms postgres-m4xl awsuser 16:50 => \x Expanded display is on.
- 統計情報を確認する。
postgres-m4xl awsuser 16:52 => select * from pg_stat_user_tables where relname = 'pgbench_accounts'; -[ RECORD 1 ]-------+------------------------------ relid | 16408 schemaname | public relname | pgbench_accounts seq_scan | 5 seq_tup_read | 100000010 idx_scan | 9 idx_tup_fetch | 6 n_tup_ins | 100000000 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 0 n_dead_tup | 0 n_mod_since_analyze | 0 last_vacuum | 2018-09-20 06:57:49.009272+00 last_autovacuum | last_analyze | 2018-09-24 16:50:48.387548+00 last_autoanalyze | vacuum_count | 1 autovacuum_count | 0 analyze_count | 3 autoanalyze_count | 0 Time: 11.523 ms
- 表示を元に戻す。
postgres-m4xl awsuser 16:55 => \x Expanded display is off.
- テーブルを参照する
postgres-m4xl awsuser 16:55 => explain analyze select * from pgbench_accounts where aid = 1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.15..8.17 rows=1 width=97) (actual time=0.005..0.005 rows=0 loops=1) Index Cond: (aid = 1) Planning time: 0.132 ms Execution time: 0.026 ms (4 rows) Time: 0.623 ms
- ビューを参照する。
postgres-m4xl awsuser 16:56 => explain analyze select * from v_pgbench_accounts where aid = 1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.15..8.17 rows=1 width=97) (actual time=0.004..0.004 rows=0 loops=1) Index Cond: (aid = 1) Planning time: 0.058 ms Execution time: 0.019 ms (4 rows) Time: 0.490 ms