正在优化postgresql查询

Question

我有一个查询，它对我来说相当慢：

select * from "cams" where 
"bust" is not null and 
"figure" is not null and 
"age" is not null and 
"hair" is not null and 
"ethnicity" is not null  
and "status" = 'online' and  
"cams"."deleted_at" is null 
order by ethnicity = 'white'  DESC, 
age = 22 DESC, 
(age >= 18 AND age <= 35) DESC, 
bust = 'medium' DESC, 
figure = 'petite' DESC, 
hair = 'blonde' DESC 
limit 10

解释分析输出

Limit  (cost=10045.82..10045.84 rows=10 width=318) (actual time=754.187..754.190 rows=10 loops=1)
->  Sort  (cost=10045.82..10047.15 rows=532 width=318) (actual time=754.182..754.183 rows=10 loops=1)
Sort Key: ((ethnicity = 'white'::ethnicity)) DESC, ((age = 22)) DESC, (((age >= 18) AND (age <= 35))) DESC, ((bust = 'medium'::bust)) DESC, ((figure = 'petite'::figure)) DESC, ((hair = 'blonde'::hair_color)) DESC
Sort Method: top-N heapsort  Memory: 33kB
->  Bitmap Heap Scan on cams  (cost=1328.15..10034.32 rows=532 width=318) (actual time=580.008..745.590 rows=5092 loops=1)
Recheck Cond: ((hair IS NOT NULL) AND (age IS NOT NULL) AND (status = 'online'::cam_status))
Filter: ((bust IS NOT NULL) AND (figure IS NOT NULL) AND (ethnicity IS NOT NULL) AND (deleted_at IS NULL))
Rows Removed by Filter: 2414
Heap Blocks: exact=49643
->  Bitmap Index Scan on cams_online_rank_age  (cost=0.00..1328.02 rows=2406 width=0) (actual time=567.587..567.587 rows=4715231 loops=1)
Index Cond: ((hair IS NOT NULL) AND (age IS NOT NULL))
Planning Time: 1.526 ms
Execution Time: 754.464 ms

有没有一个索引可以用来加快速度(请记住，订单的值将是动态的(？我在想一个部分指数，说明胸围、身材、年龄、头发种族在哪里不是空的，状态="在线"，但不确定该在哪个栏上排名，因为顺序是动态的(我正在努力寻找与某个项目相似的帖子(。

Answer 1

您唯一的希望是(status, deleted_at)上的索引。这至少符合where子句中的相等条件。如果您知道另一列具有较高比例的null值，则可以将其作为第三个键。

这可能会限制对表格的扫描。然而，性能可能更多地基于order by的排序，而不是扫描表。