Re:Re: Different execution plan between PostgreSQL 8.2 and 12.5

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(at)gmail(dot)com> wrote:
>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