Re: Optimizer improvements: to do or not to do?

"Say42" <andrews42(at)yandex(dot)ru> writes:
> ... Let's take my pervious example (I repost query and some lines
> from 'explain' here for convenience):

> select count(*) from conn.conn20060803 c where
> exists (select code from belg_mobile tc
> where c.bnum >= tc.code and c.bnum like tc.code || '%'
> order by tc.code desc limit 1)

I'm having a hard time getting excited about improving this query when
it's so badly coded in the first place. What's an ORDER BY doing in
an EXISTS subquery? The LIMIT is unnecessary too. And the inner WHERE
says nothing so much as "I don't know how to design a database" :-(.
If we're going to look at specific examples we should at least look
at examples that are representative of typical good practice.

It is true that EXISTS() subqueries are planned independently without
any idea of how often they might get re-executed. This would be good
to fix but I don't see any clear way to do it --- at the time we are
processing the outer WHERE, we don't have enough context to judge
how many times a particular clause might be evaluated. (Yeah, in this
case it's pretty obvious that it'll be executed once per conn20060803
row, but in join situations, or even just with additional outer WHERE
clauses, it's not nearly so obvious.)

regards, tom lane