Thread: Using GIN index to retrieve distinct values

Using GIN index to retrieve distinct values

From
Alexander Hill
Date:
Hi all,

My understanding of a GIN index is that it maps each unique key to the set of items containing that key. That suggests to me that enumerating the distinct keys efficiently using the index should be possible in some cases. I understand that given the nature of the GIN framework, the original key won't always be able to be retrieved. But in certain cases like int array, for example, would it be possible?

While on the topic: what's the canonical way to do that kind of query?

CREATE TABLE tagged_items
(
    id serial PRIMARY KEY,
    tag_ids integer[]
);

select array_agg(tag_id)
from (select distinct unnest(tag_ids) from items) tag_id;

Thanks!
Alex