4 ms·

With deduplicatio enabled, how does it differ from GIN index now?

pgaddict 6y ago
Interesting question. Considering how different the gid and btree indexes are, I'd say it's very different. GIN essentially builds a posting list for each value in the column, and compresses that (which might be seen as a kind of a bitmap index). OTOH btree deduplication simply compares index keys that end up on the same leaf page. So there's on "global" index compression, it's much more localized. But I suppose Peter and Anastasia have more thoughts on this ...
avremel 6y ago
> The big difference is that duplicate index entries can still occur in B-tree indexes. GIN indexes have to integrate new entries into the posting list every time, which makes data modifications less efficient (this is mitigated by the pending list). In contrast, PostgreSQL deduplicates B-tree entries only when it would otherwise have to split the index page. So it has to do the extra work only occasionally, and only when it would have to do extra work anyway. This extra work is balanced by the reduced need for page splits and the resulting smaller index size. https://www.cybertec-postgresql.com/en/b-tree-index-deduplication/ https://www.cybertec-postgresql.com/en/b-tree-index-deduplic...
petergeoghegan 6y ago
GIN is optimized for things like full text search and jsonb indexing. GIN's internal B-Tree/entry tree consists of datums that are decomposed from the data type you're actually indexing rather than the data type itself (e.g. when indexing a column that's an array of ints, the entry tree is actually plain integers). You can now use B-Tree to get something that's roughly comparable to GIN + contrib/btree_gin, though only when indexing a single column -- multi-column indexing in GIN stores each datum from the same row in totally different parts of the tree. This is totally different from B-Tree, which stores all columns that are indexed for the same row together. B-Tree deduplication is only capable of making the index about 3x smaller (though larger reductions are possible when indexing large ), whereas GIN can sometimes manage about 10x because it also compresses TID lists. This on-disk size difference is only apparent when there are a huge number of duplicates. GIN now has a higher on-disk overhead when there are only 3 or 4 duplicates per item. In summary, B-Tree is a performance all-rounder that has good, consistent read and write performance. GIN is more optimized for writes, and for specialized applications like FTS.

Interesting question. Considering how different the gid and btree indexes are, I'd say it's very different. GIN essentially builds a posting list for each value in the column, and compresses that (which might be seen as a kind of a bitmap index). OTOH btree deduplication simply compares index keys that end up on the same leaf page. So there's on "global" index compression, it's much more localized. But I suppose Peter and Anastasia have more thoughts on this ...

avremel 6y ago

> The big difference is that duplicate index entries can still occur in B-tree indexes. GIN indexes have to integrate new entries into the posting list every time, which makes data modifications less efficient (this is mitigated by the pending list). In contrast, PostgreSQL deduplicates B-tree entries only when it would otherwise have to split the index page. So it has to do the extra work only occasionally, and only when it would have to do extra work anyway. This extra work is balanced by the reduced need for page splits and the resulting smaller index size. https://www.cybertec-postgresql.com/en/b-tree-index-deduplication/ https://www.cybertec-postgresql.com/en/b-tree-index-deduplic...

petergeoghegan 6y ago

GIN is optimized for things like full text search and jsonb indexing. GIN's internal B-Tree/entry tree consists of datums that are decomposed from the data type you're actually indexing rather than the data type itself (e.g. when indexing a column that's an array of ints, the entry tree is actually plain integers). You can now use B-Tree to get something that's roughly comparable to GIN + contrib/btree_gin, though only when indexing a single column -- multi-column indexing in GIN stores each datum from the same row in totally different parts of the tree. This is totally different from B-Tree, which stores all columns that are indexed for the same row together. B-Tree deduplication is only capable of making the index about 3x smaller (though larger reductions are possible when indexing large ), whereas GIN can sometimes manage about 10x because it also compresses TID lists. This on-disk size difference is only apparent when there are a huge number of duplicates. GIN now has a higher on-disk overhead when there are only 3 or 4 duplicates per item. In summary, B-Tree is a performance all-rounder that has good, consistent read and write performance. GIN is more optimized for writes, and for specialized applications like FTS.