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
Last activity: Aug 17, 2019, 01:37 PM