Tag: perl

Small improvement for pretty-printing in paste.depesz.com

As you maybe know, some time ago I made paste service, mostly to use for queries, or related text to share on IRC.

One part of it is that it also has pretty printer of provided queries.

Recently I realized that in case of complex join conditions, the output is, well, sub-optimal. For example:

SELECT
    t1.a,
    t2.b,
    t3.c
FROM
    table_1 AS t1
    JOIN table_2 AS t2 ON t1.x = t2.x AND
    t1.y = t2.y AND
    t1.z = t2.z
    LEFT JOIN table_3 AS t3 ON t1.f = t3.f AND
    t2.g = t3.g
WHERE
    t1.v = 1 AND
    t2.t = 'x' AND
    t3.m < 123

Specifically the problem (for me) is bad indentation of join conditions (aside from first).

Anyway – as of now, the same query will be pretty-printed as:

SELECT
    t1.a,
    t2.b,
    t3.c
FROM
    table_1 AS t1
    JOIN table_2 AS t2 ON t1.x = t2.x
        AND t1.y = t2.y
        AND t1.z = t2.z
    LEFT JOIN table_3 AS t3 ON t1.f = t3.f
        AND t2.g = t3.g
WHERE
    t1.v = 1
    AND t2.t = 'x'
    AND t3.m < 123

Also, as a reminder – you can use this pretty printer from command line or some tools, without storing anything on paste.depesz.com site – simply use script that I described earlier.

Hope you'll find it useful.

explain.depesz.com – now with HINTS!

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.

Configuration changes across Pg versions

Back in 2018 I wrote why-upgrade.depesz.com – aggregator of changelogs between various versions of Pg.

Want to know what you will get when upgrading from 12.1 to 12.9? Here you go. Longer time changes? Like, from 9.5.20 to 14.1? I've got your back.

It even has a way to list every change that relates to anything related to indexes.

Today I updated the code, again to show which GUCs (configuration parameters) have changed between given versions. For example, getting diff from 13.5 to 14.1 shows you that:

  • two parameters were removed, and are no longer there
  • there are 17 new parameters
  • three parameters had their default values changed

And each GUC that is listed is (well, is supposed to but in some cases it can't) linked to relevant part of docs that describe what it is.

To make it work I compiled every version of Pg, since 7.2 (there have been 410 of them!), and extracted list of config params, and their default values.

Then, I fetched docs for all major versions of Pg, and extracted list of documentation fragments that relate to each config parameter.

This will require more work on each subsequent release, but I think I can manage it.

Any way – hope you'll find it helpful.

New changes on explain.depesz.com – fixed calculations of exclusive times

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

Display “settings” from plans on explain.depesz.com

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.

explain.depesz.com – now with JITs

Ever since PostgreSQL 11 we have JIT (Just-In-Time compilation).

Information about JIT is displayed in explain analyze plans, but up to this moment, explain.depesz.com didn't display it properly. But not anymore. Thanks to release 1.05 of Pg::Explain and subsequent change in templates for the site, you can now see nice JIT info.

Hope you'll find it useful 🙂