Changes on explain.depesz.com

Just pushed some new changes to expain.depesz.com and it's underlying parsing library.

The changes are:

  • If you upload plan in JSON/YAML/XML format, and then you'll look at “TEXT" tab, you will see explain in text format, generated using data from JSON. This explain had slight error in number formatting (3 decimal digits for estimated costs, while text formats use only 2 decimals). THis was reported by ysch on irc. Example plan
  • Numbers in node details (for example: Filtered, or Buffers info) are now commified to make it easier to read. Example plan
  • Fixed parsing of Planning time in JSON, YAML, and XML formats of explains from PostgreSQL 13. Example plans:
    JSON, YAML, and XML.

Some stats, for curious ones:

  • site is up for over 11 years and 6 months
  • so far there have been 843,215 plans added
  • within last 30 days, there have been 484 new plans added daily, on average
  • there are 1295 registered users, but only 780 of them ever added any plans

Grouping data by timestamp and interval

Someone asked today on irc about grouping data, that contains timestamps, into “partitions".

Usually when someone wants something like this, you can do grouping by date_trunc(), but this time, this person, wanted to group data that all timestamps are within given interval from each other.

I'm not sure I understood him/her right, but I think he/she wanted something like this:

Continue reading Grouping data by timestamp and interval

Grouping data into time ranges

Today some guy on IRC asked question, which I didn't fully understand, but which could (probably) be summarized: how to group data into 5 minute intervals, based on some timestamp column.

Well, it seems trivial (as long as you know how to do it), but since he clearly didn't know how to do it (or I misunderstood his problem), here's the explanation:

Continue reading Grouping data into time ranges