If you've ever worked with Postgres, chances are you've had to debug a slow SQL query.
The EXPLAIN command is usually the first port of call: it prints out the query plan that the database engine is going to execute. Paired with ANALYZE, it runs the query and updates the statistics.
It's a powerful tool, but it's easy to get lost in the output when the query is very complex.
Earlier this week I was attempting to debug a SELECT statement which was performing rather poorly on a modestly sized table.
Naturally, I reached for psql and typed in EXPLAIN ANALYZE, but I was stumped by the result.
mydatabase=> EXPLAIN ANALYZE <BORING QUERY>
QUERY PLAN
---------------------------------------------------------------------------------------------
Gather Merge (boring details)
Workers Planned: X
Workers Launched: X
<boring query plan>
(17 rows)
(17 rows) did not make sense to me. I knew the table contained several thousands rows, and I was certain that the query should return many more tuples.
After several minutes of befuddlement, a colleague pointed out that 17 was not the number of rows returned by the executed query: it was the number of lines that made up the query plan.
In other words, the default behaviour of psql is to treat each line of the query plan output as if it were a table row.
In hindsight, this should have been obvious. EXPLAIN also outputs the number of rows, which is a pretty strong indicator that (x rows) couldn't possibly be the number of rows returned by the query, since the command just devises the plan without executing it.
There are two ways to turn off that behaviour from the psql shell, if you wish to do so:
mydatabase=> \pset footer off
or
mydatabase=> \pset tuples_only on
The latter strips out a bunch of useful data (including column names), not just the footer.