PostgreSQL pg 的SQL优化相关工具和方法

最新推荐文章于 2024-06-04 18:01:04 发布
最新推荐文章于 2024-06-04 18:01:04 发布
阅读量1.8k 收藏 2
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u010024893/article/details/103903768
版权

SQL优化通常是较为复杂的工作，需要很多SQL语法知识和优化器相关知识。但是，相关分析诊断工具也非常重要。掌握了这些工具，也非常有助于提升SQL优化器相关技术知识。

SQL优化的主要工具通常是访问计划、统计信息和调试信息。下面是pg相关工具的官方文档。

pg的EXPLAIN命令说明文档：
https://www.postgresql.org/docs/9.3/sql-explain.html

pg的ANALYZE命令说明文档
https://www.postgresql.org/docs/9.3/sql-analyze.html

pg日志打开调试日志，可以看到优化器的诊断信息，官方文档：
https://www.postgresql.org/docs/9.3/runtime-config-logging.html

下面内容简要展示这些工具的使用方法。

查看当前所有连接状态，含正在执行的SQL：

test=# select * from pg_stat_activity;
 datid | datname |  pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |        backend_start         |          xact_start   
       |         query_start          |         state_change          | waiting | state  |              query              
-------+---------+-------+----------+----------+------------------+-------------+-----------------+-------------+------------------------------+-----------------------
-------+------------------------------+-------------------------------+---------+--------+---------------------------------
 16384 | test    | 49723 |       10 | sdbadmin | psql             |             |                 |          -1 | 2020-01-08 19:26:38.72099+08 | 2020-01-08 22:28:42.02
633+08 | 2020-01-08 22:28:42.02633+08 | 2020-01-08 22:28:42.026334+08 | f       | active | select * from pg_stat_activity;
(1 row)

只生成访问计划：

test=#  EXPLAIN (ANALYZE FALSE, VERBOSE TRUE, COSTS TRUE, BUFFERS FALSE, TIMING FALSE, FORMAT TEXT) select * from accounts where  account_id='A11111';
                             QUERY PLAN                             
--------------------------------------------------------------------
 Foreign Scan on public.accounts  (cost=0.00..0.06 rows=1 width=18)
   Output: account_id, cust_id, balance
   Filter: (accounts.account_id = 'A11111'::text)
   Foreign Namespace: test_db.accounts
     Filter: ({ "account_id": { "$et": "A11111" } })
(5 rows)

收集统计信息、执行并生成访问计划：

test=# EXPLAIN (ANALYZE TRUE, VERBOSE TRUE, COSTS TRUE, BUFFERS TRUE, TIMING TRUE, FORMAT TEXT) select * from accounts where  account_id='A11111';
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.accounts  (cost=0.00..0.06 rows=1 width=18) (actual time=7.288..7.290 rows=1 loops=1)
   Output: account_id, cust_id, balance
   Filter: (accounts.account_id = 'A11111'::text)
   Foreign Namespace: test_db.accounts
     Filter: ({ "account_id": { "$et": "A11111" } })
 Total runtime: 8.675 ms
(6 rows)

生成统计信息：

test=# analyze verbose accounts;
INFO:  analyzing "public.accounts"
INFO:  "accounts": collection contains 1 rows; 1 rows in sample
ANALYZE

查看统计信息：

test=# select * from pg_class where relname='accounts';
 relname  | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid 
| relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relispopulate
d | relfrozenxid | relminmxid | relacl | reloptions 
----------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------
+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+--------------
--+--------------+------------+--------+------------
 accounts |         2200 |   49163 |         0 |       10 |     0 |       49161 |             0 |        0 |         1 |             0 |             0 |             0 
| f           | f           | p              | f       |        3 |         0 | f          | f          | f           | f              | f              | t            
  |            0 |          0 |        | 
(1 row)

注： reltuples 是记录数。

查看数值分布：

test=# select * from pg_stats where tablename='accounts';
 schemaname | tablename |  attname   | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_co
mmon_elems | most_common_elem_freqs | elem_count_histogram 
------------+-----------+------------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+--------
-----------+------------------------+----------------------
 public     | accounts  | account_id | f         |         0 |         7 |         -1 |                  |                   |                  |             |        
           |                        | 
 public     | accounts  | cust_id    | f         |         0 |         6 |         -1 |                  |                   |                  |             |        
           |                        | 
 public     | accounts  | balance    | f         |         0 |         5 |         -1 |                  |                   |                  |             |        
           |                        | 
(3 rows)

修改pg配置参数，然后生效，以便于打开调试模式：

test=# SET log_min_messages=debug5;
SET
test=# SET debug_print_parse=on;
SET
test=# SET debug_print_rewritten=on;
SET
test=# SET debug_print_plan=on;
SET
test=# SET debug_pretty_print=on;
SET
test=# SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

注释：debug5这个日志级别会造成大量日志记录，分析诊断完成之后，记得将这些值修改回原来值。

debug5模式下，日志记录的示例内容：
下面是执行 explain select * from accounts where account_id=‘A11111’; 时的日志记录：

2020-01-08 23:21:32.990 CST 25843 [local]DEBUG:  StartTransactionCommand
2020-01-08 23:21:32.990 CST 25843 [local]DEBUG:  StartTransaction
2020-01-08 23:21:32.990 CST 25843 [local]DEBUG:  name: unnamed; blockState:       DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
2020-01-08 23:21:32.991 CST 25843 [local]LOG:  parse tree:
2020-01-08 23:21:32.991 CST 25843 [local]DETAIL:     {QUERY 
	   :commandType 5 
	   :querySource 0 
	   :canSetTag true 
	   :utilityStmt ? 
	   :resultRelation 0 
	   :hasAggs false 
	   :hasWindowFuncs false 
	   :hasSubLinks false 
	   :hasDistinctOn false 
	   :hasRecursive false 
	   :hasModifyingCTE false 
	   :hasForUpdate false 
	   :cteList <> 
	   :rtable <> 
	   :jointree <> 
	   :targetList <> 
	   :returningList <> 
	   :groupClause <> 
	   :havingQual <> 
	   :windowClause <> 
	   :distinctClause <> 
	   :sortClause <> 
	   :limitOffset <> 
	   :limitCount <> 
	   :rowMarks <> 
	   :setOperations <> 
	   :constraintDeps <>
	   }
	
2020-01-08 23:21:32.991 CST 25843 [local]STATEMENT:  explain select * from accounts where account_id='A11111';
2020-01-08 23:21:32.991 CST 25843 [local]LOG:  rewritten parse tree:
2020-01-08 23:21:32.991 CST 25843 [local]DETAIL:  (
	   {QUERY 
	   :commandType 5 
	   :querySource 0 
	   :canSetTag true 
	   :utilityStmt ? 
	   :resultRelation 0 
	   :hasAggs false 
	   :hasWindowFuncs false 
	   :hasSubLinks false 
	   :hasDistinctOn false 
	   :hasRecursive false 
	   :hasModifyingCTE false 
	   :hasForUpdate false 
	   :cteList <> 
	   :rtable <> 
	   :jointree <> 
	   :targetList <> 
	   :returningList <> 
	   :groupClause <> 
	   :havingQual <> 
	   :windowClause <> 
	   :distinctClause <> 
	   :sortClause <> 
	   :limitOffset <> 
	   :limitCount <> 
	   :rowMarks <> 
	   :setOperations <> 
	   :constraintDeps <>
	   }
	)
	
2020-01-08 23:21:32.991 CST 25843 [local]STATEMENT:  explain select * from accounts where account_id='A11111';
2020-01-08 23:21:32.991 CST 25843 [local]DEBUG:  ProcessUtility
2020-01-08 23:21:32.992 CST 25843 [local]DEBUG:  hostName=c1:11810,c2:11810,c3:11810,service=0
2020-01-08 23:21:32.992 CST 25843 [local]DEBUG:  foreign table:49161, handle found:0
2020-01-08 23:21:32.992 CST 25843 [local]DEBUG:  hostName=c1:11810,c2:11810,c3:11810,service=0
2020-01-08 23:21:32.993 CST 25843 [local]DEBUG:  hostName=c1:11810,c2:11810,c3:11810,service=0
2020-01-08 23:21:32.993 CST 25843 [local]DEBUG:  connecting :list=c1:11810:c2:11810:c3:11810:::,num=3
2020-01-08 23:21:33.006 CST 25843 [local]DEBUG:  bson value={ "PreferedInstance": "a", "Source": "pg-25843" },label[display session attr]
2020-01-08 23:21:33.006 CST 25843 [local]DEBUG:  setting session attr
2020-01-08 23:21:33.040 CST 25843 [local]DEBUG:  var info:foreign_table_id=49161, varattno=1, valevelsup=0
2020-01-08 23:21:33.040 CST 25843 [local]DEBUG:  isSortCanPushDown:empty pathkeys
2020-01-08 23:21:33.040 CST 25843 [local]DEBUG:  foreign table:49161, handle found:1
2020-01-08 23:21:33.040 CST 25843 [local]DEBUG:  get index from db
2020-01-08 23:21:33.048 CST 25843 [local]DEBUG:  SdbGetForeignPaths:foreignPath={FOREIGNPATH :pathtype 120 :parent_relids (b 1) :required_outer (b) :rows 1 :startup_cost 0.00 :total_cost 0.06 :pathkeys <> :fdw_private <>}
2020-01-08 23:21:33.048 CST 25843 [local]DEBUG:  foreign table:49161, handle found:1
2020-01-08 23:21:33.048 CST 25843 [local]DEBUG:  SdbGetForeignPlan:bestPath={FOREIGNPATH :pathtype 120 :parent_relids (b 1) :required_outer (b) :rows 1 :startup_cost 0.00 :total_cost 0.06 :pathkeys <> :fdw_private <>}
2020-01-08 23:21:33.049 CST 25843 [local]DEBUG:  var info:foreign_table_id=49161, varattno=1, valevelsup=0
2020-01-08 23:21:33.049 CST 25843 [local]DEBUG:  bson value={ "account_id": { "$et": "A11111" } },label[SdbGetForeignPlan query]
2020-01-08 23:21:33.049 CST 25843 [local]DEBUG:  reset parse's limit and offset
2020-01-08 23:21:33.049 CST 25843 [local]DEBUG:  SdbAllocRecord:usedCount=1,index=0
2020-01-08 23:21:33.051 CST 25843 [local]LOG:  plan:
2020-01-08 23:21:33.051 CST 25843 [local]DETAIL:     {PLANNEDSTMT 
	   :commandType 1 
	   :queryId 0 
	   :hasReturning false 
	   :hasModifyingCTE false 
	   :canSetTag true 
	   :transientPlan false 
	   :planTree 
	      {FOREIGNSCAN 
	      :startup_cost 0.00 
	      :total_cost 0.06 
	      :plan_rows 1 
	      :plan_width 18 
	      :targetlist (
	         {TARGETENTRY 
	         :expr 
	            {VAR 
	            :varno 1 
	            :varattno 1 
	            :vartype 25 
	            :vartypmod -1 
	            :varcollid 100 
	            :varlevelsup 0 
	            :varnoold 1 
	            :varoattno 1 
	            :location 15
	            }
	         :resno 1 
	         :resname account_id 
	         :ressortgroupref 0 
	         :resorigtbl 49161 
	         :resorigcol 1 
	         :resjunk false
	         }
	         {TARGETENTRY 
	         :expr 
	            {VAR 
	            :varno 1 
	            :varattno 2 
	            :vartype 25 
	            :vartypmod -1 
	            :varcollid 100 
	            :varlevelsup 0 
	            :varnoold 1 
	            :varoattno 2 
	            :location 15
	            }
	         :resno 2 
	         :resname cust_id 
	         :ressortgroupref 0 
	         :resorigtbl 49161 
	         :resorigcol 2 
	         :resjunk false
	         }
	         {TARGETENTRY 
	         :expr 
	            {VAR 
	            :varno 1 
	            :varattno 3 
	            :vartype 1700 
	            :vartypmod 1310730 
	            :varcollid 0 
	            :varlevelsup 0 
	            :varnoold 1 
	            :varoattno 3 
	            :location 15
	            }
	         :resno 3 
	         :resname balance 
	         :ressortgroupref 0 
	         :resorigtbl 49161 
	         :resorigcol 3 
	         :resjunk false
	         }
	      )
	      :qual (
	         {OPEXPR 
	         :opno 98 
	         :opfuncid 67 
	         :opresulttype 16 
	         :opretset false 
	         :opcollid 0 
	         :inputcollid 100 
	         :args (
	            {VAR 
	            :varno 1 
	            :varattno 1 
	            :vartype 25 
	            :vartypmod -1 
	            :varcollid 100 
	            :varlevelsup 0 
	            :varnoold 1 
	            :varoattno 1 
	            :location 37
	            }
	            {CONST 
	            :consttype 25 
	            :consttypmod -1 
	            :constcollid 100 
	            :constlen -1 
	            :constbyval false 
	            :constisnull false 
	            :location 48 
	            :constvalue 10 [ 40 0 0 0 65 49 49 49 49 49 ]
	            }
	         )
	         :location 47
	         }
	      )
	      :lefttree <> 
	      :righttree <> 
	      :initPlan <> 
	      :extParam (b)
	      :allParam (b)
	      :scanrelid 1 
	      :fdw_exprs <> 
	      :fdw_private ((
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 3 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 12 [ 48 0 0 0 99 49 58 49 49 56 49 48 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 12 [ 48 0 0 0 99 50 58 49 49 56 49 48 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 12 [ 48 0 0 0 99 51 58 49 49 56 49 48 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 16 0 0 0 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 16 0 0 0 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull true 
	         :location -1 
	         :constvalue <>
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 0 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull true 
	         :location -1 
	         :constvalue <>
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 5 [ 20 0 0 0 97 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 16 0 0 0 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ -1 -1 -1 -1 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 7 [ 28 0 0 0 79 70 70 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 11 [ 44 0 0 0 116 101 115 116 95 100 98 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 12 [ 48 0 0 0 97 99 99 111 117 110 116 115 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 12 [ 48 0 0 0 97 99 99 111 117 110 116 115 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 3 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 14 [ 56 0 0 0 97 99 99 111 117 110 116 95 105 100 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 1 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 26 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 25 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ -1 -1 -1 -1 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 11 [ 44 0 0 0 99 117 115 116 95 105 100 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 2 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 26 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 25 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ -1 -1 -1 -1 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 11 [ 44 0 0 0 98 97 108 97 110 99 101 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 3 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 26 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ -92 6 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 10 0 20 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 2275 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 39 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 39 [ 38 0 0 0 3 97 99 99 111 117 110 116 95 105 100 0 21 
	         0 0 0 2 36 101 116 0 7 0 0 0 65 49 49 49 49 49 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 2275 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 6 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 6 [ 5 0 0 0 0 127 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 8 
	         :constbyval false 
	         :constisnull true 
	         :location -1 
	         :constvalue <>
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 8 
	         :constbyval false 
	         :constisnull true 
	         :location -1 
	         :constvalue <>
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 1 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 1 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 8 
	         :constbyval false 
	         :constisnull true 
	         :location -1 
	         :constvalue <>
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 1 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 26 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 9 -64 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 1 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 2 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 7 [ 28 0 0 0 95 105 100 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 14 [ 56 0 0 0 97 99 99 111 117 110 116 95 105 100 ]
	         }
	      )
	      (
	         {VAR 
	         :varno 1 
	         :varattno 1 
	         :vartype 25 
	         :vartypmod -1 
	         :varcollid 100 
	         :varlevelsup 0 
	         :varnoold 1 
	         :varoattno 1 
	         :location 15
	         }
	         {VAR 
	         :varno 1 
	         :varattno 2 
	         :vartype 25 
	         :vartypmod -1 
	         :varcollid 100 
	         :varlevelsup 0 
	         :varnoold 1 
	         :varoattno 2 
	         :location 15
	         }
	         {VAR 
	         :varno 1 
	         :varattno 3 
	         :vartype 1700 
	         :vartypmod 1310730 
	         :varcollid 0 
	         :varlevelsup 0 
	         :varnoold 1 
	         :varoattno 3 
	         :location 15
	         }
	      ))
	      :fsSystemCol false
	      }
	   :rtable (
	      {RTE 
	      :alias <> 
	      :eref 
	         {ALIAS 
	         :aliasname accounts 
	         :colnames ("account_id" "cust_id" "balance")
	         }
	      :rtekind 0 
	      :relid 49161 
	      :relkind f 
	      :lateral false 
	      :inh false 
	      :inFromCl true 
	      :requiredPerms 2 
	      :checkAsUser 0 
	      :selectedCols (b 9 10 11)
	      :modifiedCols (b)
	      }
	   )
	   :resultRelations <> 
	   :utilityStmt <> 
	   :subplans <> 
	   :rewindPlanIDs (b)
	   :rowMarks <> 
	   :relationOids (o 49161)
	   :invalItems <> 
	   :nParamExec 0
	   }
	
2020-01-08 23:21:33.051 CST 25843 [local]STATEMENT:  explain select * from accounts where account_id='A11111';
2020-01-08 23:21:33.051 CST 25843 [local]DEBUG:  hostName=c1:11810,c2:11810,c3:11810,service=0
2020-01-08 23:21:33.051 CST 25843 [local]DEBUG:  CommitTransactionCommand
2020-01-08 23:21:33.051 CST 25843 [local]DEBUG:  CommitTransaction
2020-01-08 23:21:33.051 CST 25843 [local]DEBUG:  name: unnamed; blockState:       STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:

下面是执行 select * from accounts where account_id=‘A11111’; 的日志记录：

2020-01-08 23:23:50.994 CST 25843 [local]DEBUG:  StartTransactionCommand
2020-01-08 23:23:50.994 CST 25843 [local]DEBUG:  StartTransaction
2020-01-08 23:23:50.994 CST 25843 [local]DEBUG:  name: unnamed; blockState:       DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
2020-01-08 23:23:50.995 CST 25843 [local]LOG:  parse tree:
2020-01-08 23:23:50.995 CST 25843 [local]DETAIL:     {QUERY 
	   :commandType 1 
	   :querySource 0 
	   :canSetTag true 
	   :utilityStmt <> 
	   :resultRelation 0 
	   :hasAggs false 
	   :hasWindowFuncs false 
	   :hasSubLinks false 
	   :hasDistinctOn false 
	   :hasRecursive false 
	   :hasModifyingCTE false 
	   :hasForUpdate false 
	   :cteList <> 
	   :rtable (
	      {RTE 
	      :alias <> 
	      :eref 
	         {ALIAS 
	         :aliasname accounts 
	         :colnames ("account_id" "cust_id" "balance")
	         }
	      :rtekind 0 
	      :relid 49161 
	      :relkind f 
	      :lateral false 
	      :inh true 
	      :inFromCl true 
	      :requiredPerms 2 
	      :checkAsUser 0 
	      :selectedCols (b 9 10 11)
	      :modifiedCols (b)
	      }
	   )
	   :jointree 
	      {FROMEXPR 
	      :fromlist (
	         {RANGETBLREF 
	         :rtindex 1
	         }
	      )
	      :quals 
	         {OPEXPR 
	         :opno 98 
	         :opfuncid 67 
	         :opresulttype 16 
	         :opretset false 
	         :opcollid 0 
	         :inputcollid 100 
	         :args (
	            {VAR 
	            :varno 1 
	            :varattno 1 
	            :vartype 25 
	            :vartypmod -1 
	            :varcollid 100 
	            :varlevelsup 0 
	            :varnoold 1 
	            :varoattno 1 
	            :location 29
	            }
	            {CONST 
	            :consttype 25 
	            :consttypmod -1 
	            :constcollid 100 
	            :constlen -1 
	            :constbyval false 
	            :constisnull false 
	            :location 40 
	            :constvalue 10 [ 40 0 0 0 65 49 49 49 49 49 ]
	            }
	         )
	         :location 39
	         }
	      }
	   :targetList (
	      {TARGETENTRY 
	      :expr 
	         {VAR 
	         :varno 1 
	         :varattno 1 
	         :vartype 25 
	         :vartypmod -1 
	         :varcollid 100 
	         :varlevelsup 0 
	         :varnoold 1 
	         :varoattno 1 
	         :location 7
	         }
	      :resno 1 
	      :resname account_id 
	      :ressortgroupref 0 
	      :resorigtbl 49161 
	      :resorigcol 1 
	      :resjunk false
	      }
	      {TARGETENTRY 
	      :expr 
	         {VAR 
	         :varno 1 
	         :varattno 2 
	         :vartype 25 
	         :vartypmod -1 
	         :varcollid 100 
	         :varlevelsup 0 
	         :varnoold 1 
	         :varoattno 2 
	         :location 7
	         }
	      :resno 2 
	      :resname cust_id 
	      :ressortgroupref 0 
	      :resorigtbl 49161 
	      :resorigcol 2 
	      :resjunk false
	      }
	      {TARGETENTRY 
	      :expr 
	         {VAR 
	         :varno 1 
	         :varattno 3 
	         :vartype 1700 
	         :vartypmod 1310730 
	         :varcollid 0 
	         :varlevelsup 0 
	         :varnoold 1 
	         :varoattno 3 
	         :location 7
	         }
	      :resno 3 
	      :resname balance 
	      :ressortgroupref 0 
	      :resorigtbl 49161 
	      :resorigcol 3 
	      :resjunk false
	      }
	   )
	   :returningList <> 
	   :groupClause <> 
	   :havingQual <> 
	   :windowClause <> 
	   :distinctClause <> 
	   :sortClause <> 
	   :limitOffset <> 
	   :limitCount <> 
	   :rowMarks <> 
	   :setOperations <> 
	   :constraintDeps <>
	   }
	
2020-01-08 23:23:50.995 CST 25843 [local]STATEMENT:  select * from accounts where account_id='A11111';
2020-01-08 23:23:50.995 CST 25843 [local]LOG:  rewritten parse tree:
2020-01-08 23:23:50.995 CST 25843 [local]DETAIL:  (
	   {QUERY 
	   :commandType 1 
	   :querySource 0 
	   :canSetTag true 
	   :utilityStmt <> 
	   :resultRelation 0 
	   :hasAggs false 
	   :hasWindowFuncs false 
	   :hasSubLinks false 
	   :hasDistinctOn false 
	   :hasRecursive false 
	   :hasModifyingCTE false 
	   :hasForUpdate false 
	   :cteList <> 
	   :rtable (
	      {RTE 
	      :alias <> 
	      :eref 
	         {ALIAS 
	         :aliasname accounts 
	         :colnames ("account_id" "cust_id" "balance")
	         }
	      :rtekind 0 
	      :relid 49161 
	      :relkind f 
	      :lateral false 
	      :inh true 
	      :inFromCl true 
	      :requiredPerms 2 
	      :checkAsUser 0 
	      :selectedCols (b 9 10 11)
	      :modifiedCols (b)
	      }
	   )
	   :jointree 
	      {FROMEXPR 
	      :fromlist (
	         {RANGETBLREF 
	         :rtindex 1
	         }
	      )
	      :quals 
	         {OPEXPR 
	         :opno 98 
	         :opfuncid 67 
	         :opresulttype 16 
	         :opretset false 
	         :opcollid 0 
	         :inputcollid 100 
	         :args (
	            {VAR 
	            :varno 1 
	            :varattno 1 
	            :vartype 25 
	            :vartypmod -1 
	            :varcollid 100 
	            :varlevelsup 0 
	            :varnoold 1 
	            :varoattno 1 
	            :location 29
	            }
	            {CONST 
	            :consttype 25 
	            :consttypmod -1 
	            :constcollid 100 
	            :constlen -1 
	            :constbyval false 
	            :constisnull false 
	            :location 40 
	            :constvalue 10 [ 40 0 0 0 65 49 49 49 49 49 ]
	            }
	         )
	         :location 39
	         }
	      }
	   :targetList (
	      {TARGETENTRY 
	      :expr 
	         {VAR 
	         :varno 1 
	         :varattno 1 
	         :vartype 25 
	         :vartypmod -1 
	         :varcollid 100 
	         :varlevelsup 0 
	         :varnoold 1 
	         :varoattno 1 
	         :location 7
	         }
	      :resno 1 
	      :resname account_id 
	      :ressortgroupref 0 
	      :resorigtbl 49161 
	      :resorigcol 1 
	      :resjunk false
	      }
	      {TARGETENTRY 
	      :expr 
	         {VAR 
	         :varno 1 
	         :varattno 2 
	         :vartype 25 
	         :vartypmod -1 
	         :varcollid 100 
	         :varlevelsup 0 
	         :varnoold 1 
	         :varoattno 2 
	         :location 7
	         }
	      :resno 2 
	      :resname cust_id 
	      :ressortgroupref 0 
	      :resorigtbl 49161 
	      :resorigcol 2 
	      :resjunk false
	      }
	      {TARGETENTRY 
	      :expr 
	         {VAR 
	         :varno 1 
	         :varattno 3 
	         :vartype 1700 
	         :vartypmod 1310730 
	         :varcollid 0 
	         :varlevelsup 0 
	         :varnoold 1 
	         :varoattno 3 
	         :location 7
	         }
	      :resno 3 
	      :resname balance 
	      :ressortgroupref 0 
	      :resorigtbl 49161 
	      :resorigcol 3 
	      :resjunk false
	      }
	   )
	   :returningList <> 
	   :groupClause <> 
	   :havingQual <> 
	   :windowClause <> 
	   :distinctClause <> 
	   :sortClause <> 
	   :limitOffset <> 
	   :limitCount <> 
	   :rowMarks <> 
	   :setOperations <> 
	   :constraintDeps <>
	   }
	)
	
2020-01-08 23:23:50.995 CST 25843 [local]STATEMENT:  select * from accounts where account_id='A11111';
2020-01-08 23:23:50.995 CST 25843 [local]DEBUG:  hostName=c1:11810,c2:11810,c3:11810,service=0
2020-01-08 23:23:50.995 CST 25843 [local]DEBUG:  foreign table:49161, handle found:1
2020-01-08 23:23:50.995 CST 25843 [local]DEBUG:  var info:foreign_table_id=49161, varattno=1, valevelsup=0
2020-01-08 23:23:50.995 CST 25843 [local]DEBUG:  isSortCanPushDown:empty pathkeys
2020-01-08 23:23:50.995 CST 25843 [local]DEBUG:  foreign table:49161, handle found:1
2020-01-08 23:23:50.995 CST 25843 [local]DEBUG:  get index from cache
2020-01-08 23:23:50.995 CST 25843 [local]DEBUG:  i=0,keynum=1,key[0]=account_id
2020-01-08 23:23:50.995 CST 25843 [local]DEBUG:  SdbGetForeignPaths:foreignPath={FOREIGNPATH :pathtype 120 :parent_relids (b 1) :required_outer (b) :rows 1 :startup_cost 0.00 :total_cost 0.06 :pathkeys <> :fdw_private <>}
2020-01-08 23:23:50.995 CST 25843 [local]DEBUG:  foreign table:49161, handle found:1
2020-01-08 23:23:50.995 CST 25843 [local]DEBUG:  SdbGetForeignPlan:bestPath={FOREIGNPATH :pathtype 120 :parent_relids (b 1) :required_outer (b) :rows 1 :startup_cost 0.00 :total_cost 0.06 :pathkeys <> :fdw_private <>}
2020-01-08 23:23:50.995 CST 25843 [local]DEBUG:  var info:foreign_table_id=49161, varattno=1, valevelsup=0
2020-01-08 23:23:50.995 CST 25843 [local]DEBUG:  bson value={ "account_id": { "$et": "A11111" } },label[SdbGetForeignPlan query]
2020-01-08 23:23:50.995 CST 25843 [local]DEBUG:  reset parse's limit and offset
2020-01-08 23:23:50.995 CST 25843 [local]DEBUG:  SdbAllocRecord:usedCount=1,index=0
2020-01-08 23:23:50.996 CST 25843 [local]LOG:  plan:
2020-01-08 23:23:50.997 CST 25843 [local]DETAIL:     {PLANNEDSTMT 
	   :commandType 1 
	   :queryId 0 
	   :hasReturning false 
	   :hasModifyingCTE false 
	   :canSetTag true 
	   :transientPlan false 
	   :planTree 
	      {FOREIGNSCAN 
	      :startup_cost 0.00 
	      :total_cost 0.06 
	      :plan_rows 1 
	      :plan_width 18 
	      :targetlist (
	         {TARGETENTRY 
	         :expr 
	            {VAR 
	            :varno 1 
	            :varattno 1 
	            :vartype 25 
	            :vartypmod -1 
	            :varcollid 100 
	            :varlevelsup 0 
	            :varnoold 1 
	            :varoattno 1 
	            :location 7
	            }
	         :resno 1 
	         :resname account_id 
	         :ressortgroupref 0 
	         :resorigtbl 49161 
	         :resorigcol 1 
	         :resjunk false
	         }
	         {TARGETENTRY 
	         :expr 
	            {VAR 
	            :varno 1 
	            :varattno 2 
	            :vartype 25 
	            :vartypmod -1 
	            :varcollid 100 
	            :varlevelsup 0 
	            :varnoold 1 
	            :varoattno 2 
	            :location 7
	            }
	         :resno 2 
	         :resname cust_id 
	         :ressortgroupref 0 
	         :resorigtbl 49161 
	         :resorigcol 2 
	         :resjunk false
	         }
	         {TARGETENTRY 
	         :expr 
	            {VAR 
	            :varno 1 
	            :varattno 3 
	            :vartype 1700 
	            :vartypmod 1310730 
	            :varcollid 0 
	            :varlevelsup 0 
	            :varnoold 1 
	            :varoattno 3 
	            :location 7
	            }
	         :resno 3 
	         :resname balance 
	         :ressortgroupref 0 
	         :resorigtbl 49161 
	         :resorigcol 3 
	         :resjunk false
	         }
	      )
	      :qual (
	         {OPEXPR 
	         :opno 98 
	         :opfuncid 67 
	         :opresulttype 16 
	         :opretset false 
	         :opcollid 0 
	         :inputcollid 100 
	         :args (
	            {VAR 
	            :varno 1 
	            :varattno 1 
	            :vartype 25 
	            :vartypmod -1 
	            :varcollid 100 
	            :varlevelsup 0 
	            :varnoold 1 
	            :varoattno 1 
	            :location 29
	            }
	            {CONST 
	            :consttype 25 
	            :consttypmod -1 
	            :constcollid 100 
	            :constlen -1 
	            :constbyval false 
	            :constisnull false 
	            :location 40 
	            :constvalue 10 [ 40 0 0 0 65 49 49 49 49 49 ]
	            }
	         )
	         :location 39
	         }
	      )
	      :lefttree <> 
	      :righttree <> 
	      :initPlan <> 
	      :extParam (b)
	      :allParam (b)
	      :scanrelid 1 
	      :fdw_exprs <> 
	      :fdw_private ((
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 3 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 12 [ 48 0 0 0 99 49 58 49 49 56 49 48 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 12 [ 48 0 0 0 99 50 58 49 49 56 49 48 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 12 [ 48 0 0 0 99 51 58 49 49 56 49 48 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 16 0 0 0 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 16 0 0 0 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull true 
	         :location -1 
	         :constvalue <>
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 0 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull true 
	         :location -1 
	         :constvalue <>
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 5 [ 20 0 0 0 97 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 16 0 0 0 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ -1 -1 -1 -1 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 7 [ 28 0 0 0 79 70 70 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 11 [ 44 0 0 0 116 101 115 116 95 100 98 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 12 [ 48 0 0 0 97 99 99 111 117 110 116 115 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 12 [ 48 0 0 0 97 99 99 111 117 110 116 115 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 3 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 14 [ 56 0 0 0 97 99 99 111 117 110 116 95 105 100 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 1 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 26 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 25 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ -1 -1 -1 -1 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 11 [ 44 0 0 0 99 117 115 116 95 105 100 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 2 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 26 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 25 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ -1 -1 -1 -1 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 11 [ 44 0 0 0 98 97 108 97 110 99 101 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 3 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 26 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ -92 6 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 10 0 20 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 2275 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 39 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 39 [ 38 0 0 0 3 97 99 99 111 117 110 116 95 105 100 0 21 
	         0 0 0 2 36 101 116 0 7 0 0 0 65 49 49 49 49 49 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 2275 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 6 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 6 [ 5 0 0 0 0 127 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 8 
	         :constbyval false 
	         :constisnull true 
	         :location -1 
	         :constvalue <>
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 8 
	         :constbyval false 
	         :constisnull true 
	         :location -1 
	         :constvalue <>
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 1 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 1 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 8 
	         :constbyval false 
	         :constisnull true 
	         :location -1 
	         :constvalue <>
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 1 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 26 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 9 -64 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 1 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 23 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen 4 
	         :constbyval true 
	         :constisnull false 
	         :location -1 
	         :constvalue 4 [ 2 0 0 0 0 0 0 0 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 7 [ 28 0 0 0 95 105 100 ]
	         }
	         {CONST 
	         :consttype 25 
	         :consttypmod -1 
	         :constcollid 0 
	         :constlen -1 
	         :constbyval false 
	         :constisnull false 
	         :location -1 
	         :constvalue 14 [ 56 0 0 0 97 99 99 111 117 110 116 95 105 100 ]
	         }
	      )
	      (
	         {VAR 
	         :varno 1 
	         :varattno 1 
	         :vartype 25 
	         :vartypmod -1 
	         :varcollid 100 
	         :varlevelsup 0 
	         :varnoold 1 
	         :varoattno 1 
	         :location 7
	         }
	         {VAR 
	         :varno 1 
	         :varattno 2 
	         :vartype 25 
	         :vartypmod -1 
	         :varcollid 100 
	         :varlevelsup 0 
	         :varnoold 1 
	         :varoattno 2 
	         :location 7
	         }
	         {VAR 
	         :varno 1 
	         :varattno 3 
	         :vartype 1700 
	         :vartypmod 1310730 
	         :varcollid 0 
	         :varlevelsup 0 
	         :varnoold 1 
	         :varoattno 3 
	         :location 7
	         }
	      ))
	      :fsSystemCol false
	      }
	   :rtable (
	      {RTE 
	      :alias <> 
	      :eref 
	         {ALIAS 
	         :aliasname accounts 
	         :colnames ("account_id" "cust_id" "balance")
	         }
	      :rtekind 0 
	      :relid 49161 
	      :relkind f 
	      :lateral false 
	      :inh false 
	      :inFromCl true 
	      :requiredPerms 2 
	      :checkAsUser 0 
	      :selectedCols (b 9 10 11)
	      :modifiedCols (b)
	      }
	   )
	   :resultRelations <> 
	   :utilityStmt <> 
	   :subplans <> 
	   :rewindPlanIDs (b)
	   :rowMarks <> 
	   :relationOids (o 49161)
	   :invalItems <> 
	   :nParamExec 0
	   }
	
2020-01-08 23:23:50.997 CST 25843 [local]STATEMENT:  select * from accounts where account_id='A11111';
2020-01-08 23:23:50.997 CST 25843 [local]DEBUG:  foreign table:49161, handle found:1
2020-01-08 23:23:51.012 CST 25843 [local]DEBUG:  CommitTransactionCommand
2020-01-08 23:23:51.013 CST 25843 [local]DEBUG:  CommitTransaction
2020-01-08 23:23:51.013 CST 25843 [local]DEBUG:  name: unnamed; blockState:       STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
2020-01-08 23:23:52.244 CST 25149 DEBUG:  StartTransaction
2020-01-08 23:23:52.244 CST 25149 DEBUG:  name: unnamed; blockState:       DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
2020-01-08 23:23:52.244 CST 25149 DEBUG:  CommitTransaction
2020-01-08 23:23:52.244 CST 25149 DEBUG:  name: unnamed; blockState:       STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
2020-01-08 23:23:52.244 CST 25150 DEBUG:  received inquiry for 0
2020-01-08 23:23:52.244 CST 25150 DEBUG:  writing statsfile 'pg_stat_tmp/global.stat'
2020-01-08 23:23:52.244 CST 25150 DEBUG:  writing statsfile 'pg_stat_tmp/db_0.stat'
2020-01-08 23:23:52.255 CST 29807 DEBUG:  InitPostgres
2020-01-08 23:23:52.255 CST 29807 DEBUG:  my backend ID is 3
2020-01-08 23:23:52.255 CST 29807 DEBUG:  StartTransaction
2020-01-08 23:23:52.255 CST 29807 DEBUG:  name: unnamed; blockState:       DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
2020-01-08 23:23:52.256 CST 29807 DEBUG:  CommitTransaction
2020-01-08 23:23:52.256 CST 29807 DEBUG:  name: unnamed; blockState:       STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
2020-01-08 23:23:52.256 CST 29807 DEBUG:  autovacuum: processing database "postgres"
2020-01-08 23:23:52.256 CST 25150 DEBUG:  received inquiry for 12949
2020-01-08 23:23:52.256 CST 25150 DEBUG:  writing statsfile 'pg_stat_tmp/global.stat'
2020-01-08 23:23:52.256 CST 25150 DEBUG:  writing statsfile 'pg_stat_tmp/db_12949.stat'
2020-01-08 23:23:52.268 CST 29807 DEBUG:  StartTransaction
2020-01-08 23:23:52.268 CST 29807 DEBUG:  name: unnamed; blockState:       DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
2020-01-08 23:23:52.268 CST 29807 DEBUG:  pg_statistic: vac: 0 (threshold 128), anl: 0 (threshold 89)
2020-01-08 23:23:52.268 CST 29807 DEBUG:  pg_type: vac: 0 (threshold 117), anl: 0 (threshold 83)
2020-01-08 23:23:52.268 CST 29807 DEBUG:  pg_authid: vac: 0 (threshold 50), anl: 0 (threshold 50)
2020-01-08 23:23:52.268 CST 29807 DEBUG:  pg_attribute: vac: 0 (threshold 505), anl: 0 (threshold 277)
2020-01-08 23:23:52.268 CST 29807 DEBUG:  pg_proc: vac: 0 (threshold 556), anl: 0 (threshold 303)
2020-01-08 23:23:52.268 CST 29807 DEBUG:  pg_class: vac: 0 (threshold 108), anl: 0 (threshold 79)
2020-01-08 23:23:52.268 CST 29807 DEBUG:  pg_index: vac: 0 (threshold 72), anl: 0 (threshold 61)
2020-01-08 23:23:52.268 CST 29807 DEBUG:  pg_opclass: vac: 0 (threshold 74), anl: 0 (threshold 62)
2020-01-08 23:23:52.268 CST 29807 DEBUG:  pg_am: vac: 0 (threshold 51), anl: 0 (threshold 50)
2020-01-08 23:23:52.268 CST 29807 DEBUG:  pg_amop: vac: 0 (threshold 131), anl: 0 (threshold 90)
2020-01-08 23:23:52.268 CST 29807 DEBUG:  pg_amproc: vac: 0 (threshold 108), anl: 0 (threshold 79)
2020-01-08 23:23:52.268 CST 29807 DEBUG:  pg_database: vac: 0 (threshold 50), anl: 0 (threshold 50)
2020-01-08 23:23:52.268 CST 29807 DEBUG:  pg_cast: vac: 0 (threshold 89), anl: 0 (threshold 70)
2020-01-08 23:23:52.268 CST 29807 DEBUG:  pg_namespace: vac: 0 (threshold 51), anl: 0 (threshold 51)
2020-01-08 23:23:52.268 CST 29807 DEBUG:  pg_tablespace: vac: 0 (threshold 50), anl: 0 (threshold 50)
2020-01-08 23:23:52.268 CST 29807 DEBUG:  CommitTransaction
2020-01-08 23:23:52.268 CST 29807 DEBUG:  name: unnamed; blockState:       STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
2020-01-08 23:23:52.268 CST 29807 DEBUG:  shmem_exit(0): 8 callbacks to make
2020-01-08 23:23:52.268 CST 29807 DEBUG:  proc_exit(0): 2 callbacks to make
2020-01-08 23:23:52.268 CST 29807 DEBUG:  exit(0)
2020-01-08 23:23:52.268 CST 29807 DEBUG:  shmem_exit(-1): 0 callbacks to make
2020-01-08 23:23:52.268 CST 29807 DEBUG:  proc_exit(-1): 0 callbacks to make
2020-01-08 23:23:52.269 CST 25124 DEBUG:  reaping dead processes
2020-01-08 23:23:52.269 CST 25124 DEBUG:  server process (PID 29807) exited with exit code 0
u010024893
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
pg-diff：PostgreSQL模式和数据比较工具
02-03
**pg-diff：PostgreSQL模式和数据比较工具** 在数据库管理中，经常需要对不同的数据库实例进行模式（schema）和数据的比对，以便于版本控制、迁移或故障排查。`pg-diff`是一个专为PostgreSQL设计的高效工具，它能够...
深度剖析PostgreSQL慢SQL：原因与优化方案大揭秘
洛阳泰山的博客
06-27 7529
PostgreSQL是一款强大的关系型数据库，但在实际使用过程中，许多用户经常会遇到慢SQL的问题。这些问题不仅会降低数据库性能，还会直接影响业务流程和用户体验。因此，本文将会深入分析PostgreSQL慢SQL的原因和优化方案，帮助用户更好地利用这个优秀的数据库系统。无论你是初学者还是专业开发者，本文都将为你提供实用的技巧和方法，让你的PostgreSQL数据库始终保持高效快速。
postgresql 性能调优
weixin_44313745的博客
08-23 2085
postgresql 性能调优；元祖清理
PostgreSQL调优工具：PGTune
最新发布
weixin_42949219的博客
06-04 206
有些参数可以不填，填写完对应的参数后直接点击生成即可，然后对应修改pgsql数据库的postgresql.conf的配置文件之后重启即可。：总内存大小，若服务器只有数据库直接设置总内存大小，否则设置服务器空闲内存（free -h查看）：数据存储类型（SSD固态硬盘存储，SAN存储，HDD机械硬盘存储）：数据库类型，一般默认即可。
进阶数据库系列（二十三）：PostgreSQL 性能优化
pgdba的博客
10-16 1598
PostgreSQL优化一方面是找出系统的瓶颈，提高PostgreSQL数据库整体的性能； 另一方面，需要合理的结构设计和参数调整，以提高用户操作响应的速度； 同时还要尽可能的节省系统资源，以便系统可以提供更大负荷的服务。
postgresql 调优自带工具pgbehch入门
weixin_30305735的博客
09-04 493
postgresql 调优自带工具pgbehch的使用 一。pgbehch必须安装完才能使用 1.pgbehch安装 1-1.找到安装目录(a.是pg安装时，保存数据的目录 b.用./pgbench --help也可以加以判断) find / -name pgbench cd /data/postgresql-10.9/src/bin/p...
PostgreSQL查询慢sql原因和优化方案
洛阳泰山的博客
08-12 5746
查询慢sql的执行会话，关闭进程。查看数据库后台连接进程查看数据库后台连接进程，但是此条SQL不包含当前查询进程查看当前慢SQL,例如查询执行时间超过1秒的SQL可以使用pg_terminate_backend()终止连接。您必须是超级用户才能使用此功能。这在所有操作系统上都是相同的。
pg-formatter：PostgreSQL SQL语法美化器
02-05
pg格式器 PostgreSQL SQL语法美化器。 注意： 该项目是一个薄包装。 模块的执行依赖于主机系统上可用的Perl。用法import { format} from 'pg-formatter' ;format ( `SELECT foo FROM bar` ) ;组态组态格式默认描述...
PostgreSQL慢SQL调优手册
02-03
**PostgreSQL 慢 SQL 调优手册** 1、**Create Index Directly** 创建合适的索引是优化查询性能的关键。为经常出现在 WHERE 子句中的列创建索引可以显著提升查询速度。例如，如果你的查询频繁地过滤某个特定列，如 ...
迁移：Golang go-pg和PostgreSQLSQL数据库迁移
02-05
Golang和PostgreSQLSQL迁移 该软件包允许您使用在PostgreSQL数据库上运行迁移。 有关详细信息，请参见。 您可能还需要在做出决定之前检查 。 安装 go-pg / migrations需要具有支持的Go版本，并使用导入路径版本...
pg-bump:PostgreSQL极简SQL迁移CLI
05-05
pg-bump 一个自以为是的，极简的PostgreSQL SQL迁移CLI。它是什么？ pg-bump提供对“ Plain SQL”中编写的PostgreSQL数据库模式迁移的命令行管理。 假定a）您正在以及b）您正在。 pg-bump主要用于的可执行文件。 ...
postgreSql性能优化提升技巧指南
热门推荐
zzy7075的专栏
05-28 1万+
百脑汇威客网分享一篇关于postgreSql性能优化，有需要了的朋友可以参考一下。 一、使用EXPLAIN：     PostgreSQL为每个查询都生成一个查询规划，因为选择正确的查询路径对性能的影响是极为关键的。 PostgreSQL本身已经包含了一个规划器用于寻找最优规划，我们可以通过使用EXPLAIN命令来查看规划器为每个查询生成的查询规划。     PostgreSQL中生成的查
Postgresql查询优化相关插件
魂醉的一亩二分地
03-07 944
进行SQL优化之前，我们要找出相应的SQL，如何找，可以通过以下一些插件进行抓取。 pg_stat_statements 可以根据执行SQL的时长找出相关查询语句，具体使用可以参考 https://www.postgresql.org/docs/current/pgstatstatements.html pg_stat_kcache是一个用于检测系统和用户CPU使用比较高的模块。如果你的系统检测CPU使用率比较高，则可以使用该模块来查看是哪些查询导致了这种情况。需要单独下载使用： http://a
PostgreSQL优化技巧：加速大数据处理
qq_28245087的博客
05-29 1818
为了解决这个问题，我们可以使用一些技术来优化它。本文介绍了优化PostgreSQL数据库的一些技巧，例如使用索引、连接池、分区表、复制、预处理语句和分析工具。在优化 Postgre SQL 时，您还应该考虑其他因素，例如缓存大小、数据库架构和网络带宽等。通过使用这些技术，您可以显著提高数据库的性能并更快地处理大量数据。例如，您可以使用 pg_stat_statements 模块来跟踪查询的执行时间。连接池避免了每个新连接都需要建立到数据库的新连接的开销，因此使用连接池可以更快地管理连接。
PostgreSQL实战-10-性能优化
steven的博客
07-16 1622
影响数据库性能的因素： 在硬件层面： CPU IO 内存 网络 在软件层面: 操作系统配置 中间件配置 数据库参数配置 运行在数据库之上的查询和命令 等 ...... 性能检测 系统监控 在进行系统性能优化时， 应当先着眼全局进行分析，再逐步深入到细节。 PostgreSQL 数据库的SQL 服务器应用通常分为 OLTP ...
Postgresql连接状况及优化
白开水的博客
12-23 2336
查看所有连接项目： select * from pg_stat_activity; 登录不上去时，可以选择kill掉idle的进程（不良反应上不可知） select pid from pg_stat_activity where state='idle' 性能优化主要选项 选项 默认值 说明 是否优化 原因 max_connections 100
PostgreSQL数据库调优
Fish Where The Fish Are
03-11 312
官方wiki： [url=https://wiki.postgresql.org/wiki/Using_EXPLAIN]https://wiki.postgresql.org/wiki/Using_EXPLAIN[/url] [url=https://wiki.postgresql.org/wiki/Performance_Optimization]https://wiki.postgresq...
PG性能调优工具大全
eagle89的专栏
08-25 288
PostgreSQL SQL调优指南：性能分析与优化步骤详解【PPT】
同时，我们还需要收集统计信息，包括获取完整的SQL语句、表相关信息和系统资源使用情况等，以帮助更好地分析慢SQL的原因。 在具体的调优过程中，我们还需要注意数据库参数配置信息的获取，包括pg_stat_all_tables、...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值