Re: PostgreSQL 10.0 SELECT LIMIT performance problem

ср, 12 сент. 2018 г. в 19:21, Mareks Kalnačs <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