3

Suppose I have a pair of arbitrary SQL queries, each one depending upon the former ones, e.g.

CREATE VIEW v1 ( c3 ) AS SELECT c1 + c2 FROM t1;
SELECT sum(c3) FROM v1;
DROP VIEW v1;

(but note I am not asking about these specific queries - this is just an example; assume I get the queries from a file and do not know them in advance.)

Now, I want to get my DBMS to EXPLAIN its plan for all of my queries (or an arbitrary query in the middle, it's the same problem essentially) - but I do not want it to actually execute any of them.

Is this possible with (1) MySQL? (2) PostgreSQL? (3) MonetDB?

einpoklum
  • 118,144
  • 57
  • 340
  • 684

2 Answers2

1

PostgreSQL

You may use the explain statements as follows.

BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

Refer this, documentation answers your question.

MonetDB

Similarly to the above, except that the transaction-related statements are BEGIN TRANSACTION and ROLLBACK statements (assuming that you are in auto-commit mode to begin with).

Refer this.

MySQL

MySQL explain it self does what you need. No need to ROLLBACK.

Refer this answer.

Community
  • 1
  • 1
Marlon Abeykoon
  • 11,927
  • 4
  • 54
  • 75
  • What is there to roll back since no changes are being made to the database? – Patrick Aug 20 '16 at 16:10
  • He does not want to execute the first query which is a Create statement. – Marlon Abeykoon Aug 20 '16 at 16:16
  • The way I read it, the OP does not want to create the view, but s/he does want to know its behaviour upon execution. But I grant you that the wording of the question is somewhat ambiguous. – Patrick Aug 20 '16 at 16:18
  • @MarlonAbeykoon: Actually, in MonetDB, it's `BEGIN TRANSACTION;` and `ROLLBACK;` after the last query. And there's no need for `ANALYZE`, just `EXPLAIN`. Anyway, thanks, that works. – einpoklum Aug 20 '16 at 20:50
  • 1
    MonetDB syntax for transaction is `START TRANSACTION`, not `BEGIN TRANSACTION`. Besides that, there is no need for the `ROLLBACK` trick, as `EXPLAIN SELECT ...` does not execute anything. – cornuz Sep 30 '16 at 14:13
1

When you execute a view, the underlying SELECT query is executed, obviously. So in PostgreSQL the actual execution plan is based on this:

-- Common use of the view
SELECT sum(c3) FROM v1;

becomes

-- Expansion of the view into plain SQL
SELECT sum(c3) FROM (SELECT c1 + c2 AS c3 FROM t1) v1;

becomes

-- Flattening by the query planner, this is what actually gets executed
SELECT sum(c1 + c2) FROM t1;

So the answer is:

EXPLAIN SELECT sum(c1 + c2) FROM t1;

This most certainly works for PostgreSQL and most likely for all other DBMSes too, but check their docs on how the query planner works.

If your view definition is very complex, just take the query on the view you want to evaluate and paste the entire view definition in brackets () just before the name of the view (which then effectively becomes an alias for a sub-query). The query planner will do the rest for you.

Patrick
  • 29,357
  • 6
  • 62
  • 90
  • I would stop this answer at the first flattening stage and not suppose what the optimizer does, because A) optimizesr are often stupid and B) optimizer behaviour may not be consistent across DBs or even versions (+1 though) – Bohemian Aug 20 '16 at 16:26
  • @Bohemian Thanks for the +1 and I live in the happy universe of PG with the best query planner on the market (or so they say) so I usually do not presume to know better than the query planner. I just added a para to my answer which basically implements your suggestion. – Patrick Aug 20 '16 at 16:28
  • Answers that can apply to multiple contexts are valuable to more future visitors. – Bohemian Aug 20 '16 at 16:41
  • 1
    I wasn't looking for the explanation of those specific queries, that was just an example. Also, I don't need the EXPLAIN output here on SO, I need the DBMS to give me that EXPLAINation. – einpoklum Aug 20 '16 at 17:39