Работа индексов в PostgreSQL

У меня есть несколько вопросов относительно работы индексов в PostgreSQL. У меня есть таблица Friends со следующим индексом:

   Friends ( user_id1 ,user_id2) 

user_id1 и user_id2 являются внешними ключами таблицы user

  1. Являются ли эти эквиваленты? Если нет, то почему?

    Index(user_id1,user_id2) and Index(user_id2,user_id1)
    
  2. Если я создаю Первичный ключ (user_id1, user_id2), он автоматически создает для него индексы и

    Если индексы в первом вопросе не эквивалентны, то какой индекс создается над командой первичного ключа?

63 голоса | спросил codecool 24 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowSat, 24 Sep 2011 18:00:16 +0400 2011, 18:00:16

5 ответов


67

Ниже приведены результаты запроса таблицы в столбце второго столбца индекса с несколькими столбцами .
Эффекты легко воспроизвести для кого-либо. Просто попробуйте дома.

Я тестировал с PostgreSQL 9.0.5 на Debian, используя таблицу среднего размера из реальной базы данных с 23322 строками. Он реализует связь n: m между таблицами adr (адрес) и att (атрибут), но здесь это не актуально. Упрощенная схема:

CREATE TABLE adratt (
  adratt_id serial PRIMARY KEY
, adr_id    integer NOT NULL
, att_id    integer NOT NULL
, log_up    timestamp(0) NOT NULL DEFAULT (now())::timestamp(0)
, CONSTRAINT adratt_uni UNIQUE (adr_id, att_id)
);

Ограничение UNIQUE эффективно реализует уникальный индекс. Я повторил тест с простым индексом, чтобы быть уверенным, и получил идентичные результаты, как ожидалось.

CREATE INDEX adratt_idx ON adratt(adr_id, att_id)

Таблица кластеризуется в индексе adratt_uni и до запуска теста:

CLUSTER adratt;
ANALYZE adratt;

Последовательное сканирование запросов на (adr_id, att_id) выполняется так быстро, как только возможно. Индекс многоколонки по-прежнему будет использоваться для условия запроса только для второго столбца индекса.

Я запускал запросы несколько раз, чтобы заполнить кеш, и выбрал лучшее из десяти прогонов, чтобы получить сопоставимые результаты.

1. Запрос с использованием обоих столбцов

SELECT *
FROM   adratt
WHERE  att_id = 90
AND    adr_id = 10;

 adratt_id | adr_id | att_id |       log_up
-----------+--------+--------+---------------------
       123 |     10 |     90 | 2008-07-29 09:35:54
(1 row)

Вывод EXPLAIN ANALYZE:

Index Scan using adratt_uni on adratt  (cost=0.00..3.48 rows=1 width=20) (actual time=0.022..0.025 rows=1 loops=1)
  Index Cond: ((adr_id = 10) AND (att_id = 90))
Total runtime: 0.067 ms

2. Запрос с использованием первого столбца

SELECT * FROM adratt WHERE adr_id = 10

 adratt_id | adr_id | att_id |       log_up
-----------+--------+--------+---------------------
       126 |     10 |     10 | 2008-07-29 09:35:54
       125 |     10 |     13 | 2008-07-29 09:35:54
      4711 |     10 |     21 | 2008-07-29 09:35:54
     29322 |     10 |     22 | 2011-06-06 15:50:38
     29321 |     10 |     30 | 2011-06-06 15:47:17
       124 |     10 |     62 | 2008-07-29 09:35:54
     21913 |     10 |     78 | 2008-07-29 09:35:54
       123 |     10 |     90 | 2008-07-29 09:35:54
     28352 |     10 |    106 | 2010-11-22 12:37:50
(9 rows)

Вывод EXPLAIN ANALYZE:

Index Scan using adratt_uni on adratt  (cost=0.00..8.23 rows=9 width=20) (actual time=0.007..0.023 rows=9 loops=1)
  Index Cond: (adr_id = 10)
Total runtime: 0.058 ms

3. Запрос с использованием второго столбца

SELECT * FROM adratt WHERE att_id = 90

 adratt_id | adr_id | att_id |       log_up
-----------+--------+--------+---------------------
       123 |     10 |     90 | 2008-07-29 09:35:54
       180 |     39 |     90 | 2008-08-29 15:46:07
...
(83 rows)

Вывод EXPLAIN ANALYZE:

Index Scan using adratt_uni on adratt  (cost=0.00..818.51 rows=83 width=20) (actual time=0.014..0.694 rows=83 loops=1)
  Index Cond: (att_id = 90)
Total runtime: 0.849 ms

4. Отключить индексный & bitmapscan

SET enable_indexscan = off;
SELECT * FROM adratt WHERE att_id = 90

Вывод EXPLAIN ANALYZE:

Bitmap Heap Scan on adratt  (cost=779.94..854.74 rows=83 width=20) (actual time=0.558..0.743 rows=83 loops=1)
  Recheck Cond: (att_id = 90)
  ->  Bitmap Index Scan on adratt_uni  (cost=0.00..779.86 rows=83 width=0) (actual time=0.544..0.544 rows=83 loops=1)
        Index Cond: (att_id = 90)
Total runtime: 0.894 ms
SET enable_bitmapscan = off
SELECT * FROM adratt WHERE att_id = 90

Вывод EXPLAIN ANALYZE:

Seq Scan on adratt  (cost=0.00..1323.10 rows=83 width=20) (actual time=0.009..2.429 rows=83 loops=1)
  Filter: (att_id = 90)
Total runtime: 2.680 ms

Заключение

Как и ожидалось, индекс нескольких столбцов используется для запроса только для второго столбца.
Как и ожидалось, он менее эффективен, но запрос по-прежнему на 3 раза быстрее , чем без индекса.
После отключения сканирования индексов планировщик запросов выбирает сканирование растровой кучи, которое выполняется почти так же быстро. Только после его отключения он возвращается к последовательному сканированию.

ответил Erwin Brandstetter 2 32011vEurope/Moscow11bEurope/MoscowWed, 02 Nov 2011 01:42:26 +0400 2011, 01:42:26
25

re 1) Да и нет.

Для запроса, который использует оба столбца, например. where (user_id1, user_id2) = (1,2) не имеет значения, какой индекс создан.

Для запроса, который имеет условие только для одного из столбцов, например. where user_id1 = 1, это имеет значение, потому что для сравнения оптимизатором можно использовать только «ведущие» столбцы. Таким образом, where user_id1 = 1 сможет использовать индекс (user_id1, user_id2), но он не сможет индексировать (user_id2, user_id1) для всех случаев.

После игры с этим (после того, как Эрвин так любезно показал нам установку, где он работает), похоже, что это сильно зависит от распределения данных второго столбца, хотя я еще не выяснил, какая ситуация позволяет оптимизатору используйте конечные столбцы для условия WHERE.

Oracle 11, который также может (иногда) использовать столбцы, которые не находятся в начале определения индекса.

re 2) Да, он создаст индекс

Цитата из руководства

  

Добавление первичного ключа автоматически создаст уникальный индекс btree для столбца или группы столбцов, используемых в первичном ключе.

re 2a) Primary Key (user_id1,user_id2) создаст индекс (user_id1, user_id2) (который вы можете легко найти сами very , просто создав такой первичный ключ)

Я настоятельно рекомендую вам прочитать главу об индексах в руководстве , он в основном отвечает на все вышеперечисленные вопросы.

Кроме того, Какой индекс для создания? by depesz делает хорошую работу, объясняя порядок по столбцам индекса и другим темам, связанным с индексом.

ответил a_horse_with_no_name 24 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowSat, 24 Sep 2011 18:11:31 +0400 2011, 18:11:31
9

Объявление 1)
Существуют ограничения в PostgreSQL как @a_horse_with_no_name . До версия 8.0 многоколоночные индексы могут использоваться только для запросов на ведущей колонке. Это было улучшено в версии 8.1. текущее руководство для Postgres 10 (обновлено) объясняет:

  

Многоколоночный индекс B-дерева может использоваться с условиями запроса, которые   включают любое подмножество столбцов индекса, но индекс больше всего   эффективно, когда есть ограничения на ведущую (левую)   колонны. Точное правило состоит в том, что ограничения равенства на ведущих   столбцов плюс любые ограничения неравенства в первом столбце, который делает   не имеют ограничения равенства, будут использоваться для ограничения части   который сканируется. Ограничения на столбцы справа от   эти столбцы проверяются в индексе, поэтому они сохраняют посещения   таблицы, но они не уменьшают часть индекса, который имеет   для сканирования. Например, с учетом индекса на (a, b, c) и запроса   условие WHERE a = 5 AND b >= 42 AND c < 77, индекс должен был бы   сканироваться из первой записи с помощью a = 5 и b = 42 вверх через   последняя запись с a = 5. Индексированные записи с c> = 77 будут пропущены,   но их все равно придется проверять. Этот показатель может   принцип должен использоваться для запросов, которые имеют ограничения на b и /или c с   нет ограничений на a - но весь индекс нужно будет отсканировать, поэтому   в большинстве случаев планировщик предпочтет последовательное сканирование таблицы   используя индекс.

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

ответил Erwin Brandstetter 3 +04002011-10-03T01:26:26+04:00312011bEurope/MoscowMon, 03 Oct 2011 01:26:26 +0400 2011, 01:26:26
8

Обновлен 2014-11-05 для адаптации к текущим версиям Postgres.
Это ответ на ответ Джека , комментарий не будет " т. е. делать.

В версии PostgreSQL не было нет индексов покрытия до версии 9.2. Из-за модели MVCC каждый кортеж в результирующем наборе должен быть посещен для проверки видимости. Возможно, вы думаете о Oracle. Читайте здесь .

Разработчики PostgreSQL говорят о "сканировании только по индексу" . Фактически, эта функция была выпущена с помощью Postgres 9.2. прочитать сообщение о фиксации .
Depesz написал очень информативное сообщение в блоге .

Настоящие индексы покрытия (обновление) представлены с предложением INCLUDE с Postgres 11. Связанный ответ со ссылками:

Это тоже немного:

  

он полагается на то, что «полное сканирование» индекса часто бывает более быстрым   чем «полное сканирование» индексированной таблицы из-за дополнительных столбцов в   таблицу, которая не отображается в индексе.

Как сообщается в комментариях к моему другому ответу, я также запускал тесты с таблицей из двух целых чисел и ничего больше. Индекс содержит те же столбцы, что и таблица. Размер индекса btree составляет около 2/3 от таблицы. Не достаточно, чтобы объяснить ускорение фактора 3. Я провел больше тестов, основываясь на вашей настройке, упрощенной до двух столбцов и с 100000 строк. На моей установке PostgreSQL 9.0 результаты были согласованы.

Если в таблице есть дополнительные столбцы, ускорение с индексом становится более существенным, но , что, безусловно, не является единственным фактором здесь .

Подводя итог основным моментам:

  • Многоколоночные индексы могут использоваться с запросами в не ведущих столбцах, но ускорение только вокруг фактора 3 для выборочных критериев (небольшой процент строк в результате). Более высокий для больших кортежей, более низкий для большей части таблицы в результирующем наборе.

  • Создайте дополнительный индекс для этих столбцов, если производительность важна.

  • Если все задействованные столбцы включены в индекс (индекс покрытия), и все задействованные строки (за каждый блок) видимы для всех транзакций, вы можете получить " проверка только индекса " в стр. 9.2 или новее.

ответил Erwin Brandstetter 3 42011vEurope/Moscow11bEurope/MoscowThu, 03 Nov 2011 00:27:57 +0400 2011, 00:27:57
6
  
  1. Являются ли эти эквиваленты? Если нет, то почему?

         

    Индекс (user_id1, user_id2) и индекс (user_id2, user_id1)

  2.   

Это не эквивалентны, и, вообще говоря, индекс (bar, baz) не будет эффективен для запросов формы select * from foo where baz=?

Erwin продемонстрировал , что такие индексы могут действительно ускорить но этот эффект ограничен, а не того же порядка, что и вы, как правило, ожидаете, что индекс улучшит поиск - он полагается на то, что «полное сканирование» индекса часто происходит быстрее, чем «полное сканирование» индексированная таблица из-за дополнительных столбцов в таблице, которые не отображаются в индексе.

Сводка: индексы могут помогать запросам даже в не ведущих столбцах, но в одном из двух вторичных и относительно небольших способов, а не в том, что вы обычно ожидаете, что индекс поможет из-за его структуры btree

nb двумя способами, которые может помочь индекс, является то, что полное сканирование индекса значительно дешевле, чем полное сканирование таблицы, и либо:   1. Поиск таблицы дешево (потому что их мало или они кластеризованы), или   2. индекс охватывает , поэтому нет поиска таблиц вообще oops, см. комментарии Erwins здесь

обкатки:

create table foo(bar integer not null, baz integer not null, qux text not null);

insert into foo(bar, baz, qux)
select random()*100, random()*100, 'some random text '||g from generate_series(1,10000) g;

запрос 1 (нет индекса, нажатие 74 буфера ):

explain (buffers, analyze, verbose) select max(qux) from foo where baz=0;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=181.41..181.42 rows=1 width=32) (actual time=3.301..3.302 rows=1 loops=1)
   Output: max(qux)
   Buffers: shared hit=74
   ->  Seq Scan on stack.foo  (cost=0.00..181.30 rows=43 width=32) (actual time=0.043..3.228 rows=52 loops=1)
         Output: bar, baz, qux
         Filter: (foo.baz = 0)
         Buffers: shared hit=74
 Total runtime: 3.335 ms

query 2 (с индексом - оптимизатор игнорирует индекс - нажатие 74 буферов снова):

create index bar_baz on foo(bar, baz);

explain (buffers, analyze, verbose) select max(qux) from foo where baz=0;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=199.12..199.13 rows=1 width=32) (actual time=3.277..3.277 rows=1 loops=1)
   Output: max(qux)
   Buffers: shared hit=74
   ->  Seq Scan on stack.foo  (cost=0.00..199.00 rows=50 width=32) (actual time=0.043..3.210 rows=52 loops=1)
         Output: bar, baz, qux
         Filter: (foo.baz = 0)
         Buffers: shared hit=74
 Total runtime: 3.311 ms

query 2 (с индексом - и мы обманом оптимизируем его использование):

explain (buffers, analyze, verbose) select max(qux) from foo where bar>-1000 and baz=0;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=115.56..115.57 rows=1 width=32) (actual time=1.495..1.495 rows=1 loops=1)
   Output: max(qux)
   Buffers: shared hit=36 read=30
   ->  Bitmap Heap Scan on stack.foo  (cost=73.59..115.52 rows=17 width=32) (actual time=1.370..1.428 rows=52 loops=1)
         Output: bar, baz, qux
         Recheck Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
         Buffers: shared hit=36 read=30
         ->  Bitmap Index Scan on bar_baz  (cost=0.00..73.58 rows=17 width=0) (actual time=1.356..1.356 rows=52 loops=1)
               Index Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
               Buffers: shared read=30
 Total runtime: 1.535 ms

Таким образом, доступ через индекс в два раза быстрее, в этом случае ударяя 30 буферов , которые с точки зрения индексирования «немного быстрее»! и YMMV в зависимости от относительного размера таблицы и индекса, наряду с количеством отфильтрованных строк и характеристиками кластеризации данных в таблице

В отличие от запросов в ведущем столбце используется структура btree индекса - в этом случае нажатие 2 буфера :

explain (buffers, analyze, verbose) select max(qux) from foo where bar=0;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=75.70..75.71 rows=1 width=32) (actual time=0.172..0.173 rows=1 loops=1)
   Output: max(qux)
   Buffers: shared hit=38
   ->  Bitmap Heap Scan on stack.foo  (cost=4.64..75.57 rows=50 width=32) (actual time=0.036..0.097 rows=59 loops=1)
         Output: bar, baz, qux
         Recheck Cond: (foo.bar = 0)
         Buffers: shared hit=38
         ->  Bitmap Index Scan on bar_baz  (cost=0.00..4.63 rows=50 width=0) (actual time=0.024..0.024 rows=59 loops=1)
               Index Cond: (foo.bar = 0)
               Buffers: shared hit=2
 Total runtime: 0.209 ms
ответил Jack Douglas 2 32011vEurope/Moscow11bEurope/MoscowWed, 02 Nov 2011 14:59:19 +0400 2011, 14:59:19

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132