Description

pgdebug is a tool which helps you to debug complicated queries. It takes a select statement as an input and outputs a series of partial queries corresponding to each CTE and each subquery in the from clause.

Supported commands: select.

Unsupported commands: insert, update, delete.

Supported subqueries:

• CTEs defined using with
• Recursive CTEs
• Subqueries in the from clause
• Subqueries and CTEs using values

Installation

pgdebug is a Node.js-based tool, so you can install it from npm:

npm install --save pgdebug

Usage

Options

  --ast                  Output source query AST in JSON format 
                         instead of SQL statements
  -c, --command string   SQL command to process
  -f, --file string      File name to read SQL command from
  -i, --interactive      Read SQL from stdin

Here is an example of applying pgdebug to a query with CTEs (the original query is included in the output):

~/t/pgdebug ❯❯❯ npx pgdebug -c "with a as (                                                                         ⏎
    select * from customers
)
, b as (
    select * from projects
)
select *
from b
join a using (customer_id)"

WITH a AS (SELECT * FROM "customers") SELECT * FROM "a";
WITH a AS (SELECT * FROM "customers"), b AS (SELECT * FROM "projects") SELECT * FROM "b";
with a as (
    select * from customers
)
, b as (
    select * from projects
)
select *
from b
join a using (customer_id)

Here is an example of a recursive CTE (note that the recursive qualifier is preserved in the output):

~/t/pgdebug ❯❯❯ npx pgdebug -c "with recursive t(n) as (
    values (1)
  union all
    select n+1 from t where n < 100
)
select sum(n) from t"

WITH RECURSIVE t ("n") AS ((VALUES (1)) UNION ALL (SELECT (("n") + (1)) FROM "t" WHERE 
(("n") < (100)))) SELECT * FROM "t";
with recursive t(n) as (
    values (1)
  union all
    select n+1 from t where n < 100
)
select sum(n) from t

And here is an example with subqueries in the from clause:

~/t/pgdebug ❯❯❯ npx pgdebug -c "select * from (select * from customers) c 
                join (select * from projects) p using (customer_id)"

SELECT * FROM "customers";
SELECT * FROM "projects";
select * from (select * from customers) c join (select * from projects) p 
    using (customer_id)

Combining with psql

Producing these queries isn’t all that useful in itself as you still have to copy-paste and execute them, so I use pgdebug in combination with psql, first piping the queries into psql and then piping the output into less so I can scroll through it:

 echo "select * from (select zone_id, project_id, name from zones) z
 join (select project_id, name from projects) p using (project_id)"
 | npx pgdebug -i
 | psql -q -e -d pgtime
 | less

-q supresses psql messages, and -e makes it output the query SQL, which is handy to understand which result set I’m looking at. The result looks like this:

SELECT "zone_id",
"project_id",
"name" FROM "zones";
 zone_id | project_id |  name
---------+------------+--------
      14 |          1 |
       1 |          1 | Zone 1
       2 |          1 | Zone 2
       2 |          1 |
(4 rows)

Time: 1.648 ms
SELECT "project_id",
"name" FROM "projects";
 project_id |   name
------------+-----------
          1 | Highway 1
          2 | Highway 2
          2 | Highway 2
          1 | Highway 1
(4 rows)

Time: 0.290 ms
select * from (select zone_id, project_id, name from zones) z join 
    (select project_id, name from projects) p using (project_id)
 project_id | zone_id |  name  |   name
------------+---------+--------+-----------
          1 |      14 |        | Highway 1
          1 |      14 |        | Highway 1
          1 |       1 | Zone 1 | Highway 1
          1 |       1 | Zone 1 | Highway 1
          1 |       2 | Zone 2 | Highway 1
          1 |       2 | Zone 2 | Highway 1
          1 |       2 |        | Highway 1
          1 |       2 |        | Highway 1
(8 rows)

Time: 0.732 ms