CODEWITHSUNDEEP
postgresqlfull-text-search
n1_
n1_

Reputation: 4397

Postgres - gin index doesn't work

it seems that my server won't use gin index.

  1. I've created a new database with one table.
  2. I've inserted one row as example.
  3. I've loaded trigram extension and created gin index using trigrams
  4. But when I check if the index works right I can see it doesn't
  5. Any ideas?

SQL: http://pastebin.com/1yDQQA1Z

P.S. A day ago I've followed a tutorial about trigrams. Basically it was the same like my example above. The table had 2 columns, numeric(5, 0) and character varying (the one with gin trgm index). Query was with like operator using "%" and index was working (I could see Bitmap using in query explain), so I know, my server can use index (and its properly installed).

Thanks in advance.

Upvotes: 2

Views: 1768

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61516

Don't test on one row, it is meaningless.

Here's an excerpt of the documentation explaining why, in Examining Index Usage:

Use real data for experimentation. Using test data for setting up indexes will tell you what indexes you need for the test data, but that is all.

It is especially fatal to use very small test data sets. While selecting 1000 out of 100000 rows could be a candidate for an index, selecting 1 out of 100 rows will hardly be, because the 100 rows probably fit within a single disk page, and there is no plan that can beat sequentially fetching 1 disk page.

Upvotes: 6

Related Questions