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