How can I EXPLAIN several consecutive queries without executing them?
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
You may use the explain statements as follows.
BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;
Refer this, documentation answers your question.
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).
MySQL explain it self does what you need. No need to
Refer this answer.Автор: Marlon Abeykoon Размещён: 20.08.2016 04:01
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;
-- Expansion of the view into plain SQL SELECT sum(c3) FROM (SELECT c1 + c2 AS c3 FROM t1) v1;
-- 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.