可以防止 postgresql EXPLAIN 计划中的排序吗？

如果返回的行不超过大约 10%，则索引会加快搜索速度，否则全表扫描会更快。

Postgresql 选择全扫描是stage_fact_revenue_900_add_dims因为它需要选择所有行并且需要索引中不存在的列。出于同样的原因，它认为最好扫描fact_revenue一次并对结果进行排序，而不是进行 1.77 亿次索引查找。

您可以尝试的一些事情：

1. 在您的查询中添加一个明确的ORDER BY date, identifier, billed_relation_id, subject_client_id, revenue_typethis 应该向 Postgres 建议使用索引fact_revenue可以避免排序。
2. 将第一个索引更改为与第二个索引具有相同的字段顺序（或按该顺序添加新索引）。目前他们有相同的字段但顺序不同，因此实际上只能使用其中一个来避免排序。
3. 尝试使用 NOT EXISTS 而不是 LEFT JOIN 重写查询：

像这样：

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;