planner chooses incremental but not the best one

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