PostgreSQL / Explain Plan :- DBeaver & Azure Data Studio

Background

Playing around some more with PostgreSQL.

SQL

Here is the simple SQL.


select *

from   public.order_details tblOD

left outer join public.orders tblO

		on tblOD.order_id = tblO.order_id

where  tblOD.product_id = 10

Dbeaver

SQL Editor

Enter SQL in to DBeaver SQL Editor.

Execution Plan

Request

Request via Menu – Explain Execution Plan

Initiate Request

To get the query’s execution plan, please do the following :-

  1. Select the Query
  2. Execute the Query
  3. The Query is executed and it’s result is shown in a new window
  4. Show Execution Plan
    • Click on the menu item and choose “SQL Editor” \ “Explain Execution Plan”
Result
Image

northwind.orderDetails.01.20190908.0825AM.PNG

Issues

  1. The Output grid is “Simple”
    • Unfortunately, it does not appear easy nor intuitive to select all the cells, capture the selection, and copy it

 

Request via Query Prefixed with EXPLAIN

Initiate Request

To get the query’s execution plan, please do the following :-

  1. Enter the Query
  2. Prefix the entered with PostgreSQL keyword “EXPLAIN
  3. Execute the Query
  4. The Query is executed and the query’s execution plan is returned
Result
Image

sql.prefixedWithExplain.01.20190808.0836AM.PNG

Issues

  1. None
    • The query is display in a normal output window
      • There are two visible panes, Grid and Text

 

Azure Data Studio

SQL Editor

Enter SQL in to Azure Data Studio SQL Editor.

Execution Plan

Request

Request via Explain button

Initiate Request

To get the query’s execution plan, please do the following :-

  1. Select the Query
  2. Please click the Explain button
  3. The query’s Execution Plan is returned
Result
Image

azureDataStudio.button.explain.01.20190808.0841AM

Explain
  1. It is easy and intuitive to select and copy all the “Execution Plan” steps

Summary

Both DBeaver and Azure Data Studio offers good standard support for requesting and viewing a query’s execution plan.

Unfortunately the output generated via DBeaver “explain plan” is a generic textual dump.

It likely makes sense as it is likely intended to support all databases and so it is a simple canvas.

That shortcoming is easily eclipsed by simply requesting the Execution Plan via PostgreSQL “EXPLAIN” keyword.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s