Re: Regarding performance regression on specific query
| От | Jung, Jinho |
|---|---|
| Тема | Re: Regarding performance regression on specific query |
| Дата | |
| Msg-id | DM5PR07MB3927B6FA7F1EF95B9F2A067AEED90@DM5PR07MB3927.namprd07.prod.outlook.com обсуждение исходный текст |
| Ответ на | Re: Regarding performance regression on specific query (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>) |
| Ответы |
Re: Regarding performance regression on specific query
|
| Список | pgsql-hackers |
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@lab.ntt.co.jp>
Sent: Tuesday, November 20, 2018 2:47:54 AM
To: Jung, Jinho; pgsql-hackers@postgresql.org
Subject: Re: Regarding performance regression on specific query
Sent: Tuesday, November 20, 2018 2:47:54 AM
To: Jung, Jinho; pgsql-hackers@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
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
В списке pgsql-hackers по дате отправления: