Edit Question - KnowledgeHub

Title Body

From what information I could find, they both solve the same problems - more esoteric operations like array containment and intersection (&&, @>, <@, etc). However I would be interested in advice about when to use one or the other (or neither possibly). The PostgreSQL documentation has some information about this: GIN index lookups are about three times faster than GiST GIN indexes take about three times longer to build than GiST GIN indexes are about ten times slower to update than GiST GIN indexes are two-to-three times larger than GiST However I would be particularly interested to know if there is a performance impact when the memory to index size ration starts getting small (ie. the index size becomes much bigger than the available memory)? I've been told on the #postgresql IRC channel that GIN needs to keep all the index in memory, otherwise it won't be effective, because, unlike B-Tree, it doesn't know which part to read in from disk for a particular query? The question would be: is this true (because I've also been told the opposite of this)? Does GiST have the same restrictions? Are there other restrictions I should be aware of while using one of these indexing algorithms?

Tags (comma-separated) Cancel