RE: PostgreSQL 10.0 SELECT LIMIT performance problem

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čs <Mareks.Kalnacs@datakom.lv>
Cc: Victor Yegorov <vyegorov@gmail.com>; pgsql-bugs@postgresql.org; Māris Rucis <Maris.Rucis@datakom.lv>; Pāvels Koržs
<Pavels.Korzs@datakom.lv>
Subject: Re: PostgreSQL 10.0 SELECT LIMIT performance problem

=?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
toa plain reference to oss_alarms.jdata ->> 'dn'.  In general you want the index expression to be spelled exactly the
sameway that you refer to the value in queries, else the system may not realize it's relevant. 

regards, tom lane
________________________________

Šis elektroniskā pasta sūtījums un tajā esošie dokumenti ir paredzēts tikai norādītajam adresātam(-iem). Tas satur
konfidenciāluinformāciju un var saturēt arī priviliģētu informāciju. Tā satura pilnīga vai daļēja nesankcionēta
izpaušana,izmantošana vai tālāka izplatīšana jebkādā veidā ir aizliegta. Ja šis elektroniskā pasta sūtījums saņemts
kļūdasdēļ, lūdzam sazināties ar sūtītāju, nosūtot atbildes e-pasta vēstuli, un izdzēst šo vēstuli. Drošības apsvērumu
dēļSIA Datakom var veikt elektroniskā pasta sūtījumu un tiem pievienoto dokumentu satura monitoringu. Ja vien šajā
vēstulēvai pievienotajos dokumentus nav nepārprotami teikts pretējais, šis elektroniskā pasta sūtījums vai tam
pievienotiedokumenti nav uzskatāmi par SIA Datakom piedāvājumu slēgt darījumu, apņemšanos vai gatavību uzņemties
saistības.Sūtītājs: SIA Datakom, vienotais reģistrācijas nr: 40103142605, Juridiskā adrese: Vienības gatve 109, Rīga,
LV-1058,Latvija 

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

________________________________