Explaining the unexplainable

One of the first things new DBA hears is “Use the EXPLAIN". And upon first try he/she is greeted with incomprehensible:

                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=146.63..148.65 ROWS=808 width=138) (actual TIME=55.009..55.012 ROWS=71 loops=1)
   Sort KEY: n.nspname, p.proname, (pg_get_function_arguments(p.oid))
   Sort Method: quicksort  Memory: 43kB
   ->  Hash JOIN  (cost=1.14..107.61 ROWS=808 width=138) (actual TIME=42.495..54.854 ROWS=71 loops=1)
         Hash Cond: (p.pronamespace = n.oid)
         ->  Seq Scan ON pg_proc p  (cost=0.00..89.30 ROWS=808 width=78) (actual TIME=0.052..53.465 ROWS=2402 loops=1)
               FILTER: pg_function_is_visible(oid)
         ->  Hash  (cost=1.09..1.09 ROWS=4 width=68) (actual TIME=0.011..0.011 ROWS=4 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 1kB
               ->  Seq Scan ON pg_namespace n  (cost=0.00..1.09 ROWS=4 width=68) (actual TIME=0.005..0.007 ROWS=4 loops=1)
                     FILTER: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'information_schema'::name))

What does it even mean?

Continue reading Explaining the unexplainable

Changes on explain.depesz.com

One of the features that is actually disliked is anonymization. But, regardless of the dislike – it has some users. And one of the user mailed me with information about a bug – namely – foreign table file names were not anonymized.

So, I wrote a patch, tests, released new version of underlying parsing library.

Continue reading Changes on explain.depesz.com

Waiting for 9.2 – EXPLAIN TIMING

On 7th of February, Robert Haas committed patch:

Sometimes it may be useful to get actual row counts out of EXPLAIN
(ANALYZE) without paying the cost of timing every node entry/exit.
With this patch, you can say EXPLAIN (ANALYZE, TIMING OFF) to get that.
 
Tomas Vondra, reviewed by Eric Theise, with minor doc changes by me.

Continue reading Waiting for 9.2 – EXPLAIN TIMING

Waiting for 9.2 – filtered info in explain analyze

On 22nd of September, Tom Lane committed patch:

Make EXPLAIN ANALYZE report the numbers of rows rejected by filter steps.
 
This provides information about the numbers of tuples that were visited
but not returned by table scans, as well as the numbers of join tuples
that were considered and discarded within a join plan node.
 
There is still some discussion going on about the best way to report counts
for outer-join situations, but I think most of what's in the patch would
not change if we revise that, so I'm going to go ahead and commit it as-is.
 
Documentation changes to follow (they weren't in the submitted patch
either).
 
Marko Tiikkaja, reviewed by Marc Cousin, somewhat revised by Tom

Continue reading Waiting for 9.2 – filtered info in explain analyze

explain.depesz.com update

Just updated explain.depesz.com site with new functionality – when showing parsed plan, there is new tab “stats". In there you can see some basic statistics of the query, used types of nodes, and tables that were used by it.

There are probably some problems with stats (it's new functionality, so bugs are imminent), but

As usual, some stats need to be added 🙂

  • Total count of plans in database: 18127
  • Count of private (not listed on history page) plans: 6965
  • Count of anonymized plans: 166 (this feature proved to be much less used than I thought it will be)
  • Since (including) May 2011, there are more private plans added than public ones. In June – 58.9% of all plans were private.

explain.depesz.com version 2.0

Thanks to enormous work done by Łukasz ‘metys' Lewandowski, explain.depesz.com page is no longer “desiged by depesz" (which is a code for “ugly as hell"), but is nice, and good looking.

If you like it, please do send some thank you note to Łukasz – he blogs in Polish, but he reads and understands English too.

Change is not only skin deep. The whole site has been rewritten, and uses now Mojolicious web framework instead of Catalyst.

This change should be a welcome surprise to anyone willing to setup their own copy of the site for top-secret plans from their company – mostly because number of dependencies dropped significantly.

All in all – have fun, and thanks for using the site.