Статьи

Индексы Postgres

Недавно у меня возникла ситуация, когда мне нужно было подумать о том, как я использую индексы 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