Month: June 2009

Tips N’ Tricks – Generating readable reports with plain SQL

Let's say you imported some data, but it contains duplicates. You will have to handle them in some way, but to make sensible choice on how to handle it, you need more information.

So, let's start. We have table:

# \d users
                                    Table "public.users"
   Column   |           Type           |                     Modifiers
------------+--------------------------+----------------------------------------------------
 id         | integer                  | not null default nextval('users_id_seq'::regclass)
 username   | text                     |
 registered | timestamp with time zone |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

Continue reading Tips N’ Tricks – Generating readable reports with plain SQL

explain.depesz.com – update

I just updated explain.depesz.com with 2 new interface features:

  • When you put mouse cursor over node, it will mark direct child nodes with
  • When you click on node, all child nodes (even indirect) will be hidden, and the node you clicked on will be marked with

Both features are directed towards people who analyze longer plans – ability to hide parts or just visually see what the Nested Loop is calling in 2nd stage greatly helps me see the flow of query – you can test it, for example, here.

By the way – BIG THANK YOU for Metys for help on it (well, actually, for doing it).