Waiting for 8.4 – Window Functions – teaser

Yesterday Tom Lane committed enormous patch, which had commit log:

Support window functions a la SQL:2008.
 
Hitoshi Harada, with some kibitzing from Heikki and Tom.

… and that would be all. I will write more about it, its use cases, and some detailed examples but now I'm on vacation, and will stay here for some time. You can expect to get the post mid next-week.

Waiting for 8.4 – Default values for function arguments + integer in any base

On 4th of December Peter Eisentraut committed patch by Pavel Stehule (with Peters tweaks) which adds default values for function arguments:

Default values for function arguments
 
Pavel Stehule, with some tweaks by Peter Eisentraut

Continue reading Waiting for 8.4 – Default values for function arguments + integer in any base

Waiting for 8.4 – Visibility maps

Yeah. This one patch alone is worth upgrading to 8.4.

On 3rd of December Heikki Linnakangas committed his patch. Commit message:

Introduce visibility map. The visibility map is a bitmap with one bit per
heap page, where a set bit indicates that all tuples on the page are
visible to all transactions, and the page therefore doesn't need
vacuuming. It is stored in a new relation fork.
 
Lazy vacuum uses the visibility map to skip pages that don't need
vacuuming. Vacuum is also responsible for setting the bits in the map.
In the future, this can hopefully be used to implement index-only-scans,
but we can't currently guarantee that the visibility map is always 100%
up-to-date.
 
In addition to the visibility map, there's a new PD_ALL_VISIBLE flag on
each heap page, also indicating that all tuples on the page are visible to
all transactions. It's important that this flag is kept up-to-date. It
is also used to skip visibility tests in sequential scans, which gives a
small performance gain on seqscans.

Continue reading Waiting for 8.4 – Visibility maps

explain.depesz.com.

Long time ago I wrote small program to filter EXPLAIN ANALYZE output, and add summary of time.

A bit later (I guess, I don't recall exact time line, it could have been earlier) Michael Glaesemann started explain-analyze.info – cool tool for checking what might be wrong with given plan.

I'm not really happy with the emphasis Michael put on bad rowcount estimates, so I decided to write my own tool. Enter explain.depesz.com.

Basic idea is: paste your explain analyze plan, and see the output. You can click on column headers to let it know which parameter is the most important for you – exclusive node time, inclusive node time, or rowcount mis-estimate.

It is definitely not perfect. I know of at least 1 bug now, and will fix it in not-distant future.

But, as for now – you can test it, play it, or simply use it. If you'd like to change/fix something – sources are freely available. Just be warned – it's Perl ;-P

Getting list of most common domains

Today, on #postgresql on IRC, guy (can't contact him now to get his permission to name him), said:

I have a table called problematic_hostnames. It contains a list of banned hostnames in column “hostname" (varchar). I would like to display the top 10 troll ISPs based on this. Does PG have a way of spotting a “pattern"? Some ISPs are example.net while others are foo.bar.example.net, so you can't just regexp the last X.Y (since that would cause “.co.uk" to be one of the top troll ISPs).

Continue reading Getting list of most common domains