sql - 如何使用左连接优化查询

Question

请帮助优化我的查询:

select id, paid_till, rating, paid 
from lots
left join (select 1 paid) paid on current_timestamp <= lots.paid_till 
order by paid asc, rating desc, updated_at desc;

和查询计划:

Sort  (cost=1948.17..1948.18 rows=4 width=28) (actual time=0.703..0.704 rows=4 loops=1)
  Sort Key: (1), lots.rating DESC, lots.updated_at DESC
  Sort Method: quicksort  Memory: 25kB
  ->  Nested Loop Left Join  (cost=0.00..1948.13 rows=4 width=28) (actual time=0.014..0.682 rows=4 loops=1)
     Join Filter: (now() <= lots.paid_till)
     Rows Removed by Join Filter: 2
     ->  Seq Scan on lots  (cost=0.00..1948.04 rows=4 width=24) (actual time=0.008..0.675 rows=4 loops=1)
     ->  Materialize  (cost=0.00..0.03 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=4)
           ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)

Planning time: 0.210 ms
Execution time: 0.724 ms

我应该添加哪些索引？如何修复“嵌套循环左连接”？

附言我无法在选择中使用虚拟列进行排序，导致 Rails 问题。

Answer 1

这是一种获取 paid 值的奇怪方式。请改用 CASE 表达式:

select 
  id, 
  paid_till, 
  rating, 
  case when current_timestamp <= paid_till then 1 else 0 end as paid 
from lots
order by paid asc, rating desc, updated_at desc;

不过，这可能不会加快查询速度。没有 WHERE 子句，因此必须读取整个表。我认为加快速度的唯一方法是覆盖索引:

create index idx_quick on lots
(
  case when current_timestamp <= paid_till then 1 else 0 end,
  rating,
  updated_at desc,
  paid_till,
  id)
;

但是为这个查询量身定制索引似乎有点过分了。