Tag: explain.depesz.com

explain.depesz.com – stats after a year

I just checked, and apparently explain.depesz.com is online for over a year now. First plans was added on 2008-12-04 13:20:43+01.

Since then there have been 3602 plans added, 770 of them were set to be private (not displayed on previous explains page.

Longest plan has 2267194 characters (yes, 2.2 megabytes of explain analyze output!), but it's not public, so I can't tell you the url. Longest public plan is this monstrosity, which (while being only explain output, not explain analyze) has 503585 characters.

During this time, there have been 61 days that nobody added any plan on (perhaps problems with software?).

The most heavily used day was 23rd of June 2009, when 71 new plans have been added (4 private).

Thank you all for using it. I hope you find it useful, and I promise, that one day, I will finally sit down, and add all long-due features (like work with new explain formats, fixing some output glitches, stats of used objects per plan).

If you'd like to take a peek at how it works – source of explain.depesz.com is always available.

explain.depesz.com – update

I just updated explain.depesz.com with 2 new interface features:

  • When you put mouse cursor over node, it will mark direct child nodes with
  • When you click on node, all child nodes (even indirect) will be hidden, and the node you clicked on will be marked with

Both features are directed towards people who analyze longer plans – ability to hide parts or just visually see what the Nested Loop is calling in 2nd stage greatly helps me see the flow of query – you can test it, for example, here.

By the way – BIG THANK YOU for Metys for help on it (well, actually, for doing it).

explain.depesz.com – update

I just modified the internals of explain.depesz.com. Now, it finally stores the plans in database (previously it stored the plans as files in dedicated directory).

Effect for enduser is just that history page should load faster.

But, having the data in database makes it possible to add more features.

One such feature is already added – ability to explain plan, but not list it on history page.

Right now, when you add new plan, you can specify if you want it to be listed with other previous explains – default value is “yes". But if you don't want just anybody to be able to click his way to your plan – there is option for it.

For obvious reasons all previous plans are marked as public now. If you want it to be changed, please contact me – we can probably do something about it.

And as last thing: I would like to express my big THANK YOU to all of you who use explain.depesz.com.

(side note: Catalyst is really cool)

explain.depesz.com – update

I just updated explain.depesz.com with the newest explain-parsing library version (Pg::Explain v 0.09).

This version will hit CPAN mirrors in next couple of hours.

Changes:

  • Fix exclusive time calculations
  • Make PE::Node understand Bitmap scans (heap and index)
  • Add proper handling of nodes that were “never executed"
  • Add ->is_analyzed method to PE::Node to make it easy to distinguish between EXPLAIN and EXPLAIN ANALYZE nodes

explain.depesz.com.

Long time ago I wrote small program to filter EXPLAIN ANALYZE output, and add summary of time.

A bit later (I guess, I don't recall exact time line, it could have been earlier) Michael Glaesemann started explain-analyze.info – cool tool for checking what might be wrong with given plan.

I'm not really happy with the emphasis Michael put on bad rowcount estimates, so I decided to write my own tool. Enter explain.depesz.com.

Basic idea is: paste your explain analyze plan, and see the output. You can click on column headers to let it know which parameter is the most important for you – exclusive node time, inclusive node time, or rowcount mis-estimate.

It is definitely not perfect. I know of at least 1 bug now, and will fix it in not-distant future.

But, as for now – you can test it, play it, or simply use it. If you'd like to change/fix something – sources are freely available. Just be warned – it's Perl ;-P