Re: DISTINCT on jsonb fields and Indexes

On Mon, 22 Jun 2020 at 16:44, Sankar P <sankar(dot)curiosity(at)gmail(dot)com> wrote:
> select distinct record ->> 'destinationServiceName' from fluent;

> This query takes a lot of time, and does full table scan. The query planning is:

> I see that none of the indexes are used. I want to do a few
> aggregations, like "what are the distinct pairs of
> `destinationServiceName` and `sourceServiceName` etc. " in these
> records. Now, is such a querying possible at all without doing full
> table scans ? I get such kind of aggregation support in elasticsearch
> + kibana, without doing full-table scan (I believe so, but I do not
> have data to back this claim) and I am trying to see if this is
> possible with any other extra index creation in postgres.

There is some work in progress to improve this type of query, but
it'll be at least PG14 before we see that.

For your version, you might want to look at
https://wiki.postgresql.org/wiki/Loose_indexscan and try some of the
proposed solutions from there.

David