Re: Different execution plan between PostgreSQL 8.2 and 12.5

On Thu, 18 Aug 2022 at 15:32, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> The 12.5 plan looks like it thinks that the join condition is not
> hashable --- and probably not mergeable as well, else it would have
> done a mergejoin. This is odd if we assume that the lower()
> outputs are just text. But you haven't said anything about the
> data types involved, nor what locale setting you're using, nor
> what nondefault settings or extensions you might be using, so
> speculation about the cause would just be speculation.

In addition to that, I couldn't help notice that the quoted SQL does
not seem to belong to the explain. The EXPLAIN has a Limit node, but
the query does not. I'm assuming this isn't due to the relations being
views since we don't pull up subqueries with a LIMIT.

The costs for the 12.5 are cheaper than 8.4's, so I imagine the more
likely cause is the planner favouring an early startup plan.

It's probably more likely that lower() is providing the planner with
bad estimates and there's likely far less than the expected rows,
resulting in the LIMIT 10000 being a much larger proportion of the
total rows than the planner expects.

David