CODEWITHSUNDEEP
postgresqlpostgresql-9.5
HellOfACode
HellOfACode

Reputation: 1743

What is the best type of index to use on a materialized view in PostgreSQL

I want to increase the performance of queries on table in Postgrsql db i need to use.

CREATE TABLE mytable (
    article_number text NOT NULL,
    description text NOT null,
    feature text NOT null,
    ...
);

The table is just in example but the thing is that there are no unique columns. article_number is the one used in the where clause but for example article_number='000.002-00A' can have from 3 to 300 rows. The total number of rows is 102,165,920. What would be the best index to use for such a situation?

I know there B-tree, Hash, GiST, SP-GiST, GIN and BRIN index types in postgres but which one would be the best for this.

Upvotes: 0

Views: 1973

Answers (1)

Masklinn
Masklinn

Reputation: 42207

If the lookups are filtered on article_number then an index should be created on that. Not quite sure what else you're asking.

The default index is a btree and that'll work fine. If you're only checking for strict equality hash would also be an option but it has issues before Postgres 10, so I wouldn't recommend it.

Other index types are for more complicated forms of querying or custom data types, there's no reason to even consider them if you just want to perform equality filters.

  • btrees are useful for strict equality and range searches (which includes prefix search e.g. foo like 'bar%')
  • hash indexes are useful only for strict equality they can be faster & smaller than btrees in some rare cases
  • GIN indexes are useful when you have multiple index values per row (arrays, json, gis, some FTS cases)
  • GiST indexes are useful for more complex querying than equality and range (geom/gis, FTS)

I've never looked into BRIN index so I'm not sure what their use case would be. But my understanding is that there's no case to even consider it before you have huge numbers of rows.

Basically, use btree unless you know that you can not.

Upvotes: 1

Related Questions