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.
Continue reading Proper support for I/O Timings on explain.depesz.com
For a long time I was looking for SQL pretty printer.
Some queries that I had to deal with, over the years, were just insane to read, like this:
Continue reading New SQL pretty printer – based on parsing, and not regexps
Thanks to some discussion on Slack, and work by Alexandre Felipe viewing of large explains will be now a bit easier.
You can see it, for example, in this explain.
First of all, when you scroll down, the column headers stay in place. Plus – you can always see the horizontal scrollbar to see the rest for really long node descriptions.
Thanks a lot, I appreciate the work.
As of now, explain.depesz.com will provide hints for seq scans with multiple equality conditions joined with AND.
For example:
SELECT * FROM TABLE WHERE proj_type = 'GR14' AND week = 2000 AND pop_flag = '1'
You can see it in action in this plan.
Hope you'll find it useful.
I just pushed change to explain.depesz.com that allows for processing and displaying hints for plans.
For example, take a look at this plan, and check if you'll notice subtle “HINTS" tab.
In there you will see example hints – one about sort and memory, and the other about missing index.
It is not much, but it's a step in (hopefully) right direction, when the explain tool will also provide, automatically, some ideas on what to do to make the thing faster.
Hope you'll find it useful.
When you see plan on explain.depesz.com, one of the columns visible is exclusive.
In principle it's simple – take time of current node, subtract times of all sub nodes, and you get how much time was spent in this node alone.
Reality is not that simple. Two things especially make the calculations complicated. CTEs, and InitPlans.
Now, with the just pushed change to Pg::Explain (plan parsing library), and site itself, some of the bad calculations are gone.
Let's see some examples:
Continue reading New changes on explain.depesz.com – fixed calculations of exclusive times
Some time ago I wrote about new options for explains – one that prints settings that were modified from default.
This looks like this:
Aggregate (cost=35.36..35.37 rows=1 width=8) -> Index Only Scan using pg_class_oid_index on pg_class (cost=0.27..34.29 rows=429 width=0) Settings: enable_seqscan = 'off'
Finally, today, I pushed a change that displays them on explain.depesz.com. To make it more usable, it will also provide links to documentation about each such options.
For example, check this plan.
Hope you'll find it useful.
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 and time is not always there, but generally it should work.
There is also summarized info in stats page of the explain.
If you'd notice anything wrong, please let me know, either by mail, or just make an issue on GitLab.
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.
Continue reading Explaining the unexplainable – part 6: buffers
Some time ago Eugen Konkov mailed me that he'd like to have some changes on explain.depesz.com.
One of the changes was actual bug, but the rest were improvements to functionality.
I kinda didn't want to do it, but when I looked closer it appeared to me that there are some subtle bugs, and when I'll be fixing them, I can add some of the things Eugen requested: