db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nirmal Fernando <nirmal070...@gmail.com>
Subject Re: What should the "Graphical Query Explainer" shows?
Date Sat, 29 May 2010 05:20:48 GMT
Hi Bryan,

On Fri, May 28, 2010 at 8:50 PM, Rick Hillegas <rick.hillegas@oracle.com> wrote:
> Bryan Pendleton wrote:
>>> I've thought of including following after some thinking and also after
>>> referring to PostgreSQL's graphical query explainer.
>>>    * Statement executed
>>>    * Type of scan performed (eg: TABLESCAN, INDEXSCAN, PROJECTION)
>>>    * Estimated cost by the Optimizer
>>>    * Estimated rows by the Optimizer
>>>    * Sort/Scan type
>> When I study query plan output, my first goal is to get an overall
>> sense of the tree structure of the query, and my second goal is to
>> get a sense of the amount of data flowing through the various nodes.
>> So I look at the nodes, and their type, and I look at the number of rows
>> seen and returned, and the number of pages visited.
>> I generally don't look at the Estimated values from the Optimizer, as
>> I'm usually more interested in what actually happened, than what the
>> Optimizer predicted might happen.
>> Here's an example query plan, attached to a recent Derby issue
>> (DERBY-4620):
>> https://issues.apache.org/jira/secure/attachment/12442155/query-plan.txt
>> As you can see, in this format, which expresses the tree structure
>> via indentation-by-8-spaces, trees which are nested more than a few
>> levels deep can become quite hard to read. But by looking carefully at
>> the most deeply nested portions of the display, we can see that there
>> are nested inner scan result sets for which 'Number of opens' is 274966,
>> which means that the query execution scanned these result sets 275
>> thousand times, which is an indicator that understanding why the overall
>> query plan had to check these result sets so many times is key to
>> understanding the query performance.
>> So I think that my preferred set of data would include:
>>  - node type
>>  - number of opens
>>  - rows input
>>  - rows returned
>>  - pages visited

Where can I get the values for the "node type" and "pages visited",
are they in the SYSXPLAIN tables (I didn't notice these there)?

Best Regards,

C.S.Nirmal J. Fernando
Department of Computer Science & Engineering,
Faculty of Engineering,
University of Moratuwa,
Sri Lanka.

View raw message