Re: Different execution plan between PostgreSQL 8.4 and 12.11

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.