PostgreSQL递归优化Left Join行数？分页查询性能优化求助

问题背景 #

添加channel.code = 'channel_code'条件后，查询执行时间超过两分钟。核心矛盾在于：仅需获取10条订单，但当前逻辑先过滤order表（匹配数据达500万条），再与多个百万级表关联，导致关联计算量过大。所有必要索引已配置齐全，需验证以下方案是否可行：通过PostgreSQL的循环/递归机制，先取出100条预过滤的订单，再与其余表关联并验证条件，符合要求的订单加入最终结果，直到凑够10条。

原始查询 #

SELECT ord.billing_number,
       ord.amount_total_sum,
       ord.fare_sum,
       ord.service_fee_sum,
       ord.service_time_limit,
       ord.tax_sum,
       ord.currency,
       ord.pos,
       ord.updated,
       ord.created,
       ord.user_id,
       ord.hidden,
       ord.id,
       ord.marker,
       status.code                                                 as status,
       status.title                                                as status_title,
       partner.code                                                as code,
       partner.fee                                                 as fee,
       partner.fee_refund                                          as fee_refund,
       partner.config                                              as config,
       partner.project,
       COALESCE(contract_parent_partner.code, parent_partner.code) as parent_code,
       u.email                                                     as user_email
FROM (SELECT * FROM tol.order WHERE billing_number::char <> '4') ord
 LEFT JOIN tol.service_cart service_cart ON ord.id = service_cart.order_id
 INNER JOIN tol.ticket_avia_v2 ticket
            ON service_cart.ticket_uid = ticket.id AND service_cart.service_table_name = 'vw_ticket_avia'
 LEFT JOIN tol.user u ON u.id = ord.user_id
 LEFT JOIN tol.partner partner ON ord.partner_id = partner.id
 LEFT JOIN tol.status status ON ord.status_id = status.id
 LEFT JOIN tol.order_channel order_channel ON ord.id = order_channel.order_id
 LEFT JOIN tol.partner_contract partner_contract ON ord.partner_contract_id = partner_contract.id
 LEFT JOIN tol.partner parent_partner ON parent_partner.id = partner.parent_id
 LEFT JOIN tol.channel channel ON order_channel.channel_id = channel.id
 LEFT JOIN tol.partner contract_parent_partner
           ON contract_parent_partner.id = partner_contract.parent_partner_id
WHERE (EXISTS (SELECT 1
               FROM tol.flight
               WHERE tol.flight.ticket_uid = ticket.id
                 AND tol.flight.carrier_code = 'carrier_code'
               ORDER BY tol.flight.id))
  AND (channel.code = 'channel_code')
ORDER BY ord.id DESC
LIMIT 10;

已尝试方案 #

曾尝试在JOIN中使用子查询，但因LEFT JOIN的特性，得到的结果与原查询不一致，无法采用。

执行计划（EXPLAIN ANALYZE 翻译版） #

Limit  (成本=3226993.73..3226993.74 行数=1 宽度=515) (实际时间=1051.938..1055.143 行数=0 循环次数=1)
  缓冲区: 共享命中=65 读取=2
  ->  Sort  (成本=3226993.73..3226993.74 行数=1 宽度=515) (实际时间=32.786..35.991 行数=0 循环次数=1)
        排序键: "order".id DESC
        排序方法: quicksort  内存: 25kB
        缓冲区: 共享命中=65 读取=2
        ->  Nested Loop Left Join  (成本=2038882.89..3226993.72 行数=1 宽度=515) (实际时间=32.724..35.926 行数=0 循环次数=1)
              连接过滤条件: (contract_parent_partner.id = partner_contract.parent_partner_id)
              缓冲区: 共享命中=62 读取=2
              ->  Nested Loop Left Join  (成本=2038882.89..3225588.32 行数=1 宽度=492) (实际时间=32.723..35.924 行数=0 循环次数=1)
                    连接过滤条件: (parent_partner.id = partner.parent_id)
                    缓冲区: 共享命中=62 读取=2
                    ->  Gather  (成本=2038882.89..3224182.92 行数=1 宽度=491) (实际时间=32.722..35.922 行数=0 循环次数=1)
                          计划的工作进程数: 2
                          启动的工作进程数: 2
                          缓冲区: 共享命中=62 读取=2
                          ->  Nested Loop Semi Join  (成本=2037882.89..3223182.82 行数=1 宽度=491) (实际时间=0.723..0.728 行数=0 循环次数=3)
                                缓冲区: 共享命中=62 读取=2
                                ->  Hash Join  (成本=2037812.20..3215337.24 行数=105 宽度=523) (实际时间=0.723..0.726 行数=0 循环次数=3)
                                      Hash连接条件: (order_channel.channel_id = channel.id)
                                      缓冲区: 共享命中=62 读取=2
                                      ->  Hash Left Join  (成本=2037803.88..3206741.25 行数=3271051 宽度=527) (未执行)
                                            Hash连接条件: "order".partner_contract_id = partner_contract.id
                                            ->  Hash Left Join  (成本=2037462.25..3197812.02 行数=3271051 宽度=527) (未执行)
                                                  Hash连接条件: "order".status_id = status.id
                                                  ->  Parallel Hash Left Join  (成本=2037460.83..3187308.80 行数=3271051 宽度=475) (未执行)
                                                        Hash连接条件: "order".partner_id = partner.id
                                                        ->  Parallel Hash Left Join  (成本=1941872.95..3083133.07 行数=3271051 宽度=166) (未执行)
                                                              Hash连接条件: "order".user_id = u.id
                                                              ->  Parallel Hash Join  (成本=1646731.87..2615342.48 行数=3271051 宽度=152) (未执行)
                                                                    Hash连接条件: service_cart.order_id = "order".id
                                                                    ->  Parallel Hash Join  (成本=582600.12..1337818.87 行数=5341282 宽度=44) (未执行)
                                                                          Hash连接条件: service_cart.order_id = order_channel.order_id
                                                                          ->  Parallel Hash Join  (成本=402211.21..1040928.08 行数=5341282 宽度=36) (未执行)
                                                                                Hash连接条件: ticket.id = service_cart.ticket_uid
                                                                                ->  Parallel Index Only Scan 使用索引ticket_avia_v2_id 扫描ticket_avia_v2 ticket  (成本=0.56..521205.61 行数=6446412 宽度=16) (未执行)
                                                                                      堆读取次数: 0
                                                                                ->  Parallel Hash  (成本=284286.30..284286.30 行数=6423068 宽度=20) (未执行)
                                                                                      ->  Parallel Seq Scan 扫描service_cart  (成本=0.00..284286.30 行数=6423068 宽度=20) (未执行)
                                                                                            过滤条件: (service_table_name = 'vw_ticket_avia'::text)
                                                                          ->  Parallel Hash  (成本=100494.85..100494.85 行数=4869685 宽度=8) (未执行)
                                                                                ->  Parallel Seq Scan 扫描order_channel  (成本=0.00..100494.85 行数=4869685 宽度=8) (未执行)
                                                                    ->  Parallel Hash  (成本=883642.55..883642.55 行数=6000656 宽度=116) (未执行)
                                                                          ->  Parallel Seq Scan 扫描"order"  (成本=0.00..883642.55 行数=6000656 宽度=116) (未执行)
                                                                                过滤条件: ((billing_number)::character(1) <> '4'::bpchar)
                                                              ->  Parallel Hash  (成本=221481.48..221481.48 行数=4012048 宽度=18) (未执行)
                                                                    ->  Parallel Seq Scan 扫描"user" u  (成本=0.00..221481.48 行数=4012048 宽度=18) (未执行)
                                                        ->  Parallel Hash  (成本=95389.05..95389.05 行数=15906 宽度=321) (未执行)
                                                              ->  Parallel Index Scan 使用索引partner_parent_index 扫描partner  (成本=0.29..95389.05 行数=15906 宽度=321) (未执行)
                                                  ->  Hash  (成本=1.19..1.19 行数=19 宽度=60) (未执行)
                                                        ->  Seq Scan 扫描status  (成本=0.00..1.19 行数=19 宽度=60) (未执行)
                                            ->  Hash  (成本=243.50..243.50 行数=7850 宽度=8) (未执行)
                                                  ->  Seq Scan 扫描partner_contract  (成本=0.00..243.50 行数=7850 宽度=8) (未执行)
                                      ->  Hash  (成本=8.30..8.30 行数=1 宽度=4) (实际时间=0.484..0.484 行数=0 循环次数=3)
                                            桶数: 1024  批次数: 1  内存使用: 8kB
                                            缓冲区: 共享命中=6 读取=2
                                            ->  Index Scan 使用索引uniq_6cae9c8d77153098 扫描channel  (成本=0.29..8.30 行数=1 宽度=4) (实际时间=0.483..0.483 行数=0 循环次数=3)
                                                  索引条件: (code = 'channel_code'::text)
                                                  缓冲区: 共享命中=6 读取=2
                                ->  Bitmap Heap Scan 扫描flight  (成本=70.69..74.71 行数=1 宽度=16) (未执行)
                                      重检查条件: ((ticket_uid = service_cart.ticket_uid) AND (carrier_code = 'carrier_code'::text))
                                      ->  BitmapAnd  (成本=70.69..70.69 行数=1 宽度=0) (未执行)
                                            ->  Bitmap Index Scan 使用索引flight_ticket_idx  (成本=0.00..4.35 行数=36 宽度=0) (未执行)
                                                  索引条件: (ticket_uid = service_cart.ticket_uid)
                                            ->  Bitmap Index Scan 使用索引idx_flight_carrier_code  (成本=0.00..65.28 行数=3295 宽度=0) (未执行)
                                                  索引条件: (carrier_code = 'carrier_code'::text)
                    ->  Seq Scan 扫描partner parent_partner  (成本=0.00..1067.40 行数=27040 宽度=9) (未执行)
              ->  Seq Scan 扫描partner contract_parent_partner  (成本=0.00..1067.40 行数=27040 宽度=9) (未执行)
规划时间: 48.168 ms
JIT:
  函数数: 228
  选项: 内联开启, 优化开启, 表达式开启, 变形开启
  时间: 生成23.199 ms, 内联51.477 ms, 优化580.442 ms, 生成代码386.265 ms, 总计1041.384 ms
执行时间: 1078.301 ms

方案可行性分析及优化建议 #

小批量预过滤方案可行性 #

该方案完全可行，核心思路是通过分批获取预过滤的order数据，减少单次关联的数据集大小，避免全量关联的巨大开销。具体实现可以通过递归CTE或者函数循环实现：

递归CTE实现示例

WITH RECURSIVE batch_query AS (
    -- 初始批次：取前100条符合预过滤条件的订单（按id倒序）
    SELECT ord.*, 1 AS batch_num
    FROM tol.order ord
    WHERE billing_number::char <> '4'
    ORDER BY ord.id DESC
    LIMIT 100
    UNION ALL
    -- 后续批次：基于上一批次的最小id，取下一批100条订单
    SELECT ord.*, b.batch_num + 1 AS batch_num
    FROM tol.order ord
    JOIN batch_query b ON ord.id < (SELECT MIN(id) FROM batch_query WHERE batch_num = b.batch_num)
    WHERE billing_number::char <> '4'
    ORDER BY ord.id DESC
    LIMIT 100
)
-- 对每批次订单执行关联和条件验证，直到收集到10条符合要求的结果
SELECT ord.billing_number,
       ord.amount_total_sum,
       ord.fare_sum,
       ord.service_fee_sum,
       ord.service_time_limit,
       ord.tax_sum,
       ord.currency,
       ord.pos,
       ord.updated,
       ord.created,
       ord.user_id,
       ord.hidden,
       ord.id,
       ord.marker,
       status.code                                                 as status,
       status.title                                                as status_title,
       partner.code                                                as code,
       partner.fee                                                 as fee,
       partner.fee_refund                                          as fee_refund,
       partner.config                                              as config,
       partner.project,
       COALESCE(contract_parent_partner.code, parent_partner.code) as parent_code,
       u.email                                                     as user_email
FROM batch_query ord
 LEFT JOIN tol.service_cart service_cart ON ord.id = service_cart.order_id
 INNER JOIN tol.ticket_avia_v2 ticket
            ON service_cart.ticket_uid = ticket.id AND service_cart.service_table_name = 'vw_ticket_avia'
 LEFT JOIN tol.user u ON u.id = ord.user_id
 LEFT JOIN tol.partner partner ON ord.partner_id = partner.id
 LEFT JOIN tol.status status ON ord.status_id = status.id
 LEFT JOIN tol.order_channel order_channel ON ord.id = order_channel.order_id
 LEFT JOIN tol.partner_contract partner_contract ON ord.partner_contract_id = partner_contract.id
 LEFT JOIN tol.partner parent_partner ON parent_partner.id = partner.parent_id
 LEFT JOIN tol.channel channel ON order_channel.channel_id = channel.id
 LEFT JOIN tol.partner contract_parent_partner
           ON contract_parent_partner.id = partner_contract.parent_partner_id
WHERE (EXISTS (SELECT 1
               FROM tol.flight
               WHERE tol.flight.ticket_uid = ticket.id
                 AND tol.flight.carrier_code = 'carrier_code'))
  AND (channel.code = 'channel_code')
ORDER BY ord.id DESC
LIMIT 10;

更直接的查询改写优化 #

从执行计划可以看出，当前查询的执行顺序是从大表order开始扫描，而过滤性极强的channel.code条件被放在了后续的Hash Join中。可以通过调整表关联顺序，从过滤性强的表入手，直接缩小数据集：

SELECT ord.billing_number,
       ord.amount_total_sum,
       ord.fare_sum,
       ord.service_fee_sum,
       ord.service_time_limit,
       ord.tax_sum,
       ord.currency,
       ord.pos