Статьи

Индексы Postgres

Февраль 28, 2019

Недавно у меня возникла ситуация, когда мне нужно было подумать, как я использую индексы Postgres. У меня была простая таблица Book со следующей схемой…

01
02
03
04
05
06
07
08
09
10
11
>\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, чтобы добавить множество тестовых данных:

1
2
3
> 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);

Эта таблица будет поражена много раз этим простым запросом:

1
select * from book where author = 'Tony Biggins' and publisher='Books unlimited';

Чтобы получить объяснение, я сделал:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
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 . Некоторые из вариантов, о которых я думал:

  • Создать индекс по автору или издателю
  • Создать индекс по автору и создать индекс по издателю
  • Создайте индекс комбинации как для индекса, так и для издателя.

Хммм … пусть начнутся расследования. Начните с индексации только автора .

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
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 выполняет сканирование индекса, и общая стоимость значительно ниже, чем тот же запрос, который использует сканирование таблицы. А как насчет подхода с несколькими столбцами? Конечно, поскольку оба используются в запросе, он должен быть быстрее снова, верно?

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
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 использует мультииндекс, но запрос не идет быстрее. Май, залить ? Напомним, как мы заселили стол.

1
2
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);

Есть много строк, но каждая строка имеет уникальное авторское значение и уникальное издательское значение. Это будет означать, что авторский индекс для этого запроса должен работать точно так же. Аналогия может быть, вы идете в музыкальный магазин в поисках нового набора громкоговорителей, которые кто-то сказал вам купить, которые имеют определенную стоимость и определенную выходную мощность (количество ватт). Когда вы входите в магазин, вы видите, что колонки красиво упорядочены по цене, и знаете что? Нет двух комплектов колонок одинаковой стоимости. Думаю об этом. Собираетесь ли вы найти динамики быстрее, если вы используете только стоимость или вы используете стоимость и громкоговоритель?

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

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

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
// 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?

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
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
Ссылка: Индексы Postgres от нашего партнера JCG Алекса Стейвли в блоге Tech Blog в Дублине .
39
Share: