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...@lab.ntt.co.jp>
Sent: Tuesday, November 20, 2018 2:47:54 AM
To: Jung, Jinho; pgsql-hack...@postgresql.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
