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.4 32bit

Database server (new): PostgreSQL 12.11 64bit




I run following sql in PostgreSQL 8.4 and PostgreSQL 12.11, it returns 
different execution plan.




--PostgreSQL 8.4

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




old=# select count(1) from analyze_word_reports;

  count

---------

 9164136

(1 row)




old=# select indexdef from pg_indexes where tablename='analyze_word_reports';

                                         indexdef

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

 CREATE INDEX analyze_word_reports_index_cseid ON analyze_word_reports USING 
btree (cseid)

 CREATE UNIQUE INDEX analyze_word_reports_pkey ON analyze_word_reports USING 
btree (seq)

(2 rows)




old=# explain analyze select 2 from analyze_word_reports where (cseid = 94) 
limit 1;

                                                                             
QUERY PLAN                                      

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

 Limit  (cost=0.00..0.63 rows=1 width=0) (actual time=0.893..0.893 rows=0 
loops=1)

   ->  Index Scan using analyze_word_reports_index_cseid on 
analyze_word_reports  (cost=0.00..18621.98 rows=29707 width=0) (actual 
time=0.892..0.892 rows=0 loops=1)

         Index Cond: (cseid = 94)

 Total runtime: 0.941 ms

(4 rows)







--PostgreSQL 12.11

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




new=# select count(1) from analyze_word_reports;

  count

---------

 20131947

(1 row)




new=# select indexdef from pg_indexes where tablename='analyze_word_reports';

                                         indexdef

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

 CREATE INDEX analyze_word_reports_index_cseid ON analyze_word_reports USING 
btree (cseid)

 CREATE UNIQUE INDEX analyze_word_reports_pkey ON analyze_word_reports USING 
btree (seq)

(2 rows)




new=# explain analyze select 2 from analyze_word_reports where (cseid = 94) 
limit 1;

                                                             QUERY PLAN         
                                                     

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

Limit  (cost=0.00..0.41 rows=1 width=4) (actual time=4908.459..4908.462 rows=1 
loops=1)

   ->  Seq Scan on analyze_word_reports  (cost=0.00..528610.15 rows=1278561 
width=4) (actual time=4908.455..4908.456 rows=1 loops=1)

         Filter: (cseid = 94)

         Rows Removed by Filter: 15477750

Planning Time: 0.411 ms

Execution Time: 4908.498 ms

(6 行)







Although PostgreSQL 8.4 has more than 10 million rows of data less than 
PostgreSQL 12.11,

PostgreSQL 12.11 is too slow and analyze_word_reports_index_cseid does not seem 
to work.

I'm guessing that the limit keyword of PostgreSQL 12.11 causes the index not to 
work. 

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




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




Regards,




--




gzh

Reply via email to