The manual for the pg_trgm module has some advice for your question here:
As a rule of thumb, a GIN index is faster to search than a GiST index,
but slower to build or update; so GIN is better suited for static data
and GiST for often-updated data.
The FASTUPDATE
feature of GIN indexes (introduced in Postgres 8.4, ON by default) should be interesting for you. INSERTs (and UPDATEs) are stored in a pending list and integrated into the index in later bulk operations by VACUUM
. Makes those operations a lot faster, since direct GIN maintenance tends to be expensive, especially for columns with many indexable elements, i.e. many words in your case.
More basic advice on GIN vs GiST in the manual here.
In particular, if your rows are big and you insert in bulks, you may want to raise the work_mem
setting.
For very big loads (large parts of the table), it might pay to drop the index and recreate it afterwards.
Although, 15k rows spread out over a day, shouldn't be a problem at all.
Finally, there is a whole chapter on performance of GIN and GiST in the Full Text Search section. Applies generally. Read this.
The gist of it (no pun intended): GIN is considerably bigger and more expensive to maintain, but faster for most lookups (special exceptions apply for pg_trgm). Since the addition of FASTUPDATE
writes are only moderately more expensive.
postgres
has no password after an automated install, it's expected to authenticate through the peer
method. sudo su - postgres
is the first step to it. The second step is to connect through the Unix local domain socket, but your command doesn't do that, it connects through TCP, that's why you're stuck with the password problem.
The installed pg_hba.conf
file probably starts with these two rules, apart from comments:
# Database administrative login by Unix domain socket
local all postgres peer
# IPv4 local connections:
host all all 127.0.0.1/32 md5
[...]
When doing a TCP connection with -h localhost...
the first rule doesn't match. The second rule matches and it triggers the demand for a password, but as none was set this can only fail (empty passwords are not allowed either).
The solution is to remove -h localhost
from psql invocation, for the first rule to be taken instead. As it's on a Unix system, it will attempt a connection through the Unix domain socket.
Once logged to psql as the postgres user, you may set a password with the \password
command or ALTER USER postgres PASSWORD 'foobar';
See also https://help.ubuntu.com/community/PostgreSQL
In the section Using pgAdmin III GUI , they suggest to change the pg_hba.conf
first rule from auth peer
to md5
, after having set a password. Personally I don't quite get why. I'd rather leave that rule alone and run pgAdmin from my own Unix account, choosing localhost TCP connections.
Best Answer
The resulting index is a true blood GIN index. These operator classes are not installed by default because they are rarely needed.
A single-column GIN index on a column of scalar type would be inferior to a B-tree index in almost every way.