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


Reply via email to