为什么将排序的列添加到索引不会使其更有效？

debugcn 发表于 Dev

尼尔斯·克里斯蒂安

我有一个这样的查询：

SELECT  "cars"."id" FROM de."cars" 
WHERE "cars"."sales_state" = 'onsale'
  AND (cars.is_disabled IS NOT TRUE) 
  AND (cars.price >= 35920) 
  AND (cars.price <= 659880) 
  AND ("cars"."featuring_score" IS NOT NULL) 
ORDER BY
   CASE WHEN cars.featuring_score < 'C' THEN 1
        WHEN cars.featuring_score = 'C' THEN 2
        WHEN cars.featuring_score > 'C' THEN 3
        ELSE 4
   END, 
   CASE WHEN cars.au_rating >= 3 THEN 1
        WHEN cars.au_rating = 0  THEN 2
        WHEN cars.au_rating = 2  THEN 3
        WHEN cars.au_rating = 1  THEN 4
        ELSE 6
   END, 
   CASE cars.brand
        WHEN 'Audi' THEN 1
        WHEN 'Alpina' THEN 2
        WHEN 'Artega' THEN 3
        WHEN 'BMW' THEN 4
        WHEN 'Maybach' THEN 5
        ELSE 6
   END ASC, 
   CASE WHEN ABS(cars.price - 347900) < cars.price * 0.2 THEN 1
        WHEN ABS(cars.price - 347900) < cars.price * 0.4 THEN 2
        WHEN ABS(cars.price - 347900) < cars.price * 0.6 THEN 3
        ELSE 4
   END, 
   CASE WHEN images_count = 0 OR images_count IS NULL THEN 1
        ELSE 0
   END,
   ABS(cars.price - 347900) 
LIMIT 61;

我正在尝试创建一个索引，以使此查询执行得更快。我感到困惑的是为什么以下两个索引同样快，并且具有 exec 相同的查询计划：

CREATE INDEX CONCURRENTLY IF NOT EXISTS index_cars_v2
        ON de.cars USING btree
        (
          (price)
        )
        WHERE sales_state::text = 'onsale'::text 
           AND is_disabled IS NOT TRUE 
           AND featuring_score IS NOT NULL

和

CREATE INDEX CONCURRENTLY IF NOT EXISTS index_cars_v2
        ON de.cars USING btree
        (
          (CASE WHEN cars.featuring_score < 'C' THEN 1 WHEN cars.featuring_score = 'C' THEN 2 WHEN cars.featuring_score > 'C' THEN 3 ELSE 4 END),
          (CASE WHEN cars.au_rating >= 3 THEN 1 WHEN cars.au_rating = 0 THEN 2 WHEN cars.au_rating = 2 THEN 3 WHEN cars.au_rating = 1 THEN 4 ELSE 6 END),
          (price)
        )
        WHERE sales_state::text = 'onsale'::text 
           AND is_disabled IS NOT TRUE 
           AND featuring_score IS NOT NULL

两者都会产生一个类似这样的查询计划：

"Limit  (cost=326.63..326.78 rows=61 width=28) (actual time=218.501..218.508 rows=61 loops=1)"
"  Output: id, (CASE WHEN ((featuring_score)::text < 'C'::text) THEN 1 WHEN ((featuring_score)::text = 'C'::text) THEN 2 WHEN ((featuring_score)::text > 'C'::text) THEN 3 ELSE 4 END), (CASE WHEN (au_rating >= 3) THEN 1 WHEN (au_rating = 0) THEN 2 WHEN (au_rating = 2) THEN 3 WHEN (au_rating = 1) THEN 4 ELSE 6 END), (CASE brand WHEN 'Audi'::text THEN 1 WHEN 'Alpina'::text THEN 2 WHEN 'Artega'::text THEN 3 WHEN 'BMW'::text THEN 4 WHEN 'Maybach'::text THEN 5 ELSE 6 END), (CASE WHEN ((abs((price - 347900)))::numeric < ((price)::numeric * 0.2)) THEN 1 WHEN ((abs((price - 347900)))::numeric < ((price)::numeric * 0.4)) THEN 2 WHEN ((abs((price - 347900)))::numeric < ((price)::numeric * 0.6)) THEN 3 ELSE 4 END), (CASE WHEN ((images_count = 0) OR (images_count IS NULL)) THEN 1 ELSE 0 END), (abs((price - 347900)))"
"  Buffers: shared hit=60813"
"  ->  Sort  (cost=326.63..327.35 rows=286 width=28) (actual time=218.499..218.501 rows=61 loops=1)"
"        Output: id, (CASE WHEN ((featuring_score)::text < 'C'::text) THEN 1 WHEN ((featuring_score)::text = 'C'::text) THEN 2 WHEN ((featuring_score)::text > 'C'::text) THEN 3 ELSE 4 END), (CASE WHEN (au_rating >= 3) THEN 1 WHEN (au_rating = 0) THEN 2 WHEN (au_rating = 2) THEN 3 WHEN (au_rating = 1) THEN 4 ELSE 6 END), (CASE brand WHEN 'Audi'::text THEN 1 WHEN 'Alpina'::text THEN 2 WHEN 'Artega'::text THEN 3 WHEN 'BMW'::text THEN 4 WHEN 'Maybach'::text THEN 5 ELSE 6 END), (CASE WHEN ((abs((price - 347900)))::numeric < ((price)::numeric * 0.2)) THEN 1 WHEN ((abs((price - 347900)))::numeric < ((price)::numeric * 0.4)) THEN 2 WHEN ((abs((price - 347900)))::numeric < ((price)::numeric * 0.6)) THEN 3 ELSE 4 END), (CASE WHEN ((images_count = 0) OR (images_count IS NULL)) THEN 1 ELSE 0 END), (abs((price - 347900)))"
"        Sort Key: (CASE WHEN ((cars.featuring_score)::text < 'C'::text) THEN 1 WHEN ((cars.featuring_score)::text = 'C'::text) THEN 2 WHEN ((cars.featuring_score)::text > 'C'::text) THEN 3 ELSE 4 END), (CASE WHEN (cars.au_rating >= 3) THEN 1 WHEN (cars.au_rating = 0) THEN 2 WHEN (cars.au_rating = 2) THEN 3 WHEN (cars.au_rating = 1) THEN 4 ELSE 6 END), (CASE cars.brand WHEN 'Audi'::text THEN 1 WHEN 'Alpina'::text THEN 2 WHEN 'Artega'::text THEN 3 WHEN 'BMW'::text THEN 4 WHEN 'Maybach'::text THEN 5 ELSE 6 END), (CASE WHEN ((abs((cars.price - 347900)))::numeric < ((cars.price)::numeric * 0.2)) THEN 1 WHEN ((abs((cars.price - 347900)))::numeric < ((cars.price)::numeric * 0.4)) THEN 2 WHEN ((abs((cars.price - 347900)))::numeric < ((cars.price)::numeric * 0.6)) THEN 3 ELSE 4 END), (CASE WHEN ((cars.images_count = 0) OR (cars.images_count IS NULL)) THEN 1 ELSE 0 END), (abs((cars.price - 347900)))"
"        Sort Method: top-N heapsort  Memory: 33kB"
"        Buffers: shared hit=60813"
"        ->  Index Scan using index_cars_v2 on de.cars  (cost=0.42..316.72 rows=286 width=28) (actual time=0.075..205.774 rows=60655 loops=1)"
"              Output: id, CASE WHEN ((featuring_score)::text < 'C'::text) THEN 1 WHEN ((featuring_score)::text = 'C'::text) THEN 2 WHEN ((featuring_score)::text > 'C'::text) THEN 3 ELSE 4 END, CASE WHEN (au_rating >= 3) THEN 1 WHEN (au_rating = 0) THEN 2 WHEN (au_rating = 2) THEN 3 WHEN (au_rating = 1) THEN 4 ELSE 6 END, CASE brand WHEN 'Audi'::text THEN 1 WHEN 'Alpina'::text THEN 2 WHEN 'Artega'::text THEN 3 WHEN 'BMW'::text THEN 4 WHEN 'Maybach'::text THEN 5 ELSE 6 END, CASE WHEN ((abs((price - 347900)))::numeric < ((price)::numeric * 0.2)) THEN 1 WHEN ((abs((price - 347900)))::numeric < ((price)::numeric * 0.4)) THEN 2 WHEN ((abs((price - 347900)))::numeric < ((price)::numeric * 0.6)) THEN 3 ELSE 4 END, CASE WHEN ((images_count = 0) OR (images_count IS NULL)) THEN 1 ELSE 0 END, abs((price - 347900))"
"              Index Cond: ((cars.price >= 35920) AND (cars.price <= 659880))"
"              Buffers: shared hit=60813"
"Planning Time: 1.118 ms"
"Execution Time: 218.589 ms"

我会想到加入功能CASE WHEN上发言featuring_score，并au_rating根据第一排序标准的指标，以产生更好的性能，因为该指数是预先排序，但它使准确没什么区别。我的假设是否成立，或者我是否在索引定义中做错了什么。

附注。我也试过price在索引中使用last ，这在查询计划中也没有任何区别 - 实际上我也很困惑......

聚苯乙烯。我正在运行 PostgreSQL 11

劳伦兹·阿尔伯

索引必须包含所有 ORDER BY条目，以便可以使用它来避免排序：

CREATE INDEX CONCURRENTLY IF NOT EXISTS index_cars_v2
ON de.cars (
   (CASE WHEN cars.featuring_score < 'C' THEN 1
         WHEN cars.featuring_score = 'C' THEN 2
         WHEN cars.featuring_score > 'C' THEN 3
         ELSE 4
    END),
   (CASE WHEN cars.au_rating >= 3 THEN 1
         WHEN cars.au_rating = 0  THEN 2
         WHEN cars.au_rating = 2  THEN 3
         WHEN cars.au_rating = 1  THEN 4
         ELSE 6
    END),
   (CASE cars.brand
         WHEN 'Audi' THEN 1
         WHEN 'Alpina' THEN 2
         WHEN 'Artega' THEN 3
         WHEN 'BMW' THEN 4
         WHEN 'Maybach' THEN 5
         ELSE 6
    END),
   (CASE WHEN ABS(cars.price - 347900) < cars.price * 0.2 THEN 1
         WHEN ABS(cars.price - 347900) < cars.price * 0.4 THEN 2
         WHEN ABS(cars.price - 347900) < cars.price * 0.6 THEN 3
         ELSE 4
    END),
   (CASE WHEN images_count = 0 OR images_count IS NULL THEN 1
         ELSE 0
    END),
   (ABS(cars.price - 347900))
WHERE sales_state = 'onsale' 
   AND is_disabled IS NOT TRUE 
   AND featuring_score IS NOT NULL;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。