Michael Lewis 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,
Michael Lewis 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,
>
> 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.
Michael Lewis writes:
> On Sun, Jun 21, 2020 at 10:43 PM Sankar P
> wrote:
>> 2) CREATE INDEX idx_btree_records ON fluent USING BTREE ((record ->>
>> 'destinationServiceName'));
> If it is rarely used, create a partial index perhaps. I am a little
> surprised that the plain btree index wasn't us
On Sun, Jun 21, 2020 at 10:43 PM Sankar P
wrote:
> I have a table with the schema:
>
> CREATE TABLE fluent (id BIGSERIAL, record JSONB);
>
> Then I created a couple of indexes:
> 1) CREATE INDEX idx_records ON fluent USING GIN (record);
>
What about using non-default jsonb_path_ops?
> 2) CREA
>
> There is some work in progress to improve this type of query, but
> it'll be at least PG14 before we see that.
oh okay.
>
> 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.
Thanks a lot :)
--
On Mon, 22 Jun 2020 at 16:44, Sankar P 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
