Last time I wrote about what explain output shows. Now I'd like to talk more about various types of “nodes" / operations that you might see in explain plans.
Tag: index
Waiting for 9.3 – Support indexing of regular-expression searches in contrib/pg_trgm.
On 9th of April, Tom Lane committed patch:
Support indexing of regular-expression searches in contrib/pg_trgm. This works by extracting trigrams from the given regular expression, in generally the same spirit as the previously-existing support for LIKE searches, though of course the details are far more complicated. Currently, only GIN indexes are supported. We might be able to make it work with GiST indexes later. The implementation includes adding API functions to backend/regex/ to provide a view of the search NFA created from a regular expression. These functions are meant to be generic enough to be supportable in a standalone version of the regex library, should that ever happen. Alexander Korotkov, reviewed by Heikki Linnakangas and Tom Lane
One day later Tom Lane added support for the same operations using GiST indexes (original patch was working only with GIN).
Index on (a,b) vs. (b,a)?
Whenever you are considering creation of multicolumn index, there is a question what should be the order of columns in this index. I'll try to analyze various cases of this situation.
“= 123″ vs. “= ‘depesz’” – followup
Yesterday I wrote about selects on int4 vs. texts.
One of the comments that caught my attention was question about index creation time. So, let's see…
“= 123” vs. “= ‘depesz'”. What is faster?
There is this idea that normal form in databases require you to use integer, auto incrementing, primary keys.
The idea was discussed by many people, I will just point you to series of three blog posts on the subject by Josh Berkus ( part 1, 2 and 3, and reprise).
One of the points that proponents of surrogate keys (i.e. those based on integer and sequences) raise is that comparing integers is faster than comparing texts. So,
select * from users where id = 123
is faster than
select * from users where username = 'depesz'
Is it?
Waiting for 9.2 – DROP INDEX CONCURRENTLY
On 6th of April, Simon Riggs committed patch:
Add DROP INDEX CONCURRENTLY [IF EXISTS], uses ShareUpdateExclusiveLock
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.
What index to create?
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.
IMPORTANT UPDATE: As of PostgreSQL 10 hash indexes are WAL logged. As such, main point against them is gone.
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