Re: PostgreSQL 10.0 SELECT LIMIT performance problem

On 9/12/18 7:04 PM, Tom Lane wrote:
> Victor Yegorov <vyegorov(at)gmail(dot)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.

Hi,

For what it can help, I wrote this article explaining that:
https://blog.anayrat.info/en/2017/11/26/postgresql---jsonb-and-statistics/

Regards,