I don't have a full test case yet, but I did finally manage to get an
explain analyze to finish in a sane amount of time on 8.2.5. Attached
are two cleaned up explain analyze results, using the exact same data
directory but different executables: one is 8.2.3 and returns as
expected, the other is 8.2.5, which generates a slow plan despite any
fiddling with geqo/join_collapse_limit, etc. The cost is the same, but
it makes a wrong turn partway through the plan. This 8.2.5 has the
earlier patches from Tom already posted applied to it (also tested on
8.2.4 and 8.2.5 with the same result). An earlier version of the query
with tables truncated to 100000 rows ran in 70 seconds on 8.2.3, and did
not finish after an hour on 8.2.5. This version has the tables truncated
to a mere 10000 rows each. The query itself is a view calling some large
views, which call other views and functions, etc. I can post a version
of it if needed, but my energy is mostly focused now on making a
reproducible test case. Now that this is locally reproducible in a
finite amount of time, patches and tweaking suggestions are welcome.
(Postgres built with no special flags from source, all tables have been
analyzed, '***' in the explain analyze plans indicates places manually
made things more readable).
