Sample Header Ad - 728x90

Any drawbacks of using GIN PostgreSQL index for an integer foreign key?

4 votes
3 answers
4057 views
I have a large table (600 millions rows) with a foreign key other_id of type integer. A single value of the foreign key is repeated about 100 times on average. I need to have an index on that FK column because the data is frequently selected by other_id. My tests show that the gin index type is about 10 times smaller and about 3 times as performant as the default btree index (the performance was tested using SELECT queries). The question is are there any real-world drawbacks of using the gin index instead of the btree index? It looks like this index type isn't used much for a very common case like mine, i.e. an integer foreign key. But my tests show vast performance gains. Why then gin isn't recommended for such scenarios? I had to execute CREATE EXTENSION btree_gin to be able to use the gin index for the integer column. I know about the UPDATE being possibly slow due to FASTUPDATE being enabled by default: https://dba.stackexchange.com/questions/202583/occasional-intermittent-slow-10-second-update-queries-on-postgresql-table-wi I care only about the equality = operator being able to use the index (also, IN (...) with a possibly large number of values but I assume this is also equality).
Asked by Artur (41 rep)
Aug 16, 2019, 10:13 AM
Last activity: Aug 17, 2019, 01:37 PM