Dear David,
Thanks for your reply.
>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.
LIMIT node because I connect PostgreSQL with A5M2, the tool will add LIMIT when outputting the execution plan, please ignore it.
At 2022-08-18 11:38:58, "David Rowley" <dgrowleyml@xxxxxxxxx> wrote: >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
