Greg Sabino Mullane <greg@turnstep.com> writes:
> 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.
Is there a reason you rounded off most of the costs? It looks like the
estimated costs of the two join types are nearly equal, and so it's pure
chance which one gets chosen. The real problem seems to be the
misestimation here:
> ->Seq Scan on orders_smaller m (C=0..742 R=1) (AT=0..11 R=9247 L=1)
> Filter: ((order_number)::text !~~ '%.%'::text)
With a base scan estimate that's off by four orders of magnitude,
there's no reason at all to expect that the join plan above it will
be very suitable :-(
This might be a bug in the LIKE estimator (if so, it's in 8.2.3 as
well). I don't have time to look closer right now, but can you show us
the pg_stats row for orders_smaller.order_number?
regards, tom lane