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
Some time ago I wrote a blogpost about why index might not be used.
While this post seemed to be well received (top link from depesz.com on reddit), it doesn't answer another question – what index to create for given situation.
I'll try to cover this question now.
Continue reading What index to create?
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.
reviewed by Pavel Stehule, Itagaki Takahiro, Robert Haas, Noah Misch
Continue reading Waiting for 9.1 – Per-column collation support
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
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
This question (and its variants) show quite often on #postgresql on IRC. People get sequential scans, and are worried that it's slow and bad.
So, I hope that this blogpost will shed some light on the subject why indexes are being chosen to be used, or not.
Continue reading Why is my index not being used?
Today, Mattias|farm on IRC asked how to create primary key using HASH index. After some talk, he said that in some books it said that for “=" (equality) hash indexes are better.
So, I digged a bit deeper.
Continue reading Should you use HASH 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