Understanding how PostgreSQL processes a query internally can help database administrators and developers troubleshoot performance issues, analyze query behavior, and gain insight into the database engine. PostgreSQL provides some configuration parameters that allow it to print internal query processing stages directly into the server log.
This article explains how to use the following PostgreSQL debugging parameters:
- debug_print_parse
- debug_print_rewritten
- debug_print_plan
- debug_pretty_print
By enabling these parameters, you can observe how PostgreSQL transforms an SQL statement step by step before executing it.
Locating the PostgreSQL Configuration File
First, identify the location of the PostgreSQL configuration file.
We can identify the path of the configuration files in two ways
pg_lscluster
Result
Ver Cluster Port Status Owner Data directory Log file
18 main 5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log
The other way is, just enter the psql terminal and then use this show command
SHOW config_file;
Example result:
config_file
-----------------------------------------
/etc/postgresql/18/main/postgresql.conf
We can also change the value of these debug print parameters in two ways
Manually open the PostgreSQL configuration file and then change the value of the parameter, and then restart the server or execute alter commands inside the psql terminal like this:
odoo_test=# show debug_print_plan;
debug_print_plan
------------------
off
(1 row)
odoo_test=# alter system set debug_print_plan = 'True';
ALTER SYSTEM
odoo_test=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
odoo_test=# show debug_print_plan;
debug_print_plan
------------------
on
(1 row)
Open the configuration file using a text editor:
sudo nano /etc/postgresql/18/main/postgresql.conf
Search for the debug parameters:
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
These parameters are disabled by default except debug_pretty_print.
Verifying the Current Parameter Values
You can check the current values of these parameters using SQL.
SHOW debug_print_parse;
SHOW debug_print_rewritten;
SHOW debug_print_plan;
SHOW debug_pretty_print;
Example output:
debug_print_parse
-------------------
off
(1 row)
debug_print_rewritten
-----------------------
off
(1 row)
debug_print_plan
------------------
off
(1 row)
debug_pretty_print
--------------------
on
(1 row)
Monitoring PostgreSQL Logs
To observe what PostgreSQL prints during query processing, monitor the server log file.
Example command:
tail -f /var/log/postgresql/postgresql-18-main.log
When debugging parameters are enabled, PostgreSQL prints internal query structures in this log file.
Enabling Debug Parameters
You can enable these parameters dynamically using the ALTER SYSTEM command.
Example:
ALTER SYSTEM SET debug_print_parse = 'on';
ALTER SYSTEM SET debug_print_rewritten = 'on';
ALTER SYSTEM SET debug_print_plan = 'on';
Reload the configuration:
SELECT pg_reload_conf();
Once enabled, PostgreSQL begins printing internal query processing information into the log file.
Understanding PostgreSQL Query Processing Phases
When PostgreSQL receives a SQL query, it does not execute it immediately. The database processes the query through several internal stages.
The debug parameters allow you to observe three important phases:
- Parsing
- Query Rewriting
- Query Planning
Phase 1: Query Parsing
The first step in query processing is parsing.
During this stage, PostgreSQL checks the syntax of the SQL query and converts it into an internal representation called a parse tree.
When debug_print_parse is enabled, PostgreSQL prints this parse tree to the log.
Example log output:
2026-03-07 14:06:40.337 IST [257500] odoouser@odoo_test main_serverSTATEMENT: UPDATE "bus_presence"
SET "last_poll" = "__tmp"."last_poll"::timestamp, "last_presence" = "__tmp"."last_presence"::timestamp
FROM (VALUES (1, '2026-03-07 08:36:40', '2026-03-07 08:36:39.992000')) AS "__tmp"("id", "last_poll", "last_presence")
WHERE "bus_presence"."id" = "__tmp"."id"
2026-03-07 14:06:40.337 IST [257500] odoouser@odoo_test main_serverLOG: parse tree:
2026-03-07 14:06:40.337 IST [257500] odoouser@odoo_test main_serverDETAIL: {QUERY
:commandType 6
:querySource 0
:canSetTag true
:utilityStmt
{TRANSACTIONSTMT
:kind 2
:options <>
:savepoint_name <>
:gid <>
:chain false
:location -1
}
:resultRelation 0
:hasAggs false
:hasWindowFuncs false
:hasTargetSRFs false
:hasSubLinks false
:hasDistinctOn false
:hasRecursive false
:hasModifyingCTE false
:hasForUpdate false
:hasRowSecurity false
:hasGroupRTE false
:isReturn false
:cteList <>
:rtable <>
:rteperminfos <>
:jointree <>
:mergeActionList <>
:mergeTargetRelation 0
:mergeJoinCondition <>
:targetList <>
:override 0
:onConflict <>
:returningOldAlias <>
:returningNewAlias <>
:returningList <>
:groupClause <>
:groupDistinct false
:groupingSets <>
:havingQual <>
:windowClause <>
:distinctClause <>
:sortClause <>
:limitOffset <>
:limitCount <>
:limitOption 0
:rowMarks <>
:setOperations <>
:constraintDeps <>
:withCheckOptions <>
:stmt_location 0
:stmt_len 0
}
The parse tree contains structured information about the query, including:
- command type (SELECT, UPDATE, INSERT, DELETE)
- referenced tables
- expressions
- conditions
- target columns
This stage verifies that the SQL syntax is valid and converts the query into a format PostgreSQL can process internally.
Phase 2: Query Rewriting
After parsing, the query enters the rewriting stage.
In this phase, PostgreSQL modifies the parse tree before planning the execution. This step is mainly used for:
- applying rules
- expanding views
- handling INSTEAD OF rules
- processing query transformations
When debug_print_rewritten is enabled, PostgreSQL logs the rewritten query tree.
Example log entry:
2026-03-07 14:06:40.337 IST [257500] odoouser@odoo_test main_serverLOG: rewritten parse tree:
2026-03-07 14:06:40.337 IST [257500] odoouser@odoo_test main_serverDETAIL: (
{QUERY
:commandType 6
:querySource 0
:canSetTag true
:utilityStmt
{TRANSACTIONSTMT
:kind 2
:options <>
:savepoint_name <>
:gid <>
:chain false
:location -1
}
:resultRelation 0
:hasAggs false
:hasWindowFuncs false
:hasTargetSRFs false
:hasSubLinks false
:hasDistinctOn false
:hasRecursive false
:hasModifyingCTE false
:hasForUpdate false
:hasRowSecurity false
:hasGroupRTE false
:isReturn false
:cteList <>
:rtable <>
:rteperminfos <>
:jointree <>
:mergeActionList <>
:mergeTargetRelation 0
:mergeJoinCondition <>
:targetList <>
:override 0
:onConflict <>
:returningOldAlias <>
:returningNewAlias <>
:returningList <>
:groupClause <>
:groupDistinct false
:groupingSets <>
:havingQual <>
:windowClause <>
:distinctClause <>
:sortClause <>
:limitOffset <>
:limitCount <>
:limitOption 0
:rowMarks <>
:setOperations <>
:constraintDeps <>
:withCheckOptions <>
:stmt_location 0
:stmt_len 0
}
)
The rewritten tree may look similar to the original parse tree, but it can differ if rules or views modify the query.
For example:
- queries involving views are expanded into the underlying base tables
- rules can transform operations such as INSERT or UPDATE
This stage ensures that PostgreSQL executes the logically correct version of the query.
Phase 3: Query Planning
After rewriting, PostgreSQL generates an execution plan.
This phase determines how PostgreSQL will execute the query efficiently.
When debug_print_plan is enabled, PostgreSQL prints the internal execution plan structure.
Example log output:
2026-03-07 14:10:58.725 IST [261654] odoouser@odoo_test main_serverLOG: plan:
2026-03-07 14:10:58.725 IST [261654] odoouser@odoo_test main_serverDETAIL: {PLANNEDSTMT :commandType 2 :queryId -2942479215626857412 :planId 0
:hasReturning false :hasModifyingCTE false :canSetTag true :transientPlan
false :dependsOnRole false :parallelModeNeeded false :jitFlags 0 :planTree
{MODIFYTABLE :plan.disabled_nodes 0 :plan.startup_cost 0 :plan.total_cost
1.0174999999999998 :plan.plan_rows 0 :plan.plan_width 0 :plan.parallel_aware
false :plan.parallel_safe false :plan.async_capable false :plan.plan_node_id 0
:plan.targetlist <> :plan.qual <> :plan.lefttree {SEQSCAN
:scan.plan.disabled_nodes 0 :scan.plan.startup_cost 0 :scan.plan.total_cost
1.0174999999999998 :scan.plan.plan_rows 1 :scan.plan.plan_width 14
:scan.plan.parallel_aware false :scan.plan.parallel_safe false
:scan.plan.async_capable false :scan.plan.plan_node_id 1 :scan.plan.targetlist
({TARGETENTRY :expr {COERCEVIAIO :arg {CONST :consttype 2275 :consttypmod -1
:constcollid 0 :constlen -2 :constbyval false :constisnull false :location -1
:constvalue 27 [ 50 48 50 54 45 48 51 45 48 55 32 48 56 58 52 48 58 53 55 46
48 55 56 48 48 48 0 ]} :resulttype 1114 :resultcollid 0 :coerceformat 1
:location 88} :resno 1 :resname last_presence :ressortgroupref 0 :resorigtbl 0
:resorigcol 0 :resjunk false} {TARGETENTRY :expr {VAR :varno 1 :varattno -1
:vartype 27 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0
:varreturningtype 0 :varnosyn 1 :varattnosyn -1 :location -1} :resno 2
:resname ctid :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk true})
:scan.plan.qual ({OPEXPR :opno 96 :opfuncid 65 :opresulttype 16 :opretset
false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 1 :vartype 23
:vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0
:varreturningtype 0 :varnosyn 1 :varattnosyn 1 :location 228} {CONST
:consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true
:constisnull false :location 134 :constvalue 4 [ 1 0 0 0 0 0 0 0 ]}) :location
248}) :scan.plan.lefttree <> :scan.plan.righttree <> :scan.plan.initPlan <>
:scan.plan.extParam (b 0) :scan.plan.allParam (b 0) :scan.scanrelid 1}
:plan.righttree <> :plan.initPlan <> :plan.extParam (b) :plan.allParam (b)
:operation 2 :canSetTag true :nominalRelation 1 :rootRelation 0
:partColsUpdated false :resultRelations (i 1) :updateColnosLists ((i 5))
:withCheckOptionLists <> :returningOldAlias <> :returningNewAlias <>
:returningLists <> :fdwPrivLists (<>) :fdwDirectModifyPlans (b) :rowMarks <>
:epqParam 0 :onConflictAction 0 :arbiterIndexes <> :onConflictSet <>
:onConflictCols <> :onConflictWhere <> :exclRelRTI 0 :exclRelTlist <>
:mergeActionLists <> :mergeJoinConditions <>} :partPruneInfos <> :rtable
({RANGETBLENTRY :alias <> :eref {ALIAS :aliasname bus_presence :colnames ("id"
"user_id" "status" "last_poll" "last_presence" "guest_id")} :rtekind 0 :relid
332035 :inh false :relkind r :rellockmode 3 :perminfoindex 1 :tablesample <>
:lateral false :inFromCl false :securityQuals <>} {RANGETBLENTRY :alias {ALIAS
:aliasname __tmp :colnames ("id" "last_presence")} :eref {ALIAS :aliasname
__tmp :colnames ("id" "last_presence")} :rtekind 1 :subquery <>
:security_barrier false :relid 0 :inh false :relkind <> :rellockmode 0
:perminfoindex 0 :lateral false :inFromCl true :securityQuals <>}
{RANGETBLENTRY :alias <> :eref {ALIAS :aliasname *RESULT* :colnames <>}
:rtekind 8 :lateral false :inFromCl false :securityQuals <>})
:unprunableRelids (b 1) :permInfos ({RTEPERMISSIONINFO :relid 332035 :inh true
:requiredPerms 6 :checkAsUser 0 :selectedCols (b 8) :insertedCols (b)
:updatedCols (b 12)}) :resultRelations (i 1) :appendRelations <> :subplans <>
:rewindPlanIDs (b) :rowMarks <> :relationOids (o 332035) :invalItems <>
:paramExecTypes (o 0) :utilityStmt <> :stmt_location 1 :stmt_len 0}
The planner decides several important things:
- which indexes to use
- whether to use sequential scans or index scans
- join methods (nested loop, hash join, merge join)
- estimated cost of the query
- expected number of rows
The execution plan generated in this stage is later executed by PostgreSQL's executor.
Role of debug_pretty_print
The parameter debug_pretty_print controls how PostgreSQL prints these internal structures.
Default value:
debug_pretty_print = on
When enabled, PostgreSQL formats the parse tree, rewritten tree, and execution plan in a structured and indented format.
When disabled:
debug_pretty_print = off, then the log file looks like this
2026-03-07 15:07:55.293 IST [261654] odoouser@odoo_test main_serverLOG: parse tree:
2026-03-07 15:07:55.293 IST [261654] odoouser@odoo_test main_serverDETAIL: {QUERY :commandType 6 :querySource 0 :canSetTag true :utilityStmt
{TRANSACTIONSTMT :kind 2 :options <> :savepoint_name <> :gid <> :chain false
:location -1} :resultRelation 0 :hasAggs false :hasWindowFuncs false
:hasTargetSRFs false :hasSubLinks false :hasDistinctOn false :hasRecursive
false :hasModifyingCTE false :hasForUpdate false :hasRowSecurity false
:hasGroupRTE false :isReturn false :cteList <> :rtable <> :rteperminfos <>
:jointree <> :mergeActionList <> :mergeTargetRelation 0 :mergeJoinCondition <>
:targetList <> :override 0 :onConflict <> :returningOldAlias <>
:returningNewAlias <> :returningList <> :groupClause <> :groupDistinct false
:groupingSets <> :havingQual <> :windowClause <> :distinctClause <>
:sortClause <> :limitOffset <> :limitCount <> :limitOption 0 :rowMarks <>
:setOperations <> :constraintDeps <> :withCheckOptions <> :stmt_location 0
:stmt_len 0}
2026-03-07 15:07:55.293 IST [261654] odoouser@odoo_test main_serverSTATEMENT: COMMIT
2026-03-07 15:07:55.293 IST [261654] odoouser@odoo_test main_serverLOG: rewritten parse tree:
2026-03-07 15:07:55.293 IST [261654] odoouser@odoo_test main_serverDETAIL: ({QUERY :commandType 6 :querySource 0 :canSetTag true :utilityStmt
{TRANSACTIONSTMT :kind 2 :options <> :savepoint_name <> :gid <> :chain false
:location -1} :resultRelation 0 :hasAggs false :hasWindowFuncs false
:hasTargetSRFs false :hasSubLinks false :hasDistinctOn false :hasRecursive
false :hasModifyingCTE false :hasForUpdate false :hasRowSecurity false
:hasGroupRTE false :isReturn false :cteList <> :rtable <> :rteperminfos <>
:jointree <> :mergeActionList <> :mergeTargetRelation 0 :mergeJoinCondition <>
:targetList <> :override 0 :onConflict <> :returningOldAlias <>
:returningNewAlias <> :returningList <> :groupClause <> :groupDistinct false
:groupingSets <> :havingQual <> :windowClause <> :distinctClause <>
:sortClause <> :limitOffset <> :limitCount <> :limitOption 0 :rowMarks <>
:setOperations <> :constraintDeps <> :withCheckOptions <> :stmt_location 0
:stmt_len 0})
The same information appears in a compact format without indentation, making it harder to read.
These debugging parameters are mainly useful for:
- Understanding PostgreSQL internals
- analyzing how queries are transformed
- debugging query planning issues
- studying PostgreSQL source code
- learning database engine behavior
However, they should not be enabled in production systems because they generate a large volume of log data.
PostgreSQL provides powerful debugging capabilities that allow developers and DBAs to observe the internal stages of query processing. By enabling debug_print_parse, debug_print_rewritten, and debug_print_plan, you can see how PostgreSQL transforms a query from SQL text into an executable plan.
The debug_pretty_print parameter further improves readability by formatting the internal structures in an indented layout.
Studying these logs helps build a deeper understanding of PostgreSQL’s internal workflow and provides valuable insight when troubleshooting complex query behavior.