Different execution plan between PostgreSQL 8.2 and 12.5

Hi,

I have had a Perl Website working for 7 years and have had no problems

until a few weeks ago I replaced my database server with a newer one.

Database server (old): PostgreSQL 8.2 32bit

Database server (new): PostgreSQL 12.5 64bit

I run following sql in PostgreSQL 8.2 and PostgreSQL 12.5, it returns different execution plan.

--SQL

explain select

crew_base.crewid

from crew_base

left join crew_base as crew_base_introduced on crew_base.introduced_by=crew_base_introduced.crewid

where crew_base.status = '1';

--PostgreSQL 8.2

---------------

QUERY PLAN

Limit (cost=188628.24..189521.23 rows=10000 width=10)

-> Hash Left Join (cost=188628.24..3800200.71 rows=40443494 width=10)

Hash Cond: (lower(crew_base.introduced_by) = lower(crew_base_introduced.crewid))

-> Seq Scan on crew_base (cost=0.00..165072.69 rows=5446 width=20)

Filter: (status = 1)

-> Hash (cost=161359.55..161359.55 rows=1485255 width=10)

-> Seq Scan on crew_base crew_base_introduced (cost=0.00..161359.55 rows=1485255 width=10)

--PostgreSQL 12.5

---------------

QUERY PLAN

Limit (cost=0.43..47861.44 rows=10000 width=7)

-> Nested Loop Left Join (cost=0.43..169386135.30 rows=35391255 width=7)

Join Filter: (lower(crew_base.introduced_by) = lower(crew_base_introduced.crewid))

-> Seq Scan on crew_base (cost=0.00..128942.75 rows=4759 width=14)

Filter: (status = 1)

-> Materialize (cost=0.43..51909.70 rows=1487340 width=7)

-> Index Only Scan using crew_base_crewid_index on crew_base crew_base_introduced (cost=0.43..38663.00 rows=1487340 width=7)

PostgreSQL 8.2 quickly queried the data, but PostgreSQL 12.5 has not responded.

I'm guessing that the lower() function of PostgreSQL 12.5 invalidates the index.

But I don't understand why PostgreSQL 8.2 is normal.

What is the reason for this and is there any easy way to maintain compatibility?

Regards,

--

gzh