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(at)sss(dot)pgh(dot)pa(dot)us>
Sent: ceturtdiena, 2018. gada 13. septembris 20:42
To: Mareks Kalnačs <Mareks(dot)Kalnacs(at)datakom(dot)lv>
Cc: Victor Yegorov <vyegorov(at)gmail(dot)com>; pgsql-bugs(at)postgresql(dot)org; Māris Rucis <Maris(dot)Rucis(at)datakom(dot)lv>; Pāvels Koržs <Pavels(dot)Korzs(at)datakom(dot)lv>
Subject: 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
________________________________

Šis elektroniskā pasta sūtījums un tajā esošie dokumenti ir paredzēts tikai norādītajam adresātam(-iem). Tas satur konfidenciālu informā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ļūdas dēļ, 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 pievienotie dokumenti 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 information and 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, please contact the sender 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 this 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

________________________________