0

I have a table containing a one-dimensional array attribute of type VARCHAR.

CREATE TABLE IF NOT EXISTS table_name (
    attribute VARCHAR(64) ARRAY DEFAULT NULL
);

I'd like to have this type indexed, so I tried to create a GIN type index like so:

CREATE INDEX table_ix ON table_name USING GIN (attribute);

Unfortunately it seems like the index is not being picked up by PostgreSQL's execution plan. Running EXPLAIN ANALYZE [...] yields the following result:

Seq Scan on table_name  (cost=0.00..2.15 rows=11 width=717) (actual time=0.010..0.019 rows=11 loops=1)
  Filter: (attribute @> '{VALUE}'::character varying[])
  Rows Removed by Filter: 1
Planning time: 0.201 ms
Execution time: 0.033 ms

I even tried to disable the sequential scan using SET ENABLE_SEQSCAN TO OFF;, but the result is still the same, the index is still not used.

As you can see, the table itself currently contains very little rows, only 11 in fact, is it possible PostgreSQL decides to completely ignore the index because of the low amount of rows but will actually use the index when the number of records grows? Or do I perhaps have the index setup incorrectly?

Andy
  • 1,127
  • 2
  • 12
  • 25
  • For just 11 rows Postgres will probably never use an index. The are probably all stored on just one or two database blocks anyway. If you use `explain (analyze, buffers)` you can probably see that –  Nov 30 '18 at 08:32
  • @a_horse_with_no_name, so my assumption that the index is in fact correctly specified and just isn't used solely because of the low count of rows might be correct? – Andy Nov 30 '18 at 09:01
  • Yes, that assumption is correct. –  Nov 30 '18 at 09:03

0 Answers0