Re: Different execution plan between PostgreSQL 8.4 and 12.11

On 10/9/22 05: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
>
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