Advanced Features

Table of Contents

• When Verbose is Off, Row Counts Are Approximate
• PEG Can Show DML Plan Graphs
• Unbalanced Worker Values Are Highlighted
• Actual Vs. Estimated Ratios Have Minimums
• CTE Icons are Placeholders Only
• PEG Uses SQLite

When Verbose is Off, Row Counts Are Approximate

If worker information is not available, PEG will approximate row counts. It does so by multiplying the number of workers launched, plus the parallel leader, by the number of actual rows emitted by the operator. The product will often be close to the actual number of rows had worker info been returned. However, this is considered an estimate, so PEG shows the number of rows (and data mass) with normal font (estimated values) instead of bold font (actual values).

VERBOSE OFF: ROW COUNTS ARE CLOSE

VERBOSE ON: ROWS COUNTS ARE EXACT

In the upper screenshot, you can see how the Parallel Hash Join approximates 86k rows. In the lower screenshot, the same query was run with VERBOSE ON, and it actually returned 90k rows.

PEG Can Show DML Plan Graphs

You can wrap DML commands with begin / rollback transaction commands. This way, you can focus on your DML command performance without persisting your changes. In the "before" and "after" fields, be sure to separate commands with a semicolon.

Unbalanced Worker Values Are Highlighted

If an operator has workers, the parallel leader and workers should "balance." When they aren't, PEG highlights them.

• Time Difference:
  The parallel leader and all workers should be 100% of the operator's time difference, or close to it.

• Actual Mass and Rows:
  For 1 worker, the parallel leader and worker should be 50% of the actual mass or actual rows.
  For 2 workers, the the parallel leader and both workers should be 33% of the actual mass or actual rows.

UNBALANCED VALUES:

The parallel leader and worker time differences are not close to the operator's time difference.

BALANCED VALUES:

Actual Vs. Estimated Ratios Have Minimums

If Actual Rows, Estimated Rows, Actual Mass, and Estimated Mass is low, PEG will show only thin lines. Currently, PEG will scale the line thickness under these conditions:

• If Actual Rows + Estimated Rows > 10000 rows
• If Actual Mass + Estimated Mass > 50k

The ratio will be either Actual:Estimated or Estimated:Actual, whichever is larger.

CTE Icons are Placeholders Only

If a "Parent Relationship" property is set to "InitPlan," this indicates a CTE is used. So, PEG adds a CTE icon, sets it to the Subplan Name, and copies the child's mass or rows on the CTE icon's arrow. So, one operation appears as two icons.

Strictly speaking, the explain plan text does not have a CTE. I decided to display it anyway to make it a little easier when you hunt for the CTE in your SQL.

PEG Uses SQLite

You're welcome to download sqlite and look at the sqlite database that PostgreSQL Explain Plan creates. The database file will be stored in the same directory as the .JAR file.