Re: PostgreSQL 10.0 SELECT LIMIT performance problem

=?utf-8?B?TWFyZWtzIEthbG5hxI1z?= <Mareks(dot)Kalnacs(at)datakom(dot)lv> writes:
> But we are using value indexes not a json index:

> CREATE INDEX idx_oss_alarms_dn
> ON oss_alarms
> USING btree
> ((jdata ->> 'dn'::text) COLLATE pg_catalog."default");

I think you're outsmarting yourself by including those COLLATE clauses.
They don't do anything, since they're just selecting the default behavior
--- but they're enough to make the planner not realize that stats
collected on the index expression would be applicable to a plain
reference to oss_alarms.jdata ->> 'dn'. In general you want the index
expression to be spelled exactly the same way that you refer to the
value in queries, else the system may not realize it's relevant.

regards, tom lane