Would it be possible to utilize a GIN index to query for distinct values ?

From: Danny Shemesh <dany74q(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Would it be possible to utilize a GIN index to query for distinct values ?
Date: 2022-05-24 09:36:47
Message-ID: CAFZC=QojumgqutQBO_v3JT3Vqbr86O0L8ZtSzQvT79tVfqzLgg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hey everyone !

Bumping an older thread
<https://www.postgresql.org/message-id/flat/CA%2BKBOKwJkG5_vMi3W6D9mXMbLJA_i4qrpPfa-Kg5EqLvBBMYow%40mail.gmail.com>,
I've read the GIN readme in the code base and have skimmed through the
implementation, it made me wonder -
would it be possible to use the index to query for distinct / count
distinct values, at least for some key types ?

For instance, say I have a GIN index on a single, highly cardinal but
non-unique text column (a-la 'name'); from my very limited understanding,
would it be possible to query for distinct / count distinct values via
roughly:
- Traversing the GIN entry tree tuples
- Gathering the key data from said tuples
- Discarding keys with no / empty posting lists, as they aren't discarded
from the tree
- Traversing the pending list for indices with fastupdate & merging the
result set

I'm probably off by quite a lot, but I'd really appreciate your great
insight on the above.

Thanks !
Danny

Browse pgsql-general by date

  From Date Subject
Next Message Amit Kapila 2022-05-24 09:38:41 Re: How is this possible "publication does not exist"
Previous Message Durumdara 2022-05-24 06:17:26 Re: Can I start Update row in After Insert trigger function?