From: =?windows-1257?Q?Mareks_Kalna=E8s?= <Mareks.Kalnacs@datakom.lv>
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Victor Yegorov <vyegorov@gmail.com>, "pgsql-bugs@postgresql.org"
	<pgsql-bugs@postgresql.org>, =?windows-1257?Q?M=E2ris_Rucis?=
	<Maris.Rucis@datakom.lv>, =?windows-1257?Q?P=E2vels_Kor=FEs?=
	<Pavels.Korzs@datakom.lv>
Subject: RE: PostgreSQL 10.0 SELECT LIMIT performance problem
Thread-Topic: PostgreSQL 10.0 SELECT LIMIT performance problem
Thread-Index: 
 AdRKcrcxKsJ4mGzUR4mNVsCT/UUnVQACLeMAAAfHS+AAAQD+AAAqXvjAAAn58gAAH9v0QA==
Date: Fri, 14 Sep 2018 05:59:50 +0000
Message-ID: <de9da38e4e104fcfa0e4e7303c7a62e4@datakom.lv>
References: <7419b7a3a21d4f1c8509178a071ec636@datakom.lv>
 <CAGnEbohpDWcJbuCJexRWW-XsjmPVH=ZYoWn+b8zs5a3xBcawkQ@mail.gmail.com>
 <e65c26cc8fae43abbad69b3e51d1579d@datakom.lv>
 <CAGnEbohCdScdyjtSEPpWWa8CnVzc4NKBisDAFPEoxo9QWxhCOA@mail.gmail.com>
 <803c5396974a42849ebec62bfa47f2c8@datakom.lv>
 <12108.1536860530@sss.pgh.pa.us>
In-Reply-To: <12108.1536860530@sss.pgh.pa.us>
Accept-Language: en-GB, lv-LV, en-US
Content-Language: en-US
Content-Type: text/plain; charset="windows-1257"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Precedence: bulk

Actually Postgres trying to outsmart himself, I do not add COLLATE ;)

Original trigger text
CREATE INDEX idx_oss_alarms_dn ON oss_alarms ((jdata ->> 'dn'));

Mareks

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: ceturtdiena, 2018. gada 13. septembris 20:42
To: Mareks Kalna=E8s <Mareks.Kalnacs@datakom.lv>
Cc: Victor Yegorov <vyegorov@gmail.com>; pgsql-bugs@postgresql.org; M=E2ris=
 Rucis <Maris.Rucis@datakom.lv>; P=E2vels Kor=FEs <Pavels.Korzs@datakom.lv>
Subject: Re: PostgreSQL 10.0 SELECT LIMIT performance problem

=3D?utf-8?B?TWFyZWtzIEthbG5hxI1z?=3D <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_al=
arms.jdata ->> 'dn'.  In general you want the index expression to be spelle=
d exactly the same way that you refer to the value in queries, else the sys=
tem may not realize it's relevant.

regards, tom lane
________________________________

=D0is elektronisk=E2 pasta s=FBt=EEjums un taj=E2 eso=F0ie dokumenti ir par=
edz=E7ts tikai nor=E2d=EEtajam adres=E2tam(-iem). Tas satur konfidenci=E2lu=
 inform=E2ciju un var satur=E7t ar=EE privili=EC=E7tu inform=E2ciju. T=E2 s=
atura piln=EEga vai da=EF=E7ja nesankcion=E7ta izpau=F0ana, izmanto=F0ana v=
ai t=E2l=E2ka izplat=EE=F0ana jebk=E2d=E2 veid=E2 ir aizliegta. Ja =F0is el=
ektronisk=E2 pasta s=FBt=EEjums sa=F2emts k=EF=FBdas d=E7=EF, l=FBdzam sazi=
n=E2ties ar s=FBt=EEt=E2ju, nos=FBtot atbildes e-pasta v=E7stuli, un izdz=
=E7st =F0o v=E7stuli. Dro=F0=EEbas apsv=E7rumu d=E7=EF SIA Datakom var veik=
t elektronisk=E2 pasta s=FBt=EEjumu un tiem pievienoto dokumentu satura mon=
itoringu. Ja vien =F0aj=E2 v=E7stul=E7 vai pievienotajos dokumentus nav nep=
=E2rprotami teikts pret=E7jais, =F0is elektronisk=E2 pasta s=FBt=EEjums vai=
 tam pievienotie dokumenti nav uzskat=E2mi par SIA Datakom pied=E2v=E2jumu =
sl=E7gt dar=EEjumu, ap=F2em=F0anos vai gatav=EEbu uz=F2emties saist=EEbas. =
S=FBt=EEt=E2js: SIA Datakom, vienotais re=ECistr=E2cijas nr: 40103142605, J=
uridisk=E2 adrese: Vien=EEbas gatve 109, R=EEga, LV-1058, Latvija

This electronic transmission and attached documents is intended only for th=
e addressee(s). It contains confidential information and may also contain p=
rivileged information. If you are not an intended recipient, you may not us=
e, read, retransmit, disseminate or take any action in reliance upon it. If=
 this electronic transmission is received in error, please contact the send=
er by sending a reply e-mail and delete this message. SIA Datakom reserves =
the right to monitor all e-mail communications through its networks. Unless=
 this letter or the accompanying documents clearly stated otherwise, by thi=
s electronic transmission does not constitute a contract offer, a contract =
amendment or an acceptance of a contract offer on behalf of the SIA Datakom=
. Sender: SIA Datakom, registration number: 40103142605, Latvia. Registered=
 office: Vienibas gatve 109, Riga, LV-1058, Latvia

________________________________