Waiting for 9.2 – Index only scans

On 8th of October, Tom Lane committed patch:

Support index-only scans using the visibility map to avoid heap fetches.
 
When a btree index contains all columns required by the query, and the
visibility map shows that all tuples on a target heap page are
visible-to-all, we don't need to fetch that heap page.  This patch depends
on the previous patches that made the visibility map reliable.
 
There's a fair amount left to do here, notably trying to figure out a less
chintzy way of estimating the cost of an index-only scan, but the core
functionality seems ready to commit.
 
Robert Haas and Ibrar Ahmed, with some previous work by Heikki Linnakangas.

Continue reading Waiting for 9.2 – Index only scans

Waiting for 9.1 – Per-column collation support

On 8th of February, Peter Eisentraut committed patch:

Per-column collation support
 
This adds collation support for columns and domains, a COLLATE clause
to override it per expression, and B-tree index support.
 
Peter Eisentraut
reviewed by Pavel Stehule, Itagaki Takahiro, Robert Haas, Noah Misch
 
Branch
------
master

Continue reading Waiting for 9.1 – Per-column collation support

Waiting for 9.1 – Add UNIQUE/PRIMARY KEY with index

On 25th of January, Tom Lane committed patch:

Implement ALTER TABLE ADD UNIQUE/PRIMARY KEY USING INDEX.
 
This feature allows a UNIQUE OR pkey CONSTRAINT TO be created USING an
already-existing UNIQUE INDEX.  While the CONSTRAINT isn't very
functionally different from the bare index, it's nice TO be able TO do that
FOR documentation purposes.  The main advantage OVER just issuing a plain
ALTER TABLE ADD UNIQUE/PRIMARY KEY IS that the INDEX can be created WITH
CREATE INDEX CONCURRENTLY, so that there IS NOT a long INTERVAL WHERE the
TABLE IS locked against updates.
 
ON the way, refactor SOME OF the code IN DefineIndex() AND index_create()
so that we don't have to pass through those functions in order to create
the index constraint's catalog entries.  Also, IN parse_utilcmd.c, pass
around the ParseState pointer IN struct CreateStmtContext TO save ON
notation, AND ADD error location pointers TO SOME error reports that didn't
have one before.
 
Gurjeet Singh, reviewed by Steve Singer and Tom Lane

Continue reading Waiting for 9.1 – Add UNIQUE/PRIMARY KEY with index

Waiting for 9.1 – KNNGIST

On 4th of December, Tom Lane committed really cool patch:

KNNGIST, otherwise known as order-by-operator support for GIST.

This commit represents a rather heavily editorialized version of
Teodor's builtin_knngist_itself-0.8.2 and builtin_knngist_proc-0.8.1
patches.  I redid the opclass API to add a separate Distance method
instead of turning the Consistent method into an illogical mess,
fixed some bit-rot in the rbtree interfaces, and generally worked over
the code style and comments.
 
There's still no non-code documentation to speak of, but I'll work on
that separately.  Some contrib-module changes are also yet to come
(right now, point <-> point is the only KNN-ified operator).
 
Teodor Sigaev and Tom Lane

Continue reading Waiting for 9.1 – KNNGIST

Tips n’ Tricks – using “wrong” index

More than once I've seen situation when there is a table, with serial primary key, and rows contain also some kind of creation timestamp, which is usually monotonic, or close to monotonic.

Example of such case are for example comments or posts in forums – each get it's ID, but they also have creation timestamp. And it usually is so that higher ids were added later than the lower ids.

So, let's assume you have such table, and somebody asks you to make a report on data from last month. How?

Continue reading Tips n' Tricks – using “wrong" index

Waiting for 8.5 – better messages for unique violation

On 1st of August Tom Lane committed patch from Itagaki Takahiro:

Improve unique-constraint-violation error messages to include the exact
values being complained of.
 
In passing, also remove the arbitrary length limitation in the similar
error detail message for foreign key violations.
 
Itagaki Takahiro

and later added an extension to it:

Department of second thoughts: let's show the exact key during unique index
build failures, too.  Refactor a bit more since that error message isn't
spelled the same.

Continue reading Waiting for 8.5 – better messages for unique violation