I looked into the performance complaint noted here: http://archives.postgresql.org/pgsql-performance/2011-03/msg00135.php in which 8.4 and up produce a worse plan than 8.3.
The reason for the change in behavior is that the newer releases transform the WHERE EXISTS construct to a semi-join. The best-case scenario for the old implementation is that the sub-select can be implemented like a parameterized inner indexscan. If that happens to be actually the best plan, you'll only get it out of 8.4+ if the pulled-up EXISTS can be implemented as an inner indexscan, and there are restrictions on how complicated a relation we can handle that way. (I've been hoping for more than a year now to fix that, but the project is stalled on how to do costing without slowing the planner down a lot.) So there are variants of this problem where 8.4+ really is stupider than previous releases, and it's not easy to fix. But this isn't one of them. The reason that the particular case here fails to get optimized is that flatten_simple_union_all() refuses to flatten the UNION ALL because there are unknown-literal constants in it, and those don't match the resolved output type of the UNION, which was kluged to be TEXT way back in the parser. The parser however does not try to patch up the unknown literals themselves to match its decision about the output type. So the planner punts and doesn't get to the query form that it could optimize into a plan similar to 8.3's. So we could fix this either by changing the parser to make the types match, or by kluging flatten_simple_union_all (and in particular tlist_same_datatypes) to treat an UNKNOWN-type Const as a match. The latter is a lot uglier-looking but seems a bit less likely to break anything: fixing the constants at parse time would require letting transformSetOperationTree scribble on the leaf SELECTs. The reason we have not done that in the past is fear of changing the semantics of the leaf SELECTs --- for example, if the leaf SELECT includes DISTINCT or GROUP BY on that output value, changing its type would be bad news. But reflecting on that now, it seems like it'd be perfectly safe to coerce UNKNOWN Consts to the resolved type, because if the leaf SELECT depended in any way at all on the type of that targetlist entry, it'd have resolved the UNKNOWN to something else (probably TEXT) already. So I'm thinking it'd be OK to fix this in the parser, but wanted to see if anyone felt differently. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers