On Thu, 18 Aug 2022 at 15:32, Tom Lane <tgl@xxxxxxxxxxxxx> 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
