Re: Different execution plan between PostgreSQL 8.4 and 12.11

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

Well, as someone who has worked on more than one database, I can tell you that new version always means new plans. Most of the time, the new plans are better but sometimes they're not. Your problem is probably caused by one or two SQL statements that have changed plans. I would advise installing pg_hint_plan extension and fixing those few SQL queries manually. PostgreSQL would probably benefit from something like Oracle baselines, which are a good mechanism for carrying plans over to the new version.

If you don't want to install the new extension, you can try by running vacuum analyze on the database. Also, upgrading to PgSQL 12 doesn't make much sense given the fact that PgSQL 15 will be released in a few weeks. Can you upgrade to PgSQL 14?

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com