New EXPLAIN

Copy/paste or Choose File and click Submit.

Note: No data is stored unless you click the "Save Plan" button on the results page.

Usage

The following are some best practices for using PlanChecker.

General

• Provide EXPLAIN ANALYZE so PlanChecker can give more details results.
• Provide entire output including Slice statistics, Statement statistics, Settings, etc... Show Example

  repro=# EXPLAIN ANALYZE SELECT * FROM table1 t1, table2 t2 WHERE t1.col1 = t2.col2;
                                                                                QUERY PLAN                                                                              
  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
   Gather Motion 4:1  (slice2; segments: 4)  (cost=114.00..451.00 rows=10000 width=16)
     Rows out:  10000 rows at destination with 6.146 ms to first row, 13 ms to end, start offset by 0.616 ms.
     ->  Hash Join  (cost=114.00..451.00 rows=2500 width=16)
           Hash Cond: t2.col2 = t1.col1
           Rows out:  Avg 2500.0 rows x 4 workers.  Max 2501 rows (seg2) with 7.449 ms to first row, 8.642 ms to end, start offset by 1.391 ms.
           Executor memory:  32K bytes avg, 32K bytes max (seg0).
           Work_mem used:  32K bytes avg, 32K bytes max (seg0). Workfile: (0 spilling, 0 reused)
           (seg2)   Hash chain length 1.0 avg, 1 max, using 1000 of 524341 buckets.
           ->  Seq Scan on table2 t2  (cost=0.00..112.00 rows=2500 width=8)
                 Rows out:  Avg 2500.0 rows x 4 workers.  Max 2501 rows (seg2) with 0.023 ms to first row, 0.182 ms to end, start offset by 8.815 ms.
           ->  Hash  (cost=64.00..64.00 rows=1000 width=8)
                 Rows in:  Avg 1000.0 rows x 4 workers.  Max 1000 rows (seg0) with 0.369 ms to end, start offset by 12 ms.
                 ->  Broadcast Motion 4:4  (slice1; segments: 4)  (cost=0.00..64.00 rows=1000 width=8)
                       Rows out:  Avg 1000.0 rows x 4 workers at destination.  Max 1000 rows (seg0) with 0.042 ms to first row, 0.212 ms to end, start offset by 12 ms.
                       ->  Seq Scan on table1 t1  (cost=0.00..14.00 rows=250 width=8)
                             Rows out:  Avg 250.0 rows x 4 workers.  Max 251 rows (seg2) with 0.727 ms to first row, 0.749 ms to end, start offset by 3.875 ms.
   Slice statistics:
     (slice0)    Executor memory: 386K bytes.
     (slice1)    Executor memory: 207K bytes avg x 4 workers, 207K bytes max (seg0).
     (slice2)    Executor memory: 8488K bytes avg x 4 workers, 8488K bytes max (seg0).  Work_mem: 32K bytes max.
   Statement statistics:
     Memory used: 128000K bytes
   Settings:  optimizer=off
   Optimizer status: legacy query optimizer
   Total runtime: 14.718 ms
  (25 rows)

• Whitespace is used to indent each node so it's important to keep the correct whitespace.

Using psql

1. Create an .sql file containing the query:

   $ cat myquery.sql
   EXPLAIN ANALYZE SELECT * FROM mytable
   WHERE date = '1955-11-05' AND location = 'Hill Valley';

2. Execute the query using psql and redirect the output:

   $ psql -f myquery.sql > myquery.out

3. Upload the output to PlanChecker:
   Choose File, select myquery.out, click Submit

Using pgAdmin

Note: When using pgAdmin each line of the output will be enclosed in doublequotes.
This is the expected format. Please do not modify it.

1. Open pgAdmin.
2. Open SQL Editor screen.
3. Execute the query prefixed with EXPLAIN ANALYZE:

   EXPLAIN ANALYZE SELECT * FROM mytable
   WHERE date = '1955-11-05' AND location = 'Hill Valley';

4. On the Data Output tab click the QUERY PLAN text column header to select all the text.
5. Copy/Paste the selected text in to PlanChecker and click Submit.

Checks

Here are the current checks:

New checks may be added in future.

About

PlanChecker parses EXPLAIN or EXPLAIN ANALYZE output generated from Greenplum database and checks for common issues which may affect query performance.

While the query plans look similar to PostgreSQL they are formatted differently and contain nodes which are specific to Greenplum.

Because Greenplum and Apache HAWQ share similar codebase, PlanChecker should also work with Apache HAWQ query plans.