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