gzh <gzhcoder@126.com> writes:
> I run following sql in PostgreSQL 8.2 and PostgreSQL 12.5, it returns different execution plan.
8.2 is ... well, not stone age maybe, but pretty durn ancient.
You really ought to update a bit more often than that. (And
maybe pay more attention to staying up to date with minor releases?
Whatever was your reasoning for choosing 12.5, when the latest 12.x
release is 12.12?)
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.
There is some advice here about how to ask this sort of
question in a way that would obtain useful answers:
https://wiki.postgresql.org/wiki/Slow_Query_Questions
regards, tom lane