Two years of explain.depesz.com

First of all – just today I committed patch for Pg::Explain – which is the workhorse behind explain.depesz.com.

This patch fixes calculation of exclusive time for explain nodes, and the best thing about it is – I didn't write it. It's full patch provided by someone else – Filip Rembiałkowski – my former colleague, friend, and PostgreSQL DBA (not olny PG!).

This is (as far as I recall) first patch that was provided to me for this library, and I'm really grateful for the contribution.

New version of Pg::Explain will hit CPAN mirrors shortly (it's already uploaded, now we're waiting for CPAN mirrors to get it).

When I was updating it, I checked state of database. And I learned that explain.depesz.com is over 2 years now! Some stats follow.

First explain was uploaded on 2008-12-04 13:20:43+01.

Up to this moment ( now() == 2010-12-18 19:49:39.675522+01 ) there is 10532 plans (yes, over 10K!!!). Out of this 3386 plans are not public – i.e. you can't see them on previous explain page.

Year-Month New plans
2008-12 391 ####################
2009-01 257 #############
2009-02 200 ##########
2009-03 135 #######
2009-04 74 ####
2009-05 162 ########
2009-06 402 ####################
2009-07 333 #################
2009-08 288 ##############
2009-09 325 ################
2009-10 434 ######################
2009-11 236 ############
2009-12 248 ############
2010-01 336 #################
2010-02 429 #####################
2010-03 562 ############################
2010-04 499 #########################
2010-05 482 ########################
2010-06 628 ###############################
2010-07 655 #################################
2010-08 591 ##############################
2010-09 712 ####################################
2010-10 846 ##########################################
2010-11 869 ###########################################
2010-12 438 ######################

As you can see, there is nice increase of new plans in 2010.

At the end of May 2009, I added non-public plans. Since then the number of “secret" plans per month is quite stable:

Year-Month All plans Public Secret Secret%
2009-06 402 278 124 30.8 %
2009-07 333 237 96 28.8 %
2009-08 288 197 91 31.5 %
2009-09 325 215 110 33.8 %
2009-10 434 308 126 29.0 %
2009-11 236 172 64 27.1 %
2009-12 248 138 110 44.3 %
2010-01 336 216 120 35.7 %
2010-02 429 312 117 27.2 %
2010-03 562 377 185 32.9 %
2010-04 499 316 183 36.6 %
2010-05 482 283 199 41.2 %
2010-06 628 365 263 41.8 %
2010-07 655 346 309 47.1 %
2010-08 591 410 181 30.6 %
2010-09 712 440 272 38.2 %
2010-10 846 587 259 30.6 %
2010-11 869 508 361 41.5 %
2010-12 438 227 211 48.1 %

All in all – it seems to be used, and I've heard of at least two cases when people said that they will setup their own copies of explain.depesz.com for internal purposes (in case your plans can contain sensitive information).

In case you don't know – both explain parsing library and application code for the site are freely available under the same terms as Perl itself.

Finally – thanks for using the tool – it's really cool feeling to know that it's useful, and thanks again, Filip, for your contribution.

3 thoughts on “Two years of explain.depesz.com”

  1. Hi Depesz,

    Are you aware that explain.depesz.com was plugged in Greg Smith’s excellent new book?

    Peter Geoghegan (“sternocera”)

  2. I didn’t know that your tool was freely available — very good!

    Just wanted to tell you that it proved to be an invaluable tool for my Uni work.

    Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.