Re: Re: Different execution plan between PostgreSQL 8.4 and 12.11

út 11. 10. 2022 v 5:13 odesílatel gzh <gzhcoder(at)126(dot)com> napsal:

> Hi, Tom
> Thank you for your reply.
>
> > When you're asking for help, please don't give us vague statements
>
> > like "doesn't seem to work".
>
> I understand.
>
>
> > Did the plan (including rowcount
>
> > estimates) change at all? To what? How far off is that rowcount
>
> > estimate, anyway --- that is, how many rows actually have cseid = 94?
>
> Please refer to the new execution plan (PostgreSQL 12.11) below.
>
>
> new=# show enable_seqscan;
>
> enable_seqscan
>
> ----------------
>
> on
>
> (1 行)
>
>
> new=# select count(*) from analyze_word_reports;
>
> count
>
> ----------
>
> 21331980
>
> (1 行)
>
>
> new=# select count(*) from analyze_word_reports where (cseid = 94);
>
> count
>
> ---------
>
> 1287156
>
> (1 行)
>
>
> new=# explain analyze select count(2) from analyze_word_reports where
> (cseid = 94) limit 1;
>
>
> QUERY PLAN
>
>
>

the LIMIT clause is in this case totally useless and messy, and maybe can
negative impacts optimizer

Regards

Pavel

>
> ----------------------------------------------------------------------------------------------------------------------------------
>
> --------------------------------------------------------------------------
>
> Limit (cost=65184.06..65184.07 rows=1 width=8) (actual
> time=123.713..133.035 rows=1 loops=1)
>
> -> Finalize Aggregate (cost=65184.06..65184.07 rows=1 width=8)
> (actual time=123.712..133.033 rows=1 loops=1)
>
> -> Gather (cost=65183.85..65184.06 rows=2 width=8) (actual
> time=123.548..133.024 rows=3 loops=1)
>
> Workers Planned: 2
>
> Workers Launched: 2
>
> -> Partial Aggregate (cost=64183.85..64183.86 rows=1
> width=8) (actual time=119.495..119.496 rows=1 loops=3)
>
> -> Parallel Index Only Scan using
> analyze_word_reports_index_cseid on analyze_word_reports (cost=0.56..6290
>
> 9.34 rows=509805 width=0) (actual time=0.031..98.706 rows=429052 loops=3)
>
> Index Cond: (cseid = 94)
>
> Heap Fetches: 1287156 Planning Time: 0.122 ms
> Execution Time: 133.069 ms
>
> (11 行)
>
>
> new=# explain analyze select 2 from analyze_word_reports where (cseid =
> 94) limit 1;
>
> QUERY PLAN
>
>
>
>
>
> ----------------------------------------------------------------------------------------------------------------------------------
>
> ---
>
> Limit (cost=0.00..0.43 rows=1 width=4) (actual time=2156.964..2156.966
> rows=1 loops=1)
>
> -> Seq Scan on analyze_word_reports (cost=0.00..528550.75
> rows=1223533 width=4) (actual time=2156.962..2156.964 rows=1 loops=
>
> 1)
>
> Filter: (cseid = 94)
>
> Rows Removed by Filter: 18320180 Planning Time: 0.086 ms
> Execution Time: 2156.985 ms
>
> (6 行)
>
>
>
> > If the estimate is far off, then increasing the table's statistics
>
> > target might help.
>
> Thank you for your advice.
>
> Please tell me how to set the table's statistics up to improve performance.
>
>
> new=# select oid from pg_class where relname = 'analyze_word_reports';
>
> oid
>
> -------
>
> 16429
>
> (1 行)
>
>
> new=# select attrelid,attname,attstattarget from pg_attribute where
> attrelid=16429 and attname='cseid';
>
> attrelid | attname | attstattarget
>
> ----------+---------+---------------
>
> 16429 | cseid | -1
>
> (1 行)
>
>
> > Another thing that would be worth checking is whether
>
> > "set enable_seqscan = off" prods it to choose the plan you want.
>
> > If not, then there's something else going on besides poor estimates.
>
> "set enable_seqscan = off" works, and the performance is greatly improved,
> which is almost the same as PostgreSQL 8.4.
>
> The enable_seqscan(PostgreSQL 8.4) is on, will this change have an unknown
> effect on other queries?
>
>
>
>
>
> At 2022-10-10 10:45:54, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >gzh <gzhcoder(at)126(dot)com> writes:
> >> I've run analyze(not vacuum analyze), but it doesn't seem to work.
> >
> >When you're asking for help, please don't give us vague statements
> >like "doesn't seem to work". Did the plan (including rowcount
> >estimates) change at all? To what? How far off is that rowcount
> >estimate, anyway --- that is, how many rows actually have cseid = 94?
> >
> >If the estimate is far off, then increasing the table's statistics
> >target might help.
> >
> >Another thing that would be worth checking is whether
> >"set enable_seqscan = off" prods it to choose the plan you want.
> >If not, then there's something else going on besides poor estimates.
> >
> > regards, tom lane
>
>