Explaining the unexplainable – part 6: buffers
Back in 2013 I wrote a series of 5 posts about how to read explain analyze output. Figured that there is one big part missing – buffers info.
Back in 2013 I wrote a series of 5 posts about how to read explain analyze output. Figured that there is one big part missing – buffers info.
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”.
In this, hopefully 2nd to last, post in the series, I will cover the rest of usually happening operations that you can see in your explain outputs.
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.
Last time I wrote about what explain output shows. Now I’d like to talk more about various types of “nodes” / operations that you might see in explain plans.
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 … Continue reading “Explaining the unexplainable”
Some time ago I was contacted by Adam Smith – he pointed out that subquery names in “Subquery Scan” nodes were not properly anonymized. Now, they are, which you can see in here: normal version anonymized version While working on it, I also added (helpful?) links from node types to my blogposts about reading explain … Continue reading “explain.depesz.com changes and new stats”
Very long time ago (in PostgreSQL 9.2) we got ability to get, from EXPLAIN, information how long Pg spent on I/O operations. Reads and writes. Over the years situation has changed.
I just released first version of change to explain.depesz.com that displays buffer I/O information, as described recently. You can see it in here. There are two new columns in there, showing how much data given node read from disk, and how much it wrote. It’s definitely not 100% OK now, as you can see, speed … Continue reading “Buffers I/O information on explain.depesz.com”