From: Tom Lane <tgl@sss.pgh.pa.us>
To: Victor Yegorov <vyegorov@gmail.com>
Cc: Mareks.Kalnacs@datakom.lv
Cc: pgsql-bugs@postgresql.org
Cc: Maris.Rucis@datakom.lv
Subject: Re: PostgreSQL 10.0 SELECT LIMIT performance problem
Date: Wed, 12 Sep 2018 13:04:11 -0400
Message-ID: <9924.1536771851@sss.pgh.pa.us> (raw)
In-Reply-To: <CAGnEbohCdScdyjtSEPpWWa8CnVzc4NKBisDAFPEoxo9QWxhCOA@mail.gmail.com>
References: <7419b7a3a21d4f1c8509178a071ec636@datakom.lv>
<CAGnEbohpDWcJbuCJexRWW-XsjmPVH=ZYoWn+b8zs5a3xBcawkQ@mail.gmail.com>
<e65c26cc8fae43abbad69b3e51d1579d@datakom.lv>
<CAGnEbohCdScdyjtSEPpWWa8CnVzc4NKBisDAFPEoxo9QWxhCOA@mail.gmail.com>
Victor Yegorov <vyegorov@gmail.com> writes:
> 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.
Right. The fact that it doesn't make the right guesses without help
can't be considered to be a bug in all cases. These are engineering
tradeoffs we have to make.
> That is the reason I outlined, that important keys should be extracted into
> plain columns.
If that seems infeasible from an application standpoint, another
possibility is to make expression indexes on those important keys.
ANALYZE will gather stats on the values of indexed expressions, and then
perhaps the planner will have enough info to make better decisions.
In the other case mentioned, where the problem is a poor guess about
the selectivity of
where (
to_tsvector(
'simple'::regconfig,
a.ts_vector_fields
) @@ to_tsquery(
'simple',
' (decodedType5b6f0753ossAlarmsGenerator) '
)
)
it's the same problem: the planner has no stats that would let it
figure out the selectivity. It can't reasonably extract an
estimate on the fly --- if it did, you'd be complaining that
planning time was too long. The only way to get reasonable behavior
is to set things up so that ANALYZE will accumulate stats about
the values of "to_tsvector('simple',a.ts_vector_fields)". You
can arrange that either by extracting that into a column, or by
making an index on it.
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, vyegorov@gmail.com, Mareks.Kalnacs@datakom.lv, Maris.Rucis@datakom.lv
Subject: Re: PostgreSQL 10.0 SELECT LIMIT performance problem
In-Reply-To: <9924.1536771851@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