Tag: index

Waiting for 9.4 – Introduce jsonb, a structured format for storing json.

Portuguese Brazil Version

On 23rd of March, Andrew Dunstan committed patch:

Introduce jsonb, a structured format for storing json.
 
The new format accepts exactly the same data as the json type. However, it is
stored in a format that does not require reparsing the orgiginal text in order
to process it, making it much more suitable for indexing and other operations.
Insignificant whitespace is discarded, and the order of object keys is not
preserved. Neither are duplicate object keys kept - the later value for a given
key is the only one stored.
 
The new type has all the functions and operators that the json type has,
with the exception of the json generation functions (to_json, json_agg etc.)
and with identical semantics. In addition, there are operator classes for
hash and btree indexing, and two classes for GIN indexing, that have no
equivalent in the json type.
 
This feature grew out of previous work by Oleg Bartunov and Teodor Sigaev, which
was intended to provide similar facilities to a nested hstore type, but which
in the end proved to have some significant compatibility issues.
 
Authors: Oleg Bartunov,  Teodor Sigaev, Peter Geoghegan and Andrew Dunstan.
Review: Andres Freund

Continue reading Waiting for 9.4 – Introduce jsonb, a structured format for storing json.

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).

Continue reading Waiting for 9.3 – Support indexing of regular-expression searches in contrib/pg_trgm.

“= 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?

Continue reading “= 123" vs. “= ‘depesz'". What is faster?

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

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.

Continue reading What index to create?

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