Every so often you need to get list of unique elements in some column. The standard way to do it is:
SELECT DISTINCT COLUMN FROM TABLE;
or
SELECT COLUMN FROM TABLE GROUP BY COLUMN;
The only problem is that it's slow – as it has to seq scan whole table. Can it be done faster?
Also yesterday, and also Peter Eisentraut, committed patch by Guillaume Smet, which:
Add log_line_prefix placeholder %e to contain the current SQL state Author: Guillaume Smet
Yesterday Peter Eisentraut committed a patch, written by Damien Clochard, that modifies \d output in psql:
Have \d show child tables that inherit from the specified parent As per discussion, \d shows only the number of child tables, because that could be hundreds, when used for partitioning. \d+ shows the actual list. Author: Damien Clochard <damien@dalibo.info>
Continue reading Waiting for 8.5 – Have \d show child tables that inherit from the specified parent
Everybody wrote that 8.4 was released, so it's not a news now.
But. Starting from yesterday, my own PostgreSQL reports it's version like this:
# SELECT version(); version ----------------------------------------------------------------------------------------------------------- PostgreSQL 8.5devel ON i686-pc-linux-gnu, compiled BY GCC gcc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu4), 32-bit (1 ROW)
Which means: I can start new blog posts series.
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.
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
I just updated explain.depesz.com with 2 new interface features:
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).
I just modified the internals of explain.depesz.com. Now, it finally stores the plans in database (previously it stored the plans as files in dedicated directory).
Effect for enduser is just that history page should load faster.
But, having the data in database makes it possible to add more features.
One such feature is already added – ability to explain plan, but not list it on history page.
Right now, when you add new plan, you can specify if you want it to be listed with other previous explains – default value is “yes". But if you don't want just anybody to be able to click his way to your plan – there is option for it.
For obvious reasons all previous plans are marked as public now. If you want it to be changed, please contact me – we can probably do something about it.
And as last thing: I would like to express my big THANK YOU to all of you who use explain.depesz.com.
(side note: Catalyst is really cool)
I just updated explain.depesz.com with the newest explain-parsing library version (Pg::Explain v 0.09).
This version will hit CPAN mirrors in next couple of hours.
Changes:
Changes:
SVN repo at: http://svn.depesz.com/svn/pgsql-tools/trunk