Using GIN index to retrieve distinct values

From: Alexander Hill <alex(at)hill(dot)net(dot)au>
To: pgsql-general(at)postgresql(dot)org
Subject: Using GIN index to retrieve distinct values
Date: 2020-04-22 04:17:23
Message-ID: CA+KBOKwJkG5_vMi3W6D9mXMbLJA_i4qrpPfa-Kg5EqLvBBMYow@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2020-04-22 07:19:05 Re: how to slow down parts of Pg
Previous Message Virendra Kumar 2020-04-22 02:43:22 Re: how to slow down parts of Pg