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
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 |