Changes on explain.depesz.com

Uploaded new version to the server – straight from GitHub. There are two changes – one visible, and one not really.

The invisible change, first, is one for people hosting explain.depesz.com on their own. As you perhaps know you can get sources of explain.depesz.com and install it on any box you want (as log as you can get there PostgreSQL, Perl, and some perl modules). While working on it on my own, I figured I could use a way to tell which version of module-xxx the site is running right now. So I build /info page (which is inaccessible to everyone, but manually-marked admins), which lists versions and interesting paths.

The second change – the one visible to users, is that I made explain.depesz.com commify numbers. Sometimes it can be hard to read value like 12325563, but now it will be displayed as 12,325,563 making is simpler to grasp.

This second change was suggested by Jacek Wielemborek, so if you hate it – blame him. Of course if you love the change – it's all on me 🙂

Hope you'll find it helpful.

Waiting for 9.4 – Include planning time in EXPLAIN ANALYZE output.

On 29th of January, Robert Haas committed patch:

Include planning time in EXPLAIN ANALYZE output.
 
This doesn't work for prepared queries, but it's not too easy to get
the information in that case and there's some debate as to exactly
what the right thing to measure is, so just do this for now.
 
Andreas Karlsson, with slight doc changes by me.

Continue reading Waiting for 9.4 – Include planning time in EXPLAIN ANALYZE output.

Explaining the unexplainable – part 5

In previous posts in this series, I talked about how to read EXPLAIN output, and what each line (operation/node) means.

Now, in the final post, I will try to explain how it happens that Pg chooses “Operation X" over “Operation Y".

Continue reading Explaining the unexplainable – part 5

Explaining the unexplainable – part 3

In previous post in the series I wrote about how to interpret single line in explain analyze output, it's structure, and later on described all basic data-getting operations (nodes in explain tree).

Today, we'll move towards more complicated operations.

Continue reading Explaining the unexplainable – part 3

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