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.
First of all, as of today, I added new feature, called anonymization.
It is for all of the people who are afraid that their plans contain information that they don't want to (or can't) share on the internet.
It works like this:
Continue reading explain.depesz.com – new feature and some new stats
Today, I released 2 new versions (new features in 0.5, and then quick bugfix in 0.51) of Pg::Explain Perl library.
This is the base of explain.depesz.com – the code that does heavy lifting of parsing plans, and providing them in uniform way (object).
Continue reading Big changes on explain.depesz.com
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.
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.
Continue reading Two years of explain.depesz.com
This question (and its variants) show quite often on #postgresql on IRC. People get sequential scans, and are worried that it's slow and bad.
So, I hope that this blogpost will shed some light on the subject why indexes are being chosen to be used, or not.
Continue reading Why is my index not being used?
I just checked, and apparently explain.depesz.com is online for over a year now. First plans was added on 2008-12-04 13:20:43+01.
Since then there have been 3602 plans added, 770 of them were set to be private (not displayed on previous explains page.
Longest plan has 2267194 characters (yes, 2.2 megabytes of explain analyze output!), but it's not public, so I can't tell you the url. Longest public plan is this monstrosity, which (while being only explain output, not explain analyze) has 503585 characters.
During this time, there have been 61 days that nobody added any plan on (perhaps problems with software?).
The most heavily used day was 23rd of June 2009, when 71 new plans have been added (4 private).
Thank you all for using it. I hope you find it useful, and I promise, that one day, I will finally sit down, and add all long-due features (like work with new explain formats, fixing some output glitches, stats of used objects per plan).
If you'd like to take a peek at how it works – source of explain.depesz.com is always available.
Today small, but (at least for me) really useful patch. Committed on 15th of December by Robert Haas, and written by Itagaki Takahiro, this patch does:
Add an EXPLAIN (BUFFERS) option to show buffer-usage statistics.
This patch also removes buffer-usage statistics from the track_counts
output, since this (or the global server statistics) is deemed to be a better
interface to this information.
Itagaki Takahiro, reviewed by Euler Taveira de Oliveira.
Continue reading Waiting for 8.5 – buffers info for explain
After very long discussions, on 10th of August, Tom Lane committed patch by Robert Haas:
Extend EXPLAIN to support output in XML or JSON format.
There are probably still some adjustments to be made in the details
of the output, but this gets the basic structure in place.
Continue reading Waiting for 8.5 – Machine readable EXPLAIN
I just updated explain.depesz.com with bugfix, which changes the way Bitmap Index Scan and Bitmap Heap Scan are displayed. Apparently index and table names were not shown previously.
Thanks go to Viktor Rosenfeld for spotting and reporting the bug.