From: Mareks Kalnaès <Mareks.Kalnacs@datakom.lv>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Victor Yegorov <vyegorov@gmail.com>
Cc: pgsql-bugs@postgresql.org <pgsql-bugs@postgresql.org>
Cc: Mâris Rucis <Maris.Rucis@datakom.lv>
Cc: Pâvels Korþs <Pavels.Korzs@datakom.lv>
Subject: RE: PostgreSQL 10.0 SELECT LIMIT performance problem
Date: Fri, 14 Sep 2018 05:59:50 +0000
Message-ID: <de9da38e4e104fcfa0e4e7303c7a62e4@datakom.lv> (raw)
In-Reply-To: <12108.1536860530@sss.pgh.pa.us>
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>
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 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
________________________________
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: Mareks.Kalnacs@datakom.lv, tgl@sss.pgh.pa.us, vyegorov@gmail.com, Maris.Rucis@datakom.lv, Pavels.Korzs@datakom.lv
Subject: RE: PostgreSQL 10.0 SELECT LIMIT performance problem
In-Reply-To: <de9da38e4e104fcfa0e4e7303c7a62e4@datakom.lv>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox