From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mareks Kalnačs <Mareks.Kalnacs@datakom.lv>
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: Thu, 13 Sep 2018 13:42:10 -0400
Message-ID: <12108.1536860530@sss.pgh.pa.us> (raw)
In-Reply-To: <803c5396974a42849ebec62bfa47f2c8@datakom.lv>
References: <7419b7a3a21d4f1c8509178a071ec636@datakom.lv>
<CAGnEbohpDWcJbuCJexRWW-XsjmPVH=ZYoWn+b8zs5a3xBcawkQ@mail.gmail.com>
<e65c26cc8fae43abbad69b3e51d1579d@datakom.lv>
<CAGnEbohCdScdyjtSEPpWWa8CnVzc4NKBisDAFPEoxo9QWxhCOA@mail.gmail.com>
<803c5396974a42849ebec62bfa47f2c8@datakom.lv>
=?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
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: tgl@sss.pgh.pa.us, Mareks.Kalnacs@datakom.lv, vyegorov@gmail.com, Maris.Rucis@datakom.lv, Pavels.Korzs@datakom.lv
Subject: Re: PostgreSQL 10.0 SELECT LIMIT performance problem
In-Reply-To: <12108.1536860530@sss.pgh.pa.us>
* 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