Почему добавление отсортированных столбцов в индекс не делает его более эффективным?

Question

Почему добавление отсортированных столбцов в индекс не делает его более эффективным?

У меня есть такой запрос:

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;

Я пытаюсь создать индекс, который сделает этот запрос быстрее. Я озадачен тем, что следующие два индекса одинаково быстры и имеют один и тот же план запросов:

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 к индексам для повышения производительности, так как индекс будет предварительно отсортирован в соответствии с первыми критериями сортировки, однако это не имеет абсолютно никакого значения. Мое предположение выключено или я что-то делаю неправильно в определении индекса.

PS. Я также пытался price последний в индексе, это не имело никакого значения ни в плане запроса - который я на самом деле также озадачен...

PPS. Я использую PostgreSQL 11

0

postgresql indexing database-performance postgresql-11

Источник

user1002814 29 янв '19 в 11:03

1 ответ

Решение

Другие вопросы по тегам postgresql indexing database-performance postgresql-11

user6464308 29 янв '19 в 11:42 2019-01-29 11:42 · Accepted Answer · 2019-01-29 11:42

Индекс должен содержать все 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;