#14431: Explain - For Postgres and Greenplum, add a configurable parameter to turn on/off analyze when explaining a query.

tomconrad reported 2016-05-12T17:56:25Z · last modified 2016-10-27T18:38:14Z

Explain - For Postgres and Greenplum, add a configurable parameter to turn on/off analyze when explaining a query.

Dev	tomconrad
QA	tariqrahiman

Priority	Major
Complexity	Moderate

Component	Q Analyzer - Visual Explain Plan - PostgreSQL
Version	18.0

The analyze option for Postgres and Greenplum causes the statement, that is being explained, to be actually executed, not only planned. This

can cause changes to the database that the user is not expecting. For example, a user would get an insert statement executed two times when

doing the explain.

The solution is to have an ADS option for both Postgres and Greenplum databases to turn analyze on and off. The default will be off. This

way, the user can manually turn on analyze with expected results.

Two options will be added - See screenshot for location.

File->Options->Visual Explain->Greenplum Visual Explain

File->Options->Visual Explain->Postgres Visual Explain

Inside of each of the above will be a checkbox option to turn analyze on or off.

3 attachments

2016-05-12_10-55-25.png

2016-05-12T17:56:25Z

46 KB
14431_verified_patch17.0.4-8.png

2016-05-17T11:23:12Z

50 KB
14431_alpha10Behavior.png

2016-10-27T08:14:29Z

153 KB

All Comments (8) Change History

tomconrad 2016-05-16T17:45:05Z · (edited)

V17 svn #49827 svn #49833

V18 svn#49854

Hi Sachin, Please check that the text in the Greenplum and Postgres analyze option is ok?

Hi Tariq, Please have your team test this with as many versions of Greenplum and Postgres as possible.

Thank you, Tom

tariqrahiman 2016-05-13T03:27:04Z

@ sachin / tom: We do not have an option like Turn ... On/Off

From our File -> Options standard, I came up with the below instead of Turn Explain Analyzer On

Enable Explain Analyze option

When enabled, this option will carry out the Analyze command and show actual run times and other statistics with Show Execution Plan or Execute Explain. This parameter defaults to OFF.

tariqrahiman 2016-05-16T22:55:32Z

Verified in ADS v17.0.4-8

Ananya(*) 2016-05-17T11:26:14Z

Verified in Aqua Data Studio 17.0.4-8.

Scenario 1:

Launch Query Analyzer.
Write an INSERT query.
Turn ON Show Execution Plan.
Go to File > Option > Visual Explain > Greenplum Visual Explain.

Result: By default the Enable Explain Analyze is disabled.

Scenario 2:

Launch Query Analyzer.
Write an INSERT query.
Turn ON Show Execution Plan.
Go to File > Option > Visual Explain > Greenplum Visual Explain.
With Enable Explain Analyze OFF, execute the INSERT query.
Perform a select * query on the same table.

Result: Single record is inserted in the table.

Scenario 3:

Launch Query Analyzer.
Write an INSERT query.
Turn ON Show Execution Plan.
Go to File > Option > Visual Explain > Greenplum Visual Explain.
Check Enable Explain Analyze.
With Enable Explain Analyze ON, execute the INSERT query.
Perform a select * query on the same table.

Result: Same record is inserted twice.

Ananya(*) 2016-10-27T08:14:39Z

This issue is reproducible in ADS: 18.0.0-alpha-10. Please see 14431_alpha10Behavior.png

tariqrahiman 2016-10-27T15:21:38Z

@tom: Dont we need to port this to v18 as well ?

tomconrad 2016-10-27T18:06:50Z

Hi Tariq, This is ported to V18. I ran some tests in V18 and it looks like it is working correctly. Please give it a try. Thank you, Tom

tariqrahiman 2016-10-27T18:38:14Z

Verified in ADS v18.0.0-alpha-10

Search Tips

Aqua Data Studio / nhilam

Explain - For Postgres and Greenplum, add a configurable parameter to turn on/off analyze when explaining a query.

3 attachments

Issue #14431

Completion

Search Tips

Aqua Data Studio / nhilam

Title

Explain - For Postgres and Greenplum, add a configurable parameter to turn on/off analyze when explaining a query.

3 attachments

Issue #14431

Completion