How can I EXPLAIN several consecutive queries without executing them?

mysql postgresql explain sql-execution-plan monetdb

166 просмотра

2 ответа

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 - reinstate Monica Источник Размещён: 08.11.2019 11:27

Ответы (2)


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.

Автор: Marlon Abeykoon Размещён: 20.08.2016 04:01

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 Размещён: 20.08.2016 04:15
Вопросы из категории :
32x32