digoal
2021-09-04
PostgreSQL , Oracle , sql_trace
《PostgreSQL 兼容Oracle sql_trace 10046 10053 方法 - SQL诊断跟踪》
其他:
《PostgreSQL pg_stat_statements AWR 插件 pg_stat_monitor , 过去任何时间段性能分析 [推荐、收藏]》
《PostgreSQL 活跃会话历史记录插件 - pgsentinel 类似performance insight \ Oracle ASH Active Session History》
《PostgreSQL 13 preview - wait event sample - 等待事件统计(插件,hook) - ASH - performance insight》
《PostgreSQL Oracle 兼容性之 - performance insight - AWS performance insight 理念与实现解读 - 珍藏级》
- PARSER STATISTICS
- PARSE ANALYSIS STATISTICS
- parse tree
- REWRITER STATISTICS
- rewritten parse tree
- PLANNER STATISTICS
- plan
- sort STATISTICS
- EXECUTOR STATISTICS
- QUERY PLAN
- actual time
- loops
- mem usage
- Buffers: shared hit
- blocks
- io timing
- 锁
- WAL
- JIT
- 死锁等
详见:
《2019-PostgreSQL 2天体系化培训 - 视频每周更新》
打印到当前会话前台, 同时打印到日志
\set VERBOSITY verbose
set log_min_messages = debug5;
set log_min_error_statement = debug5;
-- set trace_recovery_messages = DEBUG5; # 只能启动前在参数文件中配置
-- set track_commit_timestamp = on; # 只能启动前在参数文件中配置
-- set log_checkpoints = on; # 只能启动前在参数文件中配置
set log_error_verbosity = verbose;
set log_lock_waits = on;
set log_replication_commands = off;
set log_temp_files = 0;
set track_activities = on;
set track_counts = on;
set track_io_timing = on;
set track_wal_io_timing = on;
set track_functions = 'all';
set trace_sort = on;
-- set trace_locks = on; # 要求 LOCK_DEBUG macro 定义
-- set trace_lwlocks = on; # 要求 LOCK_DEBUG macro 定义
-- set trace_userlocks = on; # 要求 LOCK_DEBUG macro 定义
-- set trace_lock_oidmin # 要求 LOCK_DEBUG macro 定义
-- set trace_lock_table # 要求 LOCK_DEBUG macro 定义
-- set debug_deadlocks = on; # 要求 LOCK_DEBUG macro 定义
-- set log_btree_build_stats = on; # 要求 BTREE_BUILD_STATS macro 定义
-- set wal_debug = on; # 要求 WAL_DEBUG macro 定义
-- set jit_debugging_support = on; # 只能启动前在参数文件中配置
set jit_dump_bitcode = on;
set jit_expressions = on;
-- set jit_profiling_support = on; # 只能启动前在参数文件中配置
set jit_tuple_deforming = on;
-- set log_statement_stats = on; # 与log_statement_stats互斥, 可挑选使用
set log_parser_stats = on; -- 与log_statement_stats互斥, 可挑选使用
set log_planner_stats = on; -- 与log_statement_stats互斥, 可挑选使用
set log_executor_stats = on; -- 与log_statement_stats互斥, 可挑选使用
-- set log_autovacuum_min_duration = 0; # 只能启动前在参数文件中配置
set deadlock_timeout = '1s';
set debug_print_parse = on;
set debug_print_rewritten = on;
set debug_print_plan = on;
set debug_pretty_print = on;
set lock_timeout = '1s';
-- set log_recovery_conflict_waits = on; # 只能启动前在参数文件中配置
set client_min_messages = log; -- 可选debug5,...debug1, log, notice, warning, error
例子
postgres=# explain (analyze,verbose,timing,costs,buffers,WAL,SUMMARY) select count(*),relkind from pg_class group by relkind order by count(*) desc limit 1;
LOG: 00000: PARSER STATISTICS
DETAIL: ! system usage stats:
! 0.000101 s user, 0.000069 s system, 0.000168 s elapsed
! [0.005759 s user, 0.003854 s system total]
! 4120 kB max resident size
! 0/0 [0/0] filesystem blocks in/out
! 0/36 [0/1095] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [31/77] messages rcvd/sent
! 0/0 [1/33] voluntary/involuntary context switches
LOCATION: ShowUsage, postgres.c:4888
LOG: 00000: PARSE ANALYSIS STATISTICS
DETAIL: ! system usage stats:
! 0.000597 s user, 0.000211 s system, 0.000809 s elapsed
! [0.006405 s user, 0.004079 s system total]
! 4824 kB max resident size
! 0/0 [0/0] filesystem blocks in/out
! 0/179 [0/1274] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [31/78] messages rcvd/sent
! 0/0 [1/33] voluntary/involuntary context switches
LOCATION: ShowUsage, postgres.c:4888
LOG: 00000: parse tree:
DETAIL: {QUERY
:commandType 5
:querySource 0
:canSetTag true
:utilityStmt ?
:resultRelation 0
:hasAggs false
:hasWindowFuncs false
:hasTargetSRFs false
:hasSubLinks false
:hasDistinctOn false
:hasRecursive false
:hasModifyingCTE false
:hasForUpdate false
:hasRowSecurity false
:isReturn false
:cteList <>
:rtable <>
:jointree <>
:targetList <>
:override 0
:onConflict <>
:returningList <>
:groupClause <>
:groupDistinct false
:groupingSets <>
:havingQual <>
:windowClause <>
:distinctClause <>
:sortClause <>
:limitOffset <>
:limitCount <>
:limitOption 0
:rowMarks <>
:setOperations <>
:constraintDeps <>
:withCheckOptions <>
:stmt_location 0
:stmt_len 144
}
LOCATION: elog_node_display, print.c:85
LOG: 00000: REWRITER STATISTICS
DETAIL: ! system usage stats:
! 0.000000 s user, 0.000000 s system, 0.000001 s elapsed
! [0.006462 s user, 0.004101 s system total]
! 4828 kB max resident size
! 0/0 [0/0] filesystem blocks in/out
! 0/0 [0/1275] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [31/80] messages rcvd/sent
! 0/0 [1/33] voluntary/involuntary context switches
LOCATION: ShowUsage, postgres.c:4888
LOG: 00000: rewritten parse tree:
DETAIL: (
{QUERY
:commandType 5
:querySource 0
:canSetTag true
:utilityStmt ?
:resultRelation 0
:hasAggs false
:hasWindowFuncs false
:hasTargetSRFs false
:hasSubLinks false
:hasDistinctOn false
:hasRecursive false
:hasModifyingCTE false
:hasForUpdate false
:hasRowSecurity false
:isReturn false
:cteList <>
:rtable <>
:jointree <>
:targetList <>
:override 0
:onConflict <>
:returningList <>
:groupClause <>
:groupDistinct false
:groupingSets <>
:havingQual <>
:windowClause <>
:distinctClause <>
:sortClause <>
:limitOffset <>
:limitCount <>
:limitOption 0
:rowMarks <>
:setOperations <>
:constraintDeps <>
:withCheckOptions <>
:stmt_location 0
:stmt_len 144
}
)
LOCATION: elog_node_display, print.c:85
LOG: 00000: PLANNER STATISTICS
DETAIL: ! system usage stats:
! 0.000549 s user, 0.000530 s system, 0.001121 s elapsed
! [0.007130 s user, 0.004662 s system total]
! 5668 kB max resident size
! 0/0 [0/0] filesystem blocks in/out
! 0/191 [0/1486] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [31/82] messages rcvd/sent
! 0/2 [1/35] voluntary/involuntary context switches
LOCATION: ShowUsage, postgres.c:4888
LOG: 00000: plan:
DETAIL: {PLANNEDSTMT
:commandType 1
:queryId 0
:hasReturning false
:hasModifyingCTE false
:canSetTag true
:transientPlan false
:dependsOnRole false
:parallelModeNeeded false
:jitFlags 0
:planTree
{LIMIT
:startup_cost 34.31
:total_cost 34.32
:plan_rows 1
:plan_width 9
:parallel_aware false
:parallel_safe false
:async_capable false
:plan_node_id 0
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 1
:vartype 20
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 0
:varattnosyn 0
:location -1
}
:resno 1
:resname count
:ressortgroupref 1
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 2
:vartype 18
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 17
:location -1
}
:resno 2
:resname relkind
:ressortgroupref 2
:resorigtbl 1259
:resorigcol 17
:resjunk false
}
)
:qual <>
:lefttree
{SORT
:startup_cost 34.31
:total_cost 34.32
:plan_rows 4
:plan_width 9
:parallel_aware false
:parallel_safe false
:async_capable false
:plan_node_id 1
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 1
:vartype 20
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 0
:varattnosyn 0
:location -1
}
:resno 1
:resname count
:ressortgroupref 1
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 2
:vartype 18
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 17
:location -1
}
:resno 2
:resname relkind
:ressortgroupref 2
:resorigtbl 1259
:resorigcol 17
:resjunk false
}
)
:qual <>
:lefttree
{AGG
:startup_cost 34.25
:total_cost 34.29
:plan_rows 4
:plan_width 9
:parallel_aware false
:parallel_safe false
:async_capable false
:plan_node_id 2
:targetlist (
{TARGETENTRY
:expr
{AGGREF
:aggfnoid 2803
:aggtype 20
:aggcollid 0
:inputcollid 0
:aggtranstype 20
:aggargtypes <>
:aggdirectargs <>
:args <>
:aggorder <>
:aggdistinct <>
:aggfilter <>
:aggstar true
:aggvariadic false
:aggkind n
:agglevelsup 0
:aggsplit 0
:aggno 0
:aggtransno 0
:location 66
}
:resno 1
:resname count
:ressortgroupref 1
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 17
:vartype 18
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 0
:varattnosyn 0
:location -1
}
:resno 2
:resname relkind
:ressortgroupref 2
:resorigtbl 1259
:resorigcol 17
:resjunk false
}
)
:qual <>
:lefttree
{SEQSCAN
:startup_cost 0.00
:total_cost 32.17
:plan_rows 417
:plan_width 1
:parallel_aware false
:parallel_safe false
:async_capable false
:plan_node_id 3
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 1
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 1
:location -1
}
:resno 1
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 2
:vartype 19
:vartypmod -1
:varcollid 950
:varlevelsup 0
:varnosyn 1
:varattnosyn 2
:location -1
}
:resno 2
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 3
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 3
:location -1
}
:resno 3
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 4
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 4
:location -1
}
:resno 4
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 5
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 5
:location -1
}
:resno 5
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 6
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 6
:location -1
}
:resno 6
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 7
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 7
:location -1
}
:resno 7
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 8
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 8
:location -1
}
:resno 8
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 9
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 9
:location -1
}
:resno 9
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 10
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 10
:location -1
}
:resno 10
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 11
:vartype 700
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 11
:location -1
}
:resno 11
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 12
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 12
:location -1
}
:resno 12
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 13
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 13
:location -1
}
:resno 13
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 14
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 14
:location -1
}
:resno 14
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 15
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 15
:location -1
}
:resno 15
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 16
:vartype 18
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 16
:location -1
}
:resno 16
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 17
:vartype 18
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 17
:location -1
}
:resno 17
:resname <>
:ressortgroupref 2
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 18
:vartype 21
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 18
:location -1
}
:resno 18
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 19
:vartype 21
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 19
:location -1
}
:resno 19
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 20
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 20
:location -1
}
:resno 20
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 21
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 21
:location -1
}
:resno 21
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 22
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 22
:location -1
}
:resno 22
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 23
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 23
:location -1
}
:resno 23
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 24
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 24
:location -1
}
:resno 24
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 25
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 25
:location -1
}
:resno 25
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 26
:vartype 18
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 26
:location -1
}
:resno 26
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 27
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 27
:location -1
}
:resno 27
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 28
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 28
:location -1
}
:resno 28
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 29
:vartype 28
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 29
:location -1
}
:resno 29
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 30
:vartype 28
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 30
:location -1
}
:resno 30
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 31
:vartype 1034
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 31
:location -1
}
:resno 31
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 32
:vartype 1009
:vartypmod -1
:varcollid 950
:varlevelsup 0
:varnosyn 1
:varattnosyn 32
:location -1
}
:resno 32
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 33
:vartype 194
:vartypmod -1
:varcollid 950
:varlevelsup 0
:varnosyn 1
:varattnosyn 33
:location -1
}
:resno 33
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
)
:qual <>
:lefttree <>
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:scanrelid 1
}
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:aggstrategy 2
:aggsplit 0
:numCols 1
:grpColIdx 17
:grpOperators 92
:grpCollations 0
:numGroups 4
:transitionSpace 0
:aggParams (b)
:groupingSets <>
:chain <>
}
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:numCols 1
:sortColIdx 1
:sortOperators 413
:collations 0
:nullsFirst true
}
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:limitOffset <>
:limitCount
{CONST
:consttype 20
:consttypmod -1
:constcollid 0
:constlen 8
:constbyval true
:constisnull false
:location -1
:constvalue 8 [ 1 0 0 0 0 0 0 0 ]
}
:limitOption 0
:uniqNumCols 0
:uniqColIdx
:uniqOperators
:uniqCollations
}
:rtable (
{RTE
:alias <>
:eref
{ALIAS
:aliasname pg_class
:colnames ("oid" "relname" "relnamespace" "reltype" "reloftype" "relo
wner" "relam" "relfilenode" "reltablespace" "relpages" "reltuples" "r
elallvisible" "reltoastrelid" "relhasindex" "relisshared" "relpersist
ence" "relkind" "relnatts" "relchecks" "relhasrules" "relhastriggers"
"relhassubclass" "relrowsecurity" "relforcerowsecurity" "relispopula
ted" "relreplident" "relispartition" "relrewrite" "relfrozenxid" "rel
minmxid" "relacl" "reloptions" "relpartbound")
}
:rtekind 0
:relid 1259
:relkind r
:rellockmode 1
:tablesample <>
:lateral false
:inh false
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 24)
:insertedCols (b)
:updatedCols (b)
:extraUpdatedCols (b)
:securityQuals <>
}
)
:resultRelations <>
:appendRelations <>
:subplans <>
:rewindPlanIDs (b)
:rowMarks <>
:relationOids (o 1259)
:invalItems <>
:paramExecTypes <>
:utilityStmt <>
:stmt_location 0
:stmt_len 0
}
LOCATION: elog_node_display, print.c:85
LOG: 00000: begin tuple sort: nkeys = 1, workMem = 4096, randomAccess = f
LOCATION: tuplesort_begin_heap, tuplesort.c:916
LOG: 00000: switching to bounded heapsort at 3 tuples: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOCATION: puttuple_common, tuplesort.c:1929
LOG: 00000: performsort of worker -1 starting: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOCATION: tuplesort_performsort, tuplesort.c:2047
LOG: 00000: performsort of worker -1 done: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOCATION: tuplesort_performsort, tuplesort.c:2136
LOG: 00000: internal sort of worker -1 ended, 25 KB used: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOCATION: tuplesort_free, tuplesort.c:1424
LOG: 00000: EXECUTOR STATISTICS
DETAIL: ! system usage stats:
! 0.001761 s user, 0.000918 s system, 0.002775 s elapsed
! [0.008342 s user, 0.005050 s system total]
! 6464 kB max resident size
! 0/0 [0/0] filesystem blocks in/out
! 0/390 [0/1685] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/12 [31/94] messages rcvd/sent
! 5/7 [6/40] voluntary/involuntary context switches
LOCATION: ShowUsage, postgres.c:4888
Limit (cost=34.31..34.32 rows=1 width=9) (actual time=0.572..0.573 rows=1 loops=1)
Output: (count(*)), relkind
Buffers: shared hit=31
-> Sort (cost=34.31..34.32 rows=4 width=9) (actual time=0.571..0.571 rows=1 loops=1)
Output: (count(*)), relkind
Sort Key: (count(*)) DESC
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=31
-> HashAggregate (cost=34.25..34.29 rows=4 width=9) (actual time=0.447..0.448 rows=4 loops=1)
Output: count(*), relkind
Group Key: pg_class.relkind
Batches: 1 Memory Usage: 24kB
Buffers: shared hit=28
-> Seq Scan on pg_catalog.pg_class (cost=0.00..32.17 rows=417 width=1) (actual time=0.017..0.215 rows=412 loops=1)
Output: oid, relname, relnamespace, reltype, reloftype, relowner, relam, relfilenode, reltablespace, relpages, reltuples, relallvisible, reltoastrelid, relhasindex, relisshared, relpersistence, relkind, relnatts, relchecks, relhasrules, relhastriggers, relhassubclass, relrowsecurity, relforcerowsecurity, relispopulated, relreplident, relispartition, relrewrite, relfrozenxid, relminmxid, relacl, reloptions, relpartbound
Buffers: shared hit=28
Planning:
Buffers: shared hit=96
Planning Time: 1.844 ms
Execution Time: 0.757 ms
(20 rows)
如果想在SQL执行后立即打印跟踪信息, 可以使用auto_explain模块.
\set VERBOSITY verbose
set log_min_messages = debug5;
set log_min_error_statement = debug5;
-- set trace_recovery_messages = DEBUG5;
-- set track_commit_timestamp = on;
-- set log_checkpoints = on;
set log_error_verbosity = verbose;
set log_lock_waits = on;
set log_replication_commands = off;
set log_temp_files = 0;
set track_activities = on;
set track_counts = on;
set track_io_timing = on;
set track_wal_io_timing = on;
set track_functions = 'all';
set trace_sort = on;
-- set trace_locks = on; # 要求 LOCK_DEBUG macro 定义
-- set trace_lwlocks = on; # 要求 LOCK_DEBUG macro 定义
-- set trace_userlocks = on; # 要求 LOCK_DEBUG macro 定义
-- set trace_lock_oidmin # 要求 LOCK_DEBUG macro 定义
-- set trace_lock_table # 要求 LOCK_DEBUG macro 定义
-- set debug_deadlocks = on; # 要求 LOCK_DEBUG macro 定义
-- set log_btree_build_stats = on; # 要求 BTREE_BUILD_STATS macro 定义
-- set wal_debug = on; # 要求 WAL_DEBUG macro 定义
-- set jit_debugging_support = on; # 只能启动前在参数中配置
set jit_dump_bitcode = on;
set jit_expressions = on;
-- set jit_profiling_support = on; # 只能启动前在参数中配置
set jit_tuple_deforming = on;
-- set log_statement_stats = on; # 与log_statement_stats互斥, 可挑选使用
set log_parser_stats = on; -- 与log_statement_stats互斥, 可挑选使用
set log_planner_stats = on; -- 与log_statement_stats互斥, 可挑选使用
set log_executor_stats = on; -- 与log_statement_stats互斥, 可挑选使用
-- set log_autovacuum_min_duration = 0; # 只能启动前在参数中配置
set deadlock_timeout = '1s';
set debug_print_parse = on;
set debug_print_rewritten = on;
set debug_print_plan = on;
set debug_pretty_print = on;
set lock_timeout = '1s';
-- set log_recovery_conflict_waits = on; # 只能启动前在参数中配置
load 'auto_explain';
set auto_explain.log_analyze = on;
set auto_explain.log_level = log; -- 可选 DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, and LOG
set auto_explain.log_settings = on;
set auto_explain.log_verbose = on;
set auto_explain.log_buffers = on;
set auto_explain.log_min_duration = 0;
set auto_explain.log_timing = on;
set auto_explain.log_wal = on;
set auto_explain.log_format = text;
set auto_explain.log_nested_statements = on;
set auto_explain.log_triggers =on;
set auto_explain.sample_rate =1;
set client_min_messages = log; -- 可选debug5,...debug1, log, notice, warning, error
postgres=# select count(*),relkind from pg_class group by relkind order by count(*) desc limit 1;
LOG: 00000: PARSER STATISTICS
DETAIL: ! system usage stats:
! 0.000080 s user, 0.000036 s system, 0.000115 s elapsed
! [0.006983 s user, 0.003714 s system total]
! 4068 kB max resident size
! 0/0 [0/0] filesystem blocks in/out
! 0/28 [0/1084] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [40/51] messages rcvd/sent
! 0/0 [0/46] voluntary/involuntary context switches
LOCATION: ShowUsage, postgres.c:4888
LOG: 00000: PARSE ANALYSIS STATISTICS
DETAIL: ! system usage stats:
! 0.000744 s user, 0.000237 s system, 0.000982 s elapsed
! [0.007761 s user, 0.003963 s system total]
! 4788 kB max resident size
! 0/0 [0/0] filesystem blocks in/out
! 0/179 [0/1264] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [40/52] messages rcvd/sent
! 0/0 [0/46] voluntary/involuntary context switches
LOCATION: ShowUsage, postgres.c:4888
LOG: 00000: parse tree:
DETAIL: {QUERY
:commandType 1
:querySource 0
:canSetTag true
:utilityStmt <>
:resultRelation 0
:hasAggs true
:hasWindowFuncs false
:hasTargetSRFs false
:hasSubLinks false
:hasDistinctOn false
:hasRecursive false
:hasModifyingCTE false
:hasForUpdate false
:hasRowSecurity false
:isReturn false
:cteList <>
:rtable (
{RTE
:alias <>
:eref
{ALIAS
:aliasname pg_class
:colnames ("oid" "relname" "relnamespace" "reltype" "reloftype" "relo
wner" "relam" "relfilenode" "reltablespace" "relpages" "reltuples" "r
elallvisible" "reltoastrelid" "relhasindex" "relisshared" "relpersist
ence" "relkind" "relnatts" "relchecks" "relhasrules" "relhastriggers"
"relhassubclass" "relrowsecurity" "relforcerowsecurity" "relispopula
ted" "relreplident" "relispartition" "relrewrite" "relfrozenxid" "rel
minmxid" "relacl" "reloptions" "relpartbound")
}
:rtekind 0
:relid 1259
:relkind r
:rellockmode 1
:tablesample <>
:lateral false
:inh true
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 24)
:insertedCols (b)
:updatedCols (b)
:extraUpdatedCols (b)
:securityQuals <>
}
)
:jointree
{FROMEXPR
:fromlist (
{RANGETBLREF
:rtindex 1
}
)
:quals <>
}
:targetList (
{TARGETENTRY
:expr
{AGGREF
:aggfnoid 2803
:aggtype 20
:aggcollid 0
:inputcollid 0
:aggtranstype 0
:aggargtypes <>
:aggdirectargs <>
:args <>
:aggorder <>
:aggdistinct <>
:aggfilter <>
:aggstar true
:aggvariadic false
:aggkind n
:agglevelsup 0
:aggsplit 0
:aggno -1
:aggtransno -1
:location 7
}
:resno 1
:resname count
:ressortgroupref 1
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 17
:vartype 18
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 17
:location 16
}
:resno 2
:resname relkind
:ressortgroupref 2
:resorigtbl 1259
:resorigcol 17
:resjunk false
}
)
:override 0
:onConflict <>
:returningList <>
:groupClause (
{SORTGROUPCLAUSE
:tleSortGroupRef 2
:eqop 92
:sortop 631
:nulls_first false
:hashable true
}
)
:groupDistinct false
:groupingSets <>
:havingQual <>
:windowClause <>
:distinctClause <>
:sortClause (
{SORTGROUPCLAUSE
:tleSortGroupRef 1
:eqop 410
:sortop 413
:nulls_first true
:hashable true
}
)
:limitOffset <>
:limitCount
{FUNCEXPR
:funcid 481
:funcresulttype 20
:funcretset false
:funcvariadic false
:funcformat 2
:funccollid 0
:inputcollid 0
:args (
{CONST
:consttype 23
:consttypmod -1
:constcollid 0
:constlen 4
:constbyval true
:constisnull false
:location 84
:constvalue 4 [ 1 0 0 0 0 0 0 0 ]
}
)
:location -1
}
:limitOption 0
:rowMarks <>
:setOperations <>
:constraintDeps <>
:withCheckOptions <>
:stmt_location 0
:stmt_len 85
}
LOCATION: elog_node_display, print.c:85
LOG: 00000: REWRITER STATISTICS
DETAIL: ! system usage stats:
! 0.000015 s user, 0.000008 s system, 0.000022 s elapsed
! [0.007944 s user, 0.004024 s system total]
! 4860 kB max resident size
! 0/0 [0/0] filesystem blocks in/out
! 0/6 [0/1282] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [40/54] messages rcvd/sent
! 0/0 [0/46] voluntary/involuntary context switches
LOCATION: ShowUsage, postgres.c:4888
LOG: 00000: rewritten parse tree:
DETAIL: (
{QUERY
:commandType 1
:querySource 0
:canSetTag true
:utilityStmt <>
:resultRelation 0
:hasAggs true
:hasWindowFuncs false
:hasTargetSRFs false
:hasSubLinks false
:hasDistinctOn false
:hasRecursive false
:hasModifyingCTE false
:hasForUpdate false
:hasRowSecurity false
:isReturn false
:cteList <>
:rtable (
{RTE
:alias <>
:eref
{ALIAS
:aliasname pg_class
:colnames ("oid" "relname" "relnamespace" "reltype" "reloftype" "relo
wner" "relam" "relfilenode" "reltablespace" "relpages" "reltuples" "r
elallvisible" "reltoastrelid" "relhasindex" "relisshared" "relpersist
ence" "relkind" "relnatts" "relchecks" "relhasrules" "relhastriggers"
"relhassubclass" "relrowsecurity" "relforcerowsecurity" "relispopula
ted" "relreplident" "relispartition" "relrewrite" "relfrozenxid" "rel
minmxid" "relacl" "reloptions" "relpartbound")
}
:rtekind 0
:relid 1259
:relkind r
:rellockmode 1
:tablesample <>
:lateral false
:inh true
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 24)
:insertedCols (b)
:updatedCols (b)
:extraUpdatedCols (b)
:securityQuals <>
}
)
:jointree
{FROMEXPR
:fromlist (
{RANGETBLREF
:rtindex 1
}
)
:quals <>
}
:targetList (
{TARGETENTRY
:expr
{AGGREF
:aggfnoid 2803
:aggtype 20
:aggcollid 0
:inputcollid 0
:aggtranstype 0
:aggargtypes <>
:aggdirectargs <>
:args <>
:aggorder <>
:aggdistinct <>
:aggfilter <>
:aggstar true
:aggvariadic false
:aggkind n
:agglevelsup 0
:aggsplit 0
:aggno -1
:aggtransno -1
:location 7
}
:resno 1
:resname count
:ressortgroupref 1
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 17
:vartype 18
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 17
:location 16
}
:resno 2
:resname relkind
:ressortgroupref 2
:resorigtbl 1259
:resorigcol 17
:resjunk false
}
)
:override 0
:onConflict <>
:returningList <>
:groupClause (
{SORTGROUPCLAUSE
:tleSortGroupRef 2
:eqop 92
:sortop 631
:nulls_first false
:hashable true
}
)
:groupDistinct false
:groupingSets <>
:havingQual <>
:windowClause <>
:distinctClause <>
:sortClause (
{SORTGROUPCLAUSE
:tleSortGroupRef 1
:eqop 410
:sortop 413
:nulls_first true
:hashable true
}
)
:limitOffset <>
:limitCount
{FUNCEXPR
:funcid 481
:funcresulttype 20
:funcretset false
:funcvariadic false
:funcformat 2
:funccollid 0
:inputcollid 0
:args (
{CONST
:consttype 23
:consttypmod -1
:constcollid 0
:constlen 4
:constbyval true
:constisnull false
:location 84
:constvalue 4 [ 1 0 0 0 0 0 0 0 ]
}
)
:location -1
}
:limitOption 0
:rowMarks <>
:setOperations <>
:constraintDeps <>
:withCheckOptions <>
:stmt_location 0
:stmt_len 85
}
)
LOCATION: elog_node_display, print.c:85
LOG: 00000: PLANNER STATISTICS
DETAIL: ! system usage stats:
! 0.000827 s user, 0.000882 s system, 0.001712 s elapsed
! [0.008901 s user, 0.004930 s system total]
! 5608 kB max resident size
! 0/0 [0/0] filesystem blocks in/out
! 0/188 [0/1470] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [40/56] messages rcvd/sent
! 0/0 [0/46] voluntary/involuntary context switches
LOCATION: ShowUsage, postgres.c:4888
LOG: 00000: plan:
DETAIL: {PLANNEDSTMT
:commandType 1
:queryId 0
:hasReturning false
:hasModifyingCTE false
:canSetTag true
:transientPlan false
:dependsOnRole false
:parallelModeNeeded false
:jitFlags 0
:planTree
{LIMIT
:startup_cost 34.31
:total_cost 34.32
:plan_rows 1
:plan_width 9
:parallel_aware false
:parallel_safe false
:async_capable false
:plan_node_id 0
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 1
:vartype 20
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 0
:varattnosyn 0
:location -1
}
:resno 1
:resname count
:ressortgroupref 1
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 2
:vartype 18
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 17
:location -1
}
:resno 2
:resname relkind
:ressortgroupref 2
:resorigtbl 1259
:resorigcol 17
:resjunk false
}
)
:qual <>
:lefttree
{SORT
:startup_cost 34.31
:total_cost 34.32
:plan_rows 4
:plan_width 9
:parallel_aware false
:parallel_safe false
:async_capable false
:plan_node_id 1
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 1
:vartype 20
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 0
:varattnosyn 0
:location -1
}
:resno 1
:resname count
:ressortgroupref 1
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 2
:vartype 18
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 17
:location -1
}
:resno 2
:resname relkind
:ressortgroupref 2
:resorigtbl 1259
:resorigcol 17
:resjunk false
}
)
:qual <>
:lefttree
{AGG
:startup_cost 34.25
:total_cost 34.29
:plan_rows 4
:plan_width 9
:parallel_aware false
:parallel_safe false
:async_capable false
:plan_node_id 2
:targetlist (
{TARGETENTRY
:expr
{AGGREF
:aggfnoid 2803
:aggtype 20
:aggcollid 0
:inputcollid 0
:aggtranstype 20
:aggargtypes <>
:aggdirectargs <>
:args <>
:aggorder <>
:aggdistinct <>
:aggfilter <>
:aggstar true
:aggvariadic false
:aggkind n
:agglevelsup 0
:aggsplit 0
:aggno 0
:aggtransno 0
:location 7
}
:resno 1
:resname count
:ressortgroupref 1
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 17
:vartype 18
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 0
:varattnosyn 0
:location -1
}
:resno 2
:resname relkind
:ressortgroupref 2
:resorigtbl 1259
:resorigcol 17
:resjunk false
}
)
:qual <>
:lefttree
{SEQSCAN
:startup_cost 0.00
:total_cost 32.17
:plan_rows 417
:plan_width 1
:parallel_aware false
:parallel_safe false
:async_capable false
:plan_node_id 3
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 1
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 1
:location -1
}
:resno 1
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 2
:vartype 19
:vartypmod -1
:varcollid 950
:varlevelsup 0
:varnosyn 1
:varattnosyn 2
:location -1
}
:resno 2
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 3
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 3
:location -1
}
:resno 3
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 4
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 4
:location -1
}
:resno 4
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 5
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 5
:location -1
}
:resno 5
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 6
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 6
:location -1
}
:resno 6
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 7
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 7
:location -1
}
:resno 7
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 8
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 8
:location -1
}
:resno 8
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 9
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 9
:location -1
}
:resno 9
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 10
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 10
:location -1
}
:resno 10
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 11
:vartype 700
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 11
:location -1
}
:resno 11
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 12
:vartype 23
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 12
:location -1
}
:resno 12
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 13
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 13
:location -1
}
:resno 13
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 14
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 14
:location -1
}
:resno 14
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 15
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 15
:location -1
}
:resno 15
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 16
:vartype 18
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 16
:location -1
}
:resno 16
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 17
:vartype 18
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 17
:location -1
}
:resno 17
:resname <>
:ressortgroupref 2
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 18
:vartype 21
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 18
:location -1
}
:resno 18
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 19
:vartype 21
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 19
:location -1
}
:resno 19
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 20
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 20
:location -1
}
:resno 20
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 21
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 21
:location -1
}
:resno 21
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 22
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 22
:location -1
}
:resno 22
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 23
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 23
:location -1
}
:resno 23
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 24
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 24
:location -1
}
:resno 24
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 25
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 25
:location -1
}
:resno 25
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 26
:vartype 18
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 26
:location -1
}
:resno 26
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 27
:vartype 16
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 27
:location -1
}
:resno 27
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 28
:vartype 26
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 28
:location -1
}
:resno 28
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 29
:vartype 28
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 29
:location -1
}
:resno 29
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 30
:vartype 28
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 30
:location -1
}
:resno 30
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 31
:vartype 1034
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnosyn 1
:varattnosyn 31
:location -1
}
:resno 31
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 32
:vartype 1009
:vartypmod -1
:varcollid 950
:varlevelsup 0
:varnosyn 1
:varattnosyn 32
:location -1
}
:resno 32
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 33
:vartype 194
:vartypmod -1
:varcollid 950
:varlevelsup 0
:varnosyn 1
:varattnosyn 33
:location -1
}
:resno 33
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
)
:qual <>
:lefttree <>
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:scanrelid 1
}
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:aggstrategy 2
:aggsplit 0
:numCols 1
:grpColIdx 17
:grpOperators 92
:grpCollations 0
:numGroups 4
:transitionSpace 0
:aggParams (b)
:groupingSets <>
:chain <>
}
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:numCols 1
:sortColIdx 1
:sortOperators 413
:collations 0
:nullsFirst true
}
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:limitOffset <>
:limitCount
{CONST
:consttype 20
:consttypmod -1
:constcollid 0
:constlen 8
:constbyval true
:constisnull false
:location -1
:constvalue 8 [ 1 0 0 0 0 0 0 0 ]
}
:limitOption 0
:uniqNumCols 0
:uniqColIdx
:uniqOperators
:uniqCollations
}
:rtable (
{RTE
:alias <>
:eref
{ALIAS
:aliasname pg_class
:colnames ("oid" "relname" "relnamespace" "reltype" "reloftype" "relo
wner" "relam" "relfilenode" "reltablespace" "relpages" "reltuples" "r
elallvisible" "reltoastrelid" "relhasindex" "relisshared" "relpersist
ence" "relkind" "relnatts" "relchecks" "relhasrules" "relhastriggers"
"relhassubclass" "relrowsecurity" "relforcerowsecurity" "relispopula
ted" "relreplident" "relispartition" "relrewrite" "relfrozenxid" "rel
minmxid" "relacl" "reloptions" "relpartbound")
}
:rtekind 0
:relid 1259
:relkind r
:rellockmode 1
:tablesample <>
:lateral false
:inh false
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 24)
:insertedCols (b)
:updatedCols (b)
:extraUpdatedCols (b)
:securityQuals <>
}
)
:resultRelations <>
:appendRelations <>
:subplans <>
:rewindPlanIDs (b)
:rowMarks <>
:relationOids (o 1259)
:invalItems <>
:paramExecTypes <>
:utilityStmt <>
:stmt_location 0
:stmt_len 85
}
LOCATION: elog_node_display, print.c:85
LOG: 00000: begin tuple sort: nkeys = 1, workMem = 4096, randomAccess = f
LOCATION: tuplesort_begin_heap, tuplesort.c:916
LOG: 00000: switching to bounded heapsort at 3 tuples: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOCATION: puttuple_common, tuplesort.c:1929
LOG: 00000: performsort of worker -1 starting: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOCATION: tuplesort_performsort, tuplesort.c:2047
LOG: 00000: performsort of worker -1 done: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOCATION: tuplesort_performsort, tuplesort.c:2136
LOG: 00000: EXECUTOR STATISTICS
DETAIL: ! system usage stats:
! 0.000373 s user, 0.000092 s system, 0.000464 s elapsed
! [0.010056 s user, 0.005361 s system total]
! 6248 kB max resident size
! 0/0 [0/0] filesystem blocks in/out
! 0/75 [0/1632] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/4 [40/68] messages rcvd/sent
! 0/0 [0/51] voluntary/involuntary context switches
LOCATION: ShowUsage, postgres.c:4888
LOG: 00000: duration: 0.445 ms plan:
Query Text: select count(*),relkind from pg_class group by relkind order by count(*) desc limit 1;
Limit (cost=34.31..34.32 rows=1 width=9) (actual time=0.436..0.438 rows=1 loops=1)
Output: (count(*)), relkind
Buffers: shared hit=31
-> Sort (cost=34.31..34.32 rows=4 width=9) (actual time=0.435..0.436 rows=1 loops=1)
Output: (count(*)), relkind
Sort Key: (count(*)) DESC
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=31
-> HashAggregate (cost=34.25..34.29 rows=4 width=9) (actual time=0.338..0.339 rows=4 loops=1)
Output: count(*), relkind
Group Key: pg_class.relkind
Batches: 1 Memory Usage: 24kB
Buffers: shared hit=28
-> Seq Scan on pg_catalog.pg_class (cost=0.00..32.17 rows=417 width=1) (actual time=0.021..0.175 rows=412 loops=1)
Output: oid, relname, relnamespace, reltype, reloftype, relowner, relam, relfilenode, reltablespace, relpages, reltuples, relallvisible, reltoastrelid, relhasindex, relisshared, relpersistence, relkind, relnatts, relchecks, relhasrules, relhastriggers, relhassubclass, relrowsecurity, relforcerowsecurity, relispopulated, relreplident, relispartition, relrewrite, relfrozenxid, relminmxid, relacl, reloptions, relpartbound
Buffers: shared hit=28
Settings: max_parallel_workers_per_gather = '0', random_page_cost = '1.1'
LOCATION: explain_ExecutorEnd, auto_explain.c:433
LOG: 00000: internal sort of worker -1 ended, 25 KB used: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOCATION: tuplesort_free, tuplesort.c:1424
count | relkind
-------+---------
161 | i
(1 row)
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
