Postgres: индекс по нескольким столбцам с другим ведущим столбцом

У меня есть два индекса двух столбцов:

create index idx_film_length_rating on film (length, rating);
create index idx_film_rating_length on film (rating, length);

Когда я выполняю:

explain analyze select title, length, rating, replacement_cost, rental_rate
from film
where rating = 'G' and length between 60 and 70

Постгрес считает, что idx_film_rating_length всегда является лучшим вариантом, и использует этот индекс. Но почему, если нет второго индекса, idx_film_rating_length, запрос будет работать медленнее? Насколько я понимаю, планы выполнения одинаковые, блоки извлечения одинаковые, они должны быть одинаковыми.

Результат только с одним индексом:

"Bitmap Heap Scan on film  (cost=4.44..35.70 rows=13 width=34) (actual time=0.102..0.120 rows=18 loops=1)"
"  Recheck Cond: ((rating = 'G'::mpaa_rating) AND (length >= 60) AND (length <= 70))"
"  Heap Blocks: exact=14"
"  ->  Bitmap Index Scan on idx_film_rating_length  (cost=0.00..4.44 rows=13 width=0) (actual time=0.095..0.095 rows=18 loops=1)"
"        Index Cond: ((rating = 'G'::mpaa_rating) AND (length >= 60) AND (length <= 70))"
"Planning time: 0.316 ms"
"Execution time: 0.160 ms"

И результат с двумя индексами в таблице:

"Bitmap Heap Scan on film  (cost=4.44..35.70 rows=13 width=34) (actual time=0.030..0.041 rows=18 loops=1)"
"  Recheck Cond: ((rating = 'G'::mpaa_rating) AND (length >= 60) AND (length <= 70))"
"  Heap Blocks: exact=14"
"  ->  Bitmap Index Scan on idx_film_rating_length  (cost=0.00..4.44 rows=13 width=0) (actual time=0.024..0.024 rows=18 loops=1)"
"        Index Cond: ((rating = 'G'::mpaa_rating) AND (length >= 60) AND (length <= 70))"
"Planning time: 0.199 ms"
"Execution time: 0.065 ms"

Вы можете видеть, что хотя планы одинаковы, второй быстрее.

===================================== Рекомендовано @a_horse_with_no_name После добавления буферов и деталей:

"Bitmap Heap Scan on film  (cost=4.44..35.70 rows=13 width=34) (actual time=0.692..0.716 rows=18 loops=1)"
"  Recheck Cond: ((rating = 'G'::mpaa_rating) AND (length >= 60) AND (length <= 70))"
"  Heap Blocks: exact=14"
"  Buffers: shared hit=14 read=2"
"  ->  Bitmap Index Scan on idx_film_cover  (cost=0.00..4.44 rows=13 width=0) (actual time=0.680..0.680 rows=18 loops=1)"
"        Index Cond: ((rating = 'G'::mpaa_rating) AND (length >= 60) AND (length <= 70))"
"        Buffers: shared read=2"
"Planning time: 1.773 ms"
"Execution time: 1.441 ms"

Кажется, проблема может быть связана с: «Буферы: общий хит = 14, чтение = 2»?


person Jie Hu    schedule 21.12.2017    source источник
comment
Используйте explain (analyze, buffers, timing), чтобы увидеть больше деталей и определить различия   -  person a_horse_with_no_name    schedule 21.12.2017
comment
Спасибо, я только что добавил результат с буферами и временем.   -  person Jie Hu    schedule 22.12.2017


Ответы (1)


Вы правы, планы идентичны.

Разница во времени выполнения просто случайна. Возможно, во второй раз кэшировалась большая часть индекса.

Повторите EXPLAIN несколько раз, чтобы получить статистически значимые результаты.

person Laurenz Albe    schedule 21.12.2017
comment
Разницы нет... только один результат. - person Jie Hu; 22.12.2017
comment
Я говорю о разнице во времени выполнения. Как вы подозреваете, чтение буферов имеет значение. - person Laurenz Albe; 22.12.2017