From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?utf-8?B?TWFyZWtzIEthbG5hxI1z?= <Mareks.Kalnacs@datakom.lv>
cc: Victor Yegorov <vyegorov@gmail.com>,
        "pgsql-bugs@postgresql.org" <pgsql-bugs@postgresql.org>,
        =?utf-8?B?TcSBcmlzIFJ1Y2lz?= <Maris.Rucis@datakom.lv>,
        =?utf-8?B?UMSBdmVscyBLb3LFvnM=?= <Pavels.Korzs@datakom.lv>
Subject: Re: PostgreSQL 10.0 SELECT LIMIT performance problem
In-reply-to: <803c5396974a42849ebec62bfa47f2c8@datakom.lv>
References: <7419b7a3a21d4f1c8509178a071ec636@datakom.lv>
 <CAGnEbohpDWcJbuCJexRWW-XsjmPVH=ZYoWn+b8zs5a3xBcawkQ@mail.gmail.com>
 <e65c26cc8fae43abbad69b3e51d1579d@datakom.lv>
 <CAGnEbohCdScdyjtSEPpWWa8CnVzc4NKBisDAFPEoxo9QWxhCOA@mail.gmail.com>
 <803c5396974a42849ebec62bfa47f2c8@datakom.lv>
Comments: In-reply-to =?utf-8?B?TWFyZWtzIEthbG5hxI1z?=
 <Mareks.Kalnacs@datakom.lv>
	message dated "Thu, 13 Sep 2018 10:03:49 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <12107.1536860530.1@sss.pgh.pa.us>
Date: Thu, 13 Sep 2018 13:42:10 -0400
Message-ID: <12108.1536860530@sss.pgh.pa.us>
Precedence: bulk

=?utf-8?B?TWFyZWtzIEthbG5hxI1z?= <Mareks.Kalnacs@datakom.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