db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: What should the "Graphical Query Explainer" shows?
Date Fri, 28 May 2010 15:20:43 GMT
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
>
> And then perhaps if I wanted to learn more about a particular node I 
> would
> want to 'drill down' into that node to see more details.
>
> thanks,
>
> bryan
>
I would also like to see the following information pop out. It is hard 
to figure this out from the current query plans:

1) For scan nodes, what pieces of the WHERE clause were pushed into the 
Store. I believe these turn up in the current query plans as "scan 
qualifiers" and "next qualifiers".

2) What additional pieces of the WHERE clause are executed at each node. 
I'm not sure that this information is saved in the XPLAIN output.

Thanks,
-Rick

Mime
View raw message