Re: BUG #16356: Can't perform json operations on casted text in where clause when a join is used. WHERE is too early

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> It's somehow resurrected the value of 'a' that was well-eliminated prior to
> this where clause. So what gives? Why does the join cause it to apply the
> last where clause (which should happen logically last) too early?

The documentation explicitly disclaims any specific evaluation order for
WHERE clauses, cf

https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-EXPRESS-EVAL

If you really need to, you can force the matter by putting some type of
optimization fence into the sub-select ("OFFSET 0" is the traditional
way, or you can use WITH ... AS MATERIALIZED in v12 and up). This is
typically disastrous for performance, of course. Not being able to
filter rows before joining would make the join much slower.

I'll just note that the sort of EAV schema that you have here is widely
agreed to be an anti-pattern in database design.

regards, tom lane