planner chooses incremental but not the best one
От | Nicolas Lutic |
---|---|
Тема | planner chooses incremental but not the best one |
Дата | |
Msg-id | d2f06ddc-a8d4-48ea-893b-a95255c632b9@loxodata.com обсуждение исходный текст |
Ответы |
Re: planner chooses incremental but not the best one
(Richard Guo <guofenglinux@gmail.com>)
|
Список | pgsql-hackers |
Dear Hackers, I've come across a behaviour of the planner I can't explain. After a migration from 11 to 15 (on RDS) we noticed a degradation in response time on a query, it went from a few seconds to ten minutes. A vacuum(analyze) has been realized to be sure that all is clean. The 'explain analyze' shows us a change of plan. Postgresql 15 chooses `incremental sort` with an index corresponding to the ORDER BY clause (on the created_at column). The previous v11 plan used a more efficient index. By deactivating incremental sort, response times in v15 are equal to v11 one. Here is the query SELECT inputdocum0_.id AS col_0_0_ FROM document_management_services.input_document inputdocum0_ WHERE (inputdocum0_.indexation_domain_id in ('2d29daf6-e151-479a-a52a-78b08bb3009d')) AND (inputdocum0_.indexation_subsidiary_id in ('9f9df402-f70b-40d9-b283-a3c35232469a')) AND (inputdocum0_.locked_at IS NULL) AND (inputdocum0_.locked_by_app IS NULL) AND (inputdocum0_.locked_by_user IS NULL) AND (inputdocum0_.lock_time_out IS NULL) AND inputdocum0_.archiving_state<> 'DESTROYED' AND (inputdocum0_.creation_state in ('READY')) AND inputdocum0_.active_content=true AND (inputdocum0_.processing_state in ('PENDING_INDEXATION')) ORDER BY inputdocum0_.created_at ASC, inputdocum0_.reception_id ASC, inputdocum0_.reception_order ASC LIMIT 50 ; Here are some details, the table `input_document` is partionned by hash with 20 partitions with a lot of indexes Indexes: "input_document_pkey" PRIMARY KEY, btree (id) "input_document_api_version_idx" btree (api_version) INVALID "input_document_created_at_idx" btree (created_at) "input_document_created_by_user_profile_idx" btree (created_by_user_profile) "input_document_dashboard_idx" btree (processing_state, indexation_family_id, indexation_group_id, reception_id) INCLUDE (active_content, archiving_state, creation_state) WHERE active_content = true AND archiving_state <> 'DESTROYED'::text AND creation_state <> 'PENDING'::text "input_document_fts_description_idx" gin (to_tsvector('simple'::regconfig, description)) "input_document_fts_insured_firstname_idx" gin (to_tsvector('simple'::regconfig, indexation_insured_firstname)) "input_document_fts_insured_lastname_idx" gin (to_tsvector('simple'::regconfig, indexation_insured_lastname)) "input_document_indexation_activity_id_idx" btree (indexation_activity_id) "input_document_indexation_agency_id_idx" btree (indexation_agency_id) "input_document_indexation_distributor_id_idx" btree (indexation_distributor_id) "input_document_indexation_domain_id_idx" btree (indexation_domain_id) "input_document_indexation_family_id_idx" btree (indexation_family_id) "input_document_indexation_group_id_idx" btree (indexation_group_id) "input_document_indexation_insurer_id_idx" btree (indexation_insurer_id) "input_document_indexation_nature_id_idx" btree (indexation_nature_id) "input_document_indexation_reference_idx" btree (indexation_reference) "input_document_indexation_subsidiary_id_idx" btree (indexation_subsidiary_id) "input_document_indexation_warranty_id_idx" btree (indexation_warranty_id) "input_document_locked_by_user_idx" btree (locked_by_user) "input_document_modified_at_idx" btree (modified_at) "input_document_modified_by_user_profile_idx" btree (modified_by_user_profile) "input_document_processing_state_idx" btree (processing_state) "input_document_stock_idx" btree (active_content, archiving_state, creation_state, processing_state) WHERE active_content AND archiving_state <> 'DESTROYED'::text AND creation_state <> 'PENDING'::text AND (processing_state = ANY ('{PENDING_PROCESSING,PENDING_INDEXATION,READY}'::text[])) "input_dom_act_pi_idx" btree (indexation_activity_id, indexation_domain_id) WHERE processing_state = 'PENDING_INDEXATION'::text "input_dom_act_pp_idx" btree (indexation_activity_id, indexation_domain_id) WHERE processing_state = 'PENDING_PROCESSING'::text "input_dom_act_sub_idx" btree (indexation_activity_id, indexation_domain_id, indexation_subsidiary_id) "input_reception_id_created_at_idx" btree (reception_id, created_at) "input_reception_id_reception_order_idx" btree (reception_id, reception_order) "operational_perimeter_view_idx" btree (processing_state, indexation_distributor_id) WHERE processing_state = 'PENDING_PROCESSING'::text Please find attached the 3 plans explain_analyse_incremental_off.txt with enable_incremental_sort to off explain_analyse_incremental_on.txt with enable_incremental_sort to on explain_analyse_incremental_on_limit5000 with enable_incremental_sort to on but with increase the limit to 5000, in this case plan choose don't use `Incremental Sort` The point that I don't understand in the plan (incremental_sort to on) is the top level one, the limit cost doesn't seem right. Limit (cost=324.05..16073.82 rows=50 width=44) (actual time=1663688.290..1663696.151 rows=50 loops=1) Buffers: shared hit=114672881 read=5725197 dirtied=38564 written=24394 I/O Timings: shared/local read=1481378.069 write=313.574 -> Incremental Sort (cost=324.05..27838050.13 rows=88375 width=44) (actual time=1663688.289..1663696.144 rows=50 loops=1) Have you a explaination on the behaviour ? Best regards -- Nicolas Lutic
Вложения
В списке pgsql-hackers по дате отправления:
Следующее
От: shveta malikДата:
Сообщение: Re: How abnormal server shutdown could be detected by tests?