JOIN com uma tabela pequena faz com que o plano de execução mude e a consulta fique drasticamente lenta

Eu tenho as seguintes tabelas:

CREATE TABLE acl_sid (
 id bigserial not null primary key,
 principal boolean not null,
 sid varchar(100) not null,
 constraint unique_uk_1 unique(sid,principal) );

CREATE TABLE users(
 id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('users_sequence') PRIMARY KEY,
 email VARCHAR(255) NOT NULL,
    -- some ~10 columns omitted
 );

CREATE TABLE authorities (
 user_id NUMERIC(20, 0) NOT NULL,
 sid_id  BIGINT         NOT NULL,
 CONSTRAINT fk_authorities_users FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE CASCADE ON DELETE CASCADE,
 CONSTRAINT fk_authorities_sids FOREIGN KEY (sid_id) REFERENCES acl_sid (id) ON UPDATE CASCADE ON DELETE CASCADE );


CREATE SEQUENCE decidables_sequence START WITH 1 NO MAXVALUE;


CREATE TABLE decidables (
 id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('decidables_sequence') PRIMARY KEY,
 assigned_to NUMERIC(20, 0) DEFAULT NULL NULL,
 CONSTRAINT fk_decidables_assigned_to FOREIGN KEY (assigned_to) REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL );

CREATE TABLE decidables_transactions (
 decidable_id NUMERIC(20, 0) NOT NULL PRIMARY KEY,
 transaction_date TIMESTAMP DEFAULT NULL NULL,
 transaction_id VARCHAR(255) DEFAULT NULL,
 -- some 100 columns here
 CONSTRAINT uq_decidables_transactions_transaction_id UNIQUE (transaction_id),
 CONSTRAINT fk_decidables_transactions_decidable_id FOREIGN KEY (decidable_id) REFERENCES decidables (id) ON UPDATE CASCADE ON DELETE CASCADE );

CREATE TABLE decidables_transactions_mv (
 decidable_id NUMERIC(20, 0) NOT NULL,
 transaction_date TIMESTAMP DEFAULT NULL NULL,
 -- some 10 columns here, excerpt from decidables_transactions
 assigned_to NUMERIC(20, 0) DEFAULT NULL NULL,
 CONSTRAINT fk_decidables_transactions_mv_decidable_id_idx FOREIGN KEY (decidable_id) REFERENCES decidables (id) ON UPDATE CASCADE ON DELETE CASCADE,
 CONSTRAINT fk_decidables_transactions_mv_assigned_to_idx FOREIGN KEY (assigned_to) REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL );

CREATE INDEX decidable_id_mv_idx ON decidables_transactions_mv (decidable_id);
-- few more indexes on omitted columns, that are not being used in the following queries
CREATE INDEX transaction_date_mv_idx ON decidables_transactions_mv (transaction_date, decidable_id);

Eu tenho esta consulta de paginação de conjunto de chaves que funciona muito bem:

SELECT d.*,
       t.*,
       u.id    AS ass_to_id,
       u.email AS ass_to_email,
       NULL    AS ass_to_authority
FROM decidables d
         JOIN decidables_transactions t ON d.id = t.decidable_id
         LEFT JOIN users u ON d.assigned_to = u.id AND u.deleted_at IS NULL
         -- some more joins omitted for simplicity
         JOIN (SELECT t.decidable_id, t.transaction_date
               FROM decidables_transactions_mv t
               ORDER BY t.transaction_date DESC, t.decidable_id DESC
               LIMIT 10) AS decidables_table ON d.id = decidables_table.decidable_id
ORDER BY t.transaction_date DESC, t.decidable_id DESC;

Mas quando tento selecionar a autoridade do usuário:

SELECT d.*,
       t.*,
       u.id  AS ass_to_id,
       u.email AS ass_to_email,
       s.sid AS ass_to_authority
FROM decidables d
         JOIN decidables_transactions t ON d.id = t.decidable_id
         LEFT JOIN users u ON d.assigned_to = u.id AND u.deleted_at IS NULL
         LEFT JOIN authorities a ON a.user_id = u.id
         LEFT JOIN acl_sid s ON a.sid_id = s.id
         -- some more joins omitted for simplicity
         JOIN (SELECT t.decidable_id, t.transaction_date
               FROM decidables_transactions_mv t
               ORDER BY t.transaction_date DESC, t.decidable_id DESC
               LIMIT 10) AS decidables_table ON d.id = decidables_table.decidable_id
ORDER BY t.transaction_date DESC, t.decidable_id DESC;

Começa a demorar alguns minutos. Eu tenho cerca de 10 linhas em ~ 10 userslinhas acl_side ~ 2 milhões em e decidables.decidables_transactionsdecidables_transactions_mv

Os planos de execução são os seguintes:

• Consulta rápida
• Consulta lenta

Por que a consulta tem um desempenho muito diferente e fica drasticamente lenta?