A classificação no plano EXPLAIN do postgresql pode ser evitada?

Um índice acelera a pesquisa se as linhas retornadas não forem superiores a aproximadamente 10%, caso contrário, uma verificação completa da tabela é mais rápida.

O Postgresql escolhe uma varredura completa stage_fact_revenue_900_add_dimsporque precisa selecionar todas as linhas e precisa de colunas que não estão presentes no índice. Pela mesma razão, ele acha que é melhor digitalizar fact_revenueuma vez e classificar os resultados em vez de fazer 177 milhões de buscas de índice.

Algumas coisas que você pode tentar:

1. Adicione à sua consulta um explícito ORDER BY date, identifier, billed_relation_id, subject_client_id, revenue_typeque isso deve sugerir ao Postgres que usar o índice fact_revenuepode evitar a classificação.
2. Altere o primeiro índice para ter a mesma ordem de campos do segundo (ou adicione um novo índice nessa ordem). No momento, eles têm os mesmos campos, mas em ordens diferentes, portanto, apenas um deles pode ser usado para evitar a classificação.
3. Tente reescrever a consulta usando NOT EXISTS em vez de LEFT JOIN:

Assim:

select
    stage_fact_revenue_900_add_dims.date as date,
    stage_fact_revenue_900_add_dims.dim_date_key as dim_date_key,
    stage_fact_revenue_900_add_dims.identifier as identifier,
    stage_fact_revenue_900_add_dims.revenue_type as revenue_type,
    stage_fact_revenue_900_add_dims.product_id as product_id,
    stage_fact_revenue_900_add_dims.dim_product_key as dim_product_key,
    stage_fact_revenue_900_add_dims.billing_partner_id as billing_partner_id,
    stage_fact_revenue_900_add_dims.dim_billing_partner_key as dim_billing_partner_key,
    stage_fact_revenue_900_add_dims.billed_relation_id as billed_relation_id,
    stage_fact_revenue_900_add_dims.subject_client_id as subject_client_id,
    stage_fact_revenue_900_add_dims.dim_subject_client_key as dim_subject_client_key,
    stage_fact_revenue_900_add_dims.client_id as client_id,
    stage_fact_revenue_900_add_dims.dim_billed_client_key as dim_billed_client_key,
    stage_fact_revenue_900_add_dims.partner_id as partner_id,
    stage_fact_revenue_900_add_dims.dim_billed_partner_key as dim_billed_partner_key,
    stage_fact_revenue_900_add_dims.price as price,
    stage_fact_revenue_900_add_dims.duration as duration,
    stage_fact_revenue_900_add_dims.amount as amount,
    stage_fact_revenue_900_add_dims.leaf as leaf,
    stage_fact_revenue_900_add_dims.dss_record_source as dss_record_source,
    current_timestamp as dss_create_time,
    current_timestamp as dss_update_time
from
    stage.stage_fact_revenue_900_add_dims stage_fact_revenue_900_add_dims
    
where
    NOT EXISTS (SELECT 1 FROM star.fact_revenue fact_revenue WHERE stage_fact_revenue_900_add_dims.date = fact_revenue.date
    and stage_fact_revenue_900_add_dims.identifier = fact_revenue.identifier
    and stage_fact_revenue_900_add_dims.revenue_type = fact_revenue.revenue_type
    and stage_fact_revenue_900_add_dims.billed_relation_id = fact_revenue.billed_relation_id
    and stage_fact_revenue_900_add_dims.subject_client_id = fact_revenue.subject_client_id)
ORDER BY date, identifier, billed_relation_id, subject_client_id, revenue_type;