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.
