Обсуждение: Regarding performance regression on specific query

Hi,

On 2018/11/20 2:49, Jung, Jinho wrote:
> Execution time
> =============
> 1.sql
> 10.6  : 469 ms
> 9.4.20: 10 ms
> 
> 4.sql
> 10.6  : 34019 ms
> 9.4.20: 0.4 ms

I noticed that these two are fixed by running ANALYZE in the database in
which these queries are run.

> 20.sql
> 10.6  : 2791 ms
> 9.4.20: 61 ms

This one may be suffering from a more serious planning issue, as doing
ANALYZE didn't help for this one.  Will have to look closely at how the
plan is changing for worse.

Thanks,
Amit

On Tue, Nov 20, 2018 at 10:08 PM Jung, Jinho <jinho.jung@gatech.edu> wrote:
> We are wondering how ANALYZE mitigated regression from query "1.sql" and "4.sql".
>
> We followed this procedure but still observe performance regression:
> 1) run ANALYZE on used table_name
>     analyze pg_catalog.pg_ts_parser;
>     analyze information_schema.column_options;
>     analyze pg_catalog.pg_aggregate;
>     analyze pg_catalog.pg_inherits;
>     analyze pg_catalog.pg_aggregate;
>     analyze pg_catalog.pg_rewrite;
>     analyze pg_catalog.pg_stat_user_indexes;
>     analyze pg_catalog.pg_stat_user_tables;
>     analyze pg_catalog.pg_attribute;
>     analyze information_schema.column_privileges;
>     analyze pg_catalog.pg_user_mapping;
>     analyze pg_catalog.pg_type;
>     analyze pg_catalog.pg_shseclabel;
>     analyze pg_catalog.pg_statio_sys_sequences;
>     analyze information_schema.role_routine_grants;
>     analyze pg_catalog.pg_type;
>     analyze information_schema.user_mapping_options;
>     analyze pg_catalog.pg_stat_xact_sys_tables;

You can run ANALYZE without explicitly specifying any table name, so
that you don't miss any.

> We have more cases. Do you think we should report them through the bug report website?
(https://www.postgresql.org/account/login/?next=/account/submitbug/)

You can send an email to pgsql-performance mailing list.

https://www.postgresql.org/list/pgsql-performance/

Thanks,
Amit

Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> writes:
> On 2018/11/20 2:49, Jung, Jinho wrote:
>>  [ assorted queries ]

> I noticed that these two are fixed by running ANALYZE in the database in
> which these queries are run.

That didn't help much for me.  What did help was increasing
join_collapse_limit and from_collapse_limit to not limit the
join search space --- on queries with as many input relations
as these, you're really at the mercy of whether the given query
structure represents a good join order if you don't.

In general I can't get even a little bit excited about the quality of the
plans selected for these examples, as they all involve made-up restriction
and join clauses that the planner isn't going to have the slightest clue
about.  The observations boil down to "9.4 made one set of arbitrary plan
choices, while v10 made a different set of arbitrary plan choices, and on
these particular examples 9.4 got lucky and 10 didn't".

Possibly also worth noting is that running these in an empty database
is in itself kind of a worst case, because many of the tables are empty
to start with (or the restriction/join clauses pass no rows), and so
the fastest runtime tends to go to plans of the form "nestloop with
empty relation on the outside and all the expensive stuff on the
inside".  (Observe all the "(never executed)" notations in the EXPLAIN
output.)  This kind of plan wins only when the outer rel is actually
empty, otherwise it can easily lose big, and therefore PG's planner is
intentionally designed to discount the case entirely.  We never believe
that a relation is empty, unless we can mathematically prove that, and
our cost estimates are never made with an eye to exploiting such cases.
This contributes a lot to the random-chance nature of which plan is
actually fastest; the planner isn't expecting "(never executed)" to
happen and doesn't prefer plans that will win if it does happen.

            regards, tom lane