Postgres - gin index doesn't work

Question

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

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

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.

P.S.S I've tried to load extension first and then create table, or insert data and then create index... i've tried many combinations but without any luck. — n1_
– n1_, Commented Jan 27, 2013 at 13:35

Daniel Vérité · Accepted Answer · 2013-01-27 13:37:07Z

6

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.

answered Jan 27, 2013 at 13:37

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres - gin index doesn't work

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related