sql - 如何优化此选择

Question

SELECT *
FROM   trips,
       dates
WHERE  places_number_on_flag > 0
       AND places_number > 0
       AND ( places_number - ( (SELECT Count(id)
                                FROM   resrv_customers
                                WHERE  trip = trips.id
                                       AND ow > 0)
                               + (SELECT Count(id)
                                  FROM   resrv_customers
                                  WHERE  trip = trips.id
                                         AND extra_seat = 1
                                         AND ow > 0) ) < 20 )
       AND dates.id = trips.trip_date
       AND dates.from_date > 2458553;  

它在大约一分 50 秒后返回 5 行。

我希望它更快。

标签： sqlpostgresqloptimization

Answer 1

一种优化是只运行一次子查询并使用条件聚合来计算座位数：

SELECT *
FROM   trips
  JOIN dates on dates.id = trips.trip_date
WHERE  places_number_on_flag > 0
       AND places_number > 0
       AND ( places_number - ( (SELECT Count(id) + count(id) filter (where extra_seat = 1)
                                FROM   resrv_customers
                                WHERE  trip = trips.id
                                       AND ow > 0)) < 20 )
       AND dates.from_date > 2458553;  

请注意，我用显式JOIN运算符替换了 WHERE 子句中古老而脆弱的隐式连接。它对性能没有任何影响，它只是更好的编码风格。