1、用一个简单的表，让它的执行阶段不要花费太久

postgres=# \d t                 Table "public.t" Column |  Type   | Collation | Nullable | Default --------+---------+-----------+----------+--------- a      | integer |           |          |

简单的SQL语句

select a from t;

2、测试，9k多一点

quanzl-mac:bin quanzl$ ./pgbench -f select1.sql postgres -T 10 -ntransaction type: select1.sqlscaling factor: 1query mode: simplenumber of clients: 1number of threads: 1duration: 10 snumber of transactions actually processed: 91537latency average = 0.109 mstps = 9151.154970 (including connections establishing)tps = 9153.441420 (excluding connections establishing)quanzl-mac:bin quanzl$ ./pgbench -f select1.sql postgres -T 10 -ntransaction type: select1.sqlscaling factor: 1query mode: simplenumber of clients: 1number of threads: 1duration: 10 snumber of transactions actually processed: 91661latency average = 0.109 mstps = 9162.928603 (including connections establishing)tps = 9165.530320 (excluding connections establishing)

3、修改代码，因为只是验证想法，不妨简单一些

src/backend/tcop/postgres.c 的函数 List *pg_parse_query(const char *query_string)

static List	   *sel_parsetree_list = NIL;。。。// 如果已经分析过，直接复制一份返回	if (strcmp("select a from t;", query_string)==0 && sel_parsetree_list!=NIL)		return copyObject(sel_parsetree_list);。。。// 如果是第一次调用，将查询树暂存	if (strcmp("select a from t;", query_string)==0 && sel_parsetree_list==NIL)	{		MemoryContext oldcontext = MemoryContextSwitchTo(TopMemoryContext);		sel_parsetree_list = copyObject(raw_parsetree_list);		MemoryContextSwitchTo(oldcontext);	}

4、测试变化

quanzl-mac:bin quanzl$ ./pgbench -f select1.sql postgres -T 10 -ntransaction type: select1.sqlscaling factor: 1query mode: simplenumber of clients: 1number of threads: 1duration: 10 snumber of transactions actually processed: 94443latency average = 0.106 mstps = 9443.693083 (including connections establishing)tps = 9446.186718 (excluding connections establishing)quanzl-mac:bin quanzl$ ./pgbench -f select1.sql postgres -T 10 -ntransaction type: select1.sqlscaling factor: 1query mode: simplenumber of clients: 1number of threads: 1duration: 10 snumber of transactions actually processed: 94921latency average = 0.105 mstps = 9489.780463 (including connections establishing)tps = 9492.466957 (excluding connections establishing)

5、可以看到，有明显的变化，方案可行

这个只是验证，实际实现要复杂得多。如果对执行计划做类似改动，相信会有更大惊喜。

补充：

对计划树的缓存对比，惊喜的表现

quanzl-mac:bin quanzl$ ./pgbench -f select1.sql flying -T 10 -ntransaction type: select1.sqlscaling factor: 1query mode: simplenumber of clients: 1number of threads: 1duration: 10 snumber of transactions actually processed: 115574latency average = 0.087 mstps = 11556.180953 (including connections establishing)tps = 11559.223205 (excluding connections establishing)quanzl-mac:bin quanzl$ ./pgbench -f select1.sql flying -T 10 -ntransaction type: select1.sqlscaling factor: 1query mode: simplenumber of clients: 1number of threads: 1duration: 10 snumber of transactions actually processed: 141918latency average = 0.070 mstps = 14191.319752 (including connections establishing)tps = 14196.134852 (excluding connections establishing)

---------------------------------------------------