Hi Bryan, On Fri, May 28, 2010 at 8:50 PM, Rick Hillegas <[email protected]> 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, Nirmal C.S.Nirmal J. Fernando Department of Computer Science & Engineering, Faculty of Engineering, University of Moratuwa, Sri Lanka.
