Re: DISTINCT on jsonb fields and Indexes

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Michael Lewis <mlewis(at)entrata(dot)com>
Cc: Sankar P <sankar(dot)curiosity(at)gmail(dot)com>, PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: DISTINCT on jsonb fields and Indexes
Date: 2020-06-22 22:00:54
Message-ID: 2093791.1592863254@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Michael Lewis <mlewis(at)entrata(dot)com> writes:
>> In the example of "select distinct expression", the planner will never
>> notice that that expression has anything to do with an index.

> Thanks for that explanation. I assume re-writing as a 'group by' would have
> no bearing on that planner decision.

Hmm ... actually, now that you mention it, it might. Using GROUP BY
moves the expression into the category of "things we might want to
sort on", and we do pay attention to indexes for that. Trying it
just now, I find that I can get a index scan but not an index-only
scan for something like "select expr from tab group by expr".
Not sure if that will move the needle enough to help for the
original problem.

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2020-06-22 22:04:42 Re: scram-sha-256 encrypted password in pgpass
Previous Message Daniel Verite 2020-06-22 22:00:37 RE: autovacuum failing on pg_largeobject and disk usage of the pg_largeobject growing unchecked