ablog

不器用で落着きのない技術者のメモ

PostgreSQL のクエリーリライトはコストベースではなく一定のルールでリライトされる

PostgreSQL のクエリーリライトはプラン(実行計画)を生成する前に一定のルールで書き換えられる。

リライタのエントリポイントは、pg_rewrite_queries() であり、クエリの木のリストをもらってクエリ木のリストを返す。pg_rewrite_queries() の中からリライトモジュールの QueryRewrite() を呼び出し、1つずつクエリ木を処理する。

PostgreSQL では、VIEW や RULE をクエリを書き換えることによって実装しています。 もし必要ならばこの段階でクエリを書き換えます。 ここでの処理はリライト処理と呼ばれ、リライト処理を行うモジュールをリライタ (rewriter) と呼びます。 リライト処理のエントリポイントは QueryRewrite (rewrite/rewriteHandler.c) です。

PostgreSQL の構造とソースツリー | Let's Postgres

ソースコードを確認する

ちょっと見てみただけ。

$ 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