Недавно у меня возникла ситуация, когда мне нужно было подумать о том, как я использую индексы Postgres. У меня была простая таблица Book со следующей схемой …
>\d book
Table "shopping.book"
Column | Type | Modifiers
---------------------+------------------------+-----------
id | uuid | not null
version | bigint | not null
amount_minor_units | integer | not null
currency | character varying(255) | not null
author | character varying(255) | not null
publisher | character varying(255) |
Столбцы автора и издателя были просто указателями String на фактические ссылки на авторов и издателей, которые были в другой системе, что означало, что классические внешние ключи нельзя использовать и что эти парни были просто обычными столбцами.
Мне нужно было понять, как таблица будет работать с большим количеством данных, поэтому сначала немного простого SQL, чтобы добавить множество тестовых данных:
> CREATE EXTENSION "uuid-ossp";
> insert into book (id, version, amount_minor_units, currency, author, publisher)
select uuid_generate_v4(), 2, 22, 'USD', 'author' || x.id, 'publisher' || x.id from generate_series(1,1000000) AS x(id);
Эта таблица будет поражена много раз этим простым запросом:
select * from book where author = 'Tony Biggins' and publisher='Books unlimited';
Чтобы получить объяснение, я сделал:
dublintech=> EXPLAIN (FORMAT JSON) select * from book where author = 'Tony Biggins' and publisher = 'Books unlimited';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Seq Scan", +
"Relation Name": "book", +
"Alias": "book", +
"Startup Cost": 0.00, +
"Total Cost": 123424.88, +
"Plan Rows": 1, +
"Plan Width": 127, +
"Filter": "(((author)::text = 'Tony Biggins'::text) AND ((publisher)::text = 'Books unlimited'::text))"+
} +
} +
]
(1 row)
Как видно, Postgres выполняет сканирование Seq , то есть сканирование таблицы . Я хотел ускорить процесс. В таблице был только один индекс для идентификатора . Это был просто обычный индекс B-Tree, который был бы бесполезен в этом запросе, поскольку его даже не было в предложении where . Некоторые из вариантов, о которых я думал:
- Создать индекс по автору или издателю
- Создать индекс по автору и создать индекс по издателю
- Создайте индекс комбинации как для индекса, так и для издателя.
Хммм … пусть начнутся расследования. Начните с индексации только автора .
dublintech=> create index author_idx on book(author);
dublintech=> EXPLAIN (FORMAT JSON) select * from book where publisher = 'publisher3' and author='author3';
QUERY PLAN
-------------------------------------------------------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Index Scan", +
"Scan Direction": "Forward", +
"Index Name": "author_idx", +
"Relation Name": "book", +
"Alias": "book", +
"Startup Cost": 0.42, +
"Total Cost": 8.45, +
"Plan Rows": 1, +
"Plan Width": 127, +
"Index Cond": "((author)::text = 'author3'::text)",+
"Filter": "((publisher)::text = 'publisher3'::text)" +
} +
} +
]
(1 row)
Как видно, Postgres выполняет сканирование индекса, и общая стоимость значительно ниже, чем тот же запрос, который использует сканирование таблицы. А как насчет подхода с несколькими столбцами? Конечно, поскольку оба используются в запросе, он должен быть быстрее снова, верно?
dublintech=> create index author_publisher_idx on book(author, publisher);
CREATE INDEX
dublintech=> EXPLAIN (FORMAT JSON) select * from book where publisher = 'publisher3' and author='author3';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Index Scan", +
"Scan Direction": "Forward", +
"Index Name": "author_publisher_idx", +
"Relation Name": "book", +
"Alias": "book", +
"Startup Cost": 0.42, +
"Total Cost": 8.45, +
"Plan Rows": 1, +
"Plan Width": 127, +
"Index Cond": "(((author)::text = 'author3'::text) AND ((publisher)::text = 'publisher3'::text))"+
} +
} +
]
(1 row)
На этот раз Postgres использует мультииндекс, но запрос не идет быстрее. Май, залить ? Напомним, как мы заселили стол.
insert into book (id, version, amount_minor_units, currency, author, publisher)
select uuid_generate_v4(), 2, 22, 'USD', 'author' || x.id, 'publisher' || x.id from generate_series(1,1000000) AS x(id);
Есть много строк, но каждая строка имеет уникальное авторское значение и уникальное издательское значение. Это будет означать, что авторский индекс для этого запроса должен работать точно так же. Можно привести аналогию: вы идете в музыкальный магазин в поисках нового набора громкоговорителей, которые кто-то сказал вам купить, которые имеют определенную стоимость и определенную выходную мощность (количество ватт). Когда вы входите в магазин, вы видите, что колонки красиво упорядочены по цене, и знаете что? Нет двух комплектов колонок одинаковой стоимости. Думаю об этом. Собираетесь ли вы найти динамики быстрее, если вы используете только стоимость или вы используете стоимость и громкоговоритель?
Теперь представьте себе случай, если множество громкоговорителей были одинаковой стоимости. Тогда, конечно, использование как стоимости, так и мощности будет быстрее.
Теперь давайте возьмем эту точку до крайности в наших тестовых данных. Предположим, что все авторы были одинаковыми. Индекс автора становится бесполезным, и если у нас нет индекса комбинации автор / издатель, мы вернемся к просмотру таблицы.
// drop combination index and just leave author index on table
dublintech=> drop index author_uesr_ref_idx;
DROP INDEX
dublintech=> update book set author='author3';
dublintech=> EXPLAIN (FORMAT JSON) select * from book where publisher = 'publisher3' and author='author3';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Seq Scan", +
"Relation Name": "book", +
"Alias": "book", +
"Startup Cost": 0.00, +
"Total Cost": 153088.88, +
"Plan Rows": 1, +
"Plan Width": 123, +
"Filter": "(((publisher)::text = 'publisher3'::text) AND ((author)::text = 'author3'::text))"+
} +
} +
]
(1 row)
Таким образом, из этого можно сделать вывод, что индексы с одним столбцом для комбинированного поиска могут работать так же хорошо, как и комбинационные индексы, когда в данных этого отдельного столбца существует огромная разница. Однако, когда их нет, они не будут работать так же хорошо, и следует использовать комбинационный индекс. Да, я проверил крайности, но это лучший способ прояснить принципы. И обратите внимание: для случая, когда существует максимальная разница в данных, добавление другого индекса в другой столбец в предложении where, издатель не имеет значения. Это как и ожидалось.
Хорошо, давайте остановимся на случае, когда в столбце есть значительная разница в значениях данных. Рассмотрим случай максимальной дисперсии, и в запросе всегда используется только точное соответствие . В этом случае все авторские ценности гарантированно будут уникальными, и вы никогда не будете заинтересованы в том, чтобы делать что-то вроде меньше или больше чем . Так почему бы не использовать хеш-индекс вместо индекса B-Tree?
dublintech=> create index author_hash on book using hash (author);
dublintech=> EXPLAIN (FORMAT JSON) select * from book where publisher = 'publisher3' and author='author3';
QUERY PLAN
-------------------------------------------------------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Index Scan", +
"Scan Direction": "NoMovement", +
"Index Name": "author_hash", +
"Relation Name": "book", +
"Alias": "book", +
"Startup Cost": 0.00, +
"Total Cost": 8.02, +
"Plan Rows": 1, +
"Plan Width": 127, +
"Index Cond": "((author)::text = 'author3'::text)",+
"Filter": "((publisher)::text = 'publisher3'::text)" +
} +
} +
]
(1 row)
Интересно, мы снова пошли быстрее. На этот раз не большая разница, но, тем не менее, это улучшение, которое может быть более актуальным при большем росте данных и / или когда требуется более сложный запрос с большим количеством вычислений. Из этой части можно смело сделать вывод, что, если вас интересуют только точные совпадения, тогда хеш-индекс превосходит индекс b-дерева. До следующего раза береги себя.
Ссылки:
- http://www.postgresql.org/docs/9.1/static/using-explain.html
- http://www.postgresql.org/docs/9.3/static/indexes-bitmap-scans.html