"Say42" <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org