A better viewer for PostgreSQL debug trees
If you set debug_print_parse, debug_print_rewritten, or debug_print_plan to true, PostgreSQL will log some of the interesting internal data structures during the query execution. But these logs are usually too long and difficult to inspect.
I recently switched to using Frog to generate my blog. Last week I wrote a little Frog plugin to allow me embed these trees in a nice tree view in blog posts.
For example, for SELECT * FROM t, the query tree would be:
-
- :commandType 1
- :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
- :cteList <>
-
:rtable
-
-
-
- :alias <>
-
:eref
-
- :aliasname t
-
:colnames
-
- "a" "b"
(1 items) - )
-
... - }
-
- :rtekind 0
- :relid 16890
- :relkind r
- :tablesample <>
- :lateral false
- :inh true
- :inFromCl true
- :requiredPerms 2
- :checkAsUser 0
-
:selectedCols
-
- b 9 10
(1 items) - )
-
-
:insertedCols
-
- b
(1 items) - )
-
-
:updatedCols
-
- b
(1 items) - )
-
- :securityQuals <>
... - }
-
(1 items) -
- )
-
-
:jointree
-
-
:fromlist
-
-
-
- :rtindex 1
... - }
-
(1 items) -
- )
-
-
:quals
-
- :opno 521
- :opfuncid 147
- :opresulttype 16
- :opretset false
- :opcollid 0
- :inputcollid 0
-
:args
-
-
-
- :varno 1
- :varattno 1
- :vartype 23
- :vartypmod -1
- :varcollid 0
- :varlevelsup 0
- :varnoold 1
- :varoattno 1
- :location 22
... - }
-
-
-
- :consttype 23
- :consttypmod -1
- :constcollid 0
- :constlen 4
- :constbyval true
- :constisnull false
- :location 26
- :constvalue 4 [ 0 0 0 0 0 0 0 0 ]
... - }
-
(2 items) -
- )
-
- :location 24
... - }
-
... -
:fromlist
- }
-
-
:targetList
-
-
-
-
:expr
-
- :varno 1
- :varattno 1
- :vartype 23
- :vartypmod -1
- :varcollid 0
- :varlevelsup 0
- :varnoold 1
- :varoattno 1
- :location 7
... - }
-
- :resno 1
- :resname a
- :ressortgroupref 0
- :resorigtbl 16890
- :resorigcol 1
- :resjunk false
... -
:expr
- }
-
-
-
-
:expr
-
- :varno 1
- :varattno 2
- :vartype 25
- :vartypmod -1
- :varcollid 100
- :varlevelsup 0
- :varnoold 1
- :varoattno 2
- :location 7
... - }
-
- :resno 2
- :resname b
- :ressortgroupref 0
- :resorigtbl 16890
- :resorigcol 2
- :resjunk false
... -
:expr
- }
-
(2 items) -
- )
-
- :override 0
- :onConflict <>
- :returningList <>
- :groupClause <>
- :groupingSets <>
- :havingQual <>
- :windowClause <>
- :distinctClause <>
- :sortClause <>
- :limitOffset <>
- :limitCount <>
- :rowMarks <>
- :setOperations <>
- :constraintDeps <>
- :stmt_location 0
- :stmt_len 27
... - }
If you haven’t noticed, the advantage of this compared to putting the whole thing into a simple html code block is that arrays and objects are minimized by default so you can have a better view at the higher level before digging deep into the lower levels.
You can find the source code for this plugin at pgtree.rkt.
It would be nice if some of editors/IDEs had something similar for postgres logs.