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