db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendleton.de...@gmail.com>
Subject Re: What should the "Graphical Query Explainer" shows?
Date Fri, 28 May 2010 14:35:54 GMT
> 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

Mime
View raw message