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@gmail.com> wrote:
>On Thu, 18 Aug 2022 at 15:32, Tom Lane <tgl@sss.pgh.pa.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