Did you analyze and vacuum all of the tables in the new database?
On 10/9/22 04:11, gzh wrote:
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
--
Angular momentum makes the world go 'round.
