The EXPLAIN statement allows you to obtain the query execution plan generated by YSQL for a given SQL statement.
The EXPLAIN command has a lot of cool options. Here are few examples that could
be very useful during the analysis of query performance issues…
Say I have this simple query:
yugabyte=> SELECT * FROM fact JOIN dim ON dim.c2 = fact.c2;
c1 | c2 | c2 | c3
----+----+----+----
1 | A | A | X
2 | B | B | Y
3 | C | C | Z
(3 rows)
The standard base EXPLAIN command shows:
yugabyte=> EXPLAIN SELECT * FROM fact JOIN dim ON dim.c2 = fact.c2 ORDER BY dim.c3;
QUERY PLAN
---------------------------------------------------------------------------------
Sort (cost=263.72..266.22 rows=1000 width=132)
Sort Key: dim.c3
-> Nested Loop (cost=0.00..213.89 rows=1000 width=132)
-> Seq Scan on fact (cost=0.00..100.00 rows=1000 width=36)
-> Index Scan using dim_pkey on dim (cost=0.00..0.11 rows=1 width=64)
Index Cond: ((c2)::text = (fact.c2)::text)
(6 rows)
We can turn off cost outputs like this:
yugabyte=> EXPLAIN (costs off) SELECT * FROM fact JOIN dim ON dim.c2 = fact.c2 ORDER BY dim.c3;
QUERY PLAN
----------------------------------------------------------
Sort
Sort Key: dim.c3
-> Nested Loop
-> Seq Scan on fact
-> Index Scan using dim_pkey on dim
Index Cond: ((c2)::text = (fact.c2)::text)
(6 rows)
And we can add the anaylze option to run the SQL and display actual row counts and total elapsed time expended within each plan node:
yugabyte=> EXPLAIN (costs off, analyze) SELECT * FROM fact JOIN dim ON dim.c2 = fact.c2 ORDER BY dim.c3;
QUERY PLAN
----------------------------------------------------------------------------------------
Sort (actual time=2.004..2.005 rows=3 loops=1)
Sort Key: dim.c3
Sort Method: quicksort Memory: 25kB
-> Nested Loop (actual time=1.183..1.992 rows=3 loops=1)
-> Seq Scan on fact (actual time=0.708..0.715 rows=3 loops=1)
-> Index Scan using dim_pkey on dim (actual time=0.409..0.409 rows=1 loops=3)
Index Cond: ((c2)::text = (fact.c2)::text)
Planning Time: 0.113 ms
Execution Time: 2.091 ms
(9 rows)
And we can show even more information regarding the plan with the verbose option:
yugabyte=> EXPLAIN (costs off, analyze, verbose) SELECT * FROM fact JOIN dim ON dim.c2 = fact.c2 ORDER BY dim.c3;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Sort (actual time=2.056..2.056 rows=3 loops=1)
Output: fact.c1, fact.c2, dim.c2, dim.c3, dim.c3
Sort Key: dim.c3
Sort Method: quicksort Memory: 25kB
-> Nested Loop (actual time=1.131..2.044 rows=3 loops=1)
Output: fact.c1, fact.c2, dim.c2, dim.c3, dim.c3
Inner Unique: true
-> Seq Scan on public.fact (actual time=0.647..0.653 rows=3 loops=1)
Output: fact.c1, fact.c2
-> Index Scan using dim_pkey on public.dim (actual time=0.446..0.446 rows=1 loops=3)
Output: dim.c2, dim.c3
Index Cond: ((dim.c2)::text = (fact.c2)::text)
Planning Time: 0.113 ms
Execution Time: 2.144 ms
(14 rows)
Have Fun!