Re: Regarding performance regression on specific query

Thanks for the test.

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;

2) execute the same query

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/)

Jinho Jung

________________________________
From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
Sent: Tuesday, November 20, 2018 2:47:54 AM
To: Jung, Jinho; pgsql-hackers(at)postgresql(dot)org
Subject: Re: 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