RE: PostgreSQL 10.0 SELECT LIMIT performance problem

Hi!

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");

CREATE INDEX idx_oss_objects_sid
ON oss_objects
USING btree
((jdata ->> 'sid'::text) COLLATE pg_catalog."default");

CREATE UNIQUE INDEX idx_tvc_entity_sid_u
ON tvc_entity
USING btree
((jdata ->> 'siteId'::text) COLLATE pg_catalog."default")
WHERE (jdata ->> 'siteId'::text) IS NOT NULL;

May be we don’t understand this index behavior?

Mareks

From: Victor Yegorov <vyegorov(at)gmail(dot)com>
Sent: trešdiena, 2018. gada 12. septembris 19:43
To: Mareks Kalnačs <Mareks(dot)Kalnacs(at)datakom(dot)lv>
Cc: pgsql-bugs(at)postgresql(dot)org; Māris Rucis <Maris(dot)Rucis(at)datakom(dot)lv>
Subject: Re: PostgreSQL 10.0 SELECT LIMIT performance problem

ср, 12 сент. 2018 г. в 19:21, Mareks Kalnačs <Mareks(dot)Kalnacs(at)datakom(dot)lv<mailto:Mareks(dot)Kalnacs(at)datakom(dot)lv>>:
I will disagree with you about bug. I understand that foreign keys will solve some issues and we already making changes add FK, but this only bypass problem, not solve it and in different conditions we got problem again. Main issue, optimizer not take in account index distribution. In current case main cause actually is subselect. I little bit played around with indexes and different select conditions and got very strange behavior. What we done, add new index for tvc_entity table (idx_tvc_entity_sid_u) and run full analyze for all tables and got problem in reverse, now optimizer thinks that he will get less rows from subselect.

The fact, that planner is not accurate on the estimates of JSON internal keys is expected, PostgreSQL is not parsing JSON values when gathering stats.
You cannot expect planner to be picky about all possible corner cases, it would make planning time enormously huge.
That is the reason I outlined, that important keys should be extracted into plain columns.

If you still consider this is a bug, please — send isolated reproducible test case that demonstrates the bug.

--
Victor Yegorov
________________________________

Š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

________________________________