Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 PostgreSQL с покрывающим индексом лезет в HEAP (heap blocks lossy = 0)...  [new]
Cyrax_02
Member

Откуда:
Сообщений: 1462
Есть таблица связей tbl (main_id int8, rel_id int8, flag bool)
Есть покрывающие индексы mr_idx (main_id, rel_id) и rfm_idx (rel_id, flag, main_id)

Выполняем запрос:
EXPLAIN(ANALYSE) SELECT rel_id FROM tbl WHERE (main_id = 31524);

Index Only Scan using mr_idx on tbl (cost=0.28..2.50 rows=1 width=4) (actual time=0.022..0.025 rows=2 loops=1)
  Index Cond: (main_id = 31524)
  Heap Fetches: 2
Planning Time: 0.085 ms
Execution Time: 0.045 ms
Здесь всё нормально: используется только индекс mr_idx, записи не читаются.

Выполняем противоположный запрос с задействованием поля "flag":
EXPLAIN(ANALYSE) SELECT main_id FROM tbl WHERE (rel_id = 13612) AND flag;

Bitmap Heap Scan on tbl (cost=1.41..4.67 rows=3 width=4) (actual time=0.026..0.032 rows=3 loops=1)
  Recheck Cond: (rel_id = 13612)
  Filter: flag
  Heap Blocks: exact=3
  ->  Bitmap Index Scan on rfm_idx (cost=0.00..1.41 rows=3 width=0) (actual time=0.018..0.018 rows=3 loops=1)
        Index Cond: ((rel_id = 13612) AND (flag = true))
Planning Time: 0.114 ms
Execution Time: 0.057 ms
А вот здесь PostgreSQL почему-то лезет в Heap, хотя индекс rfm_idx тоже покрывающий.

Вопросы:
1) Если в Heap он лезет из-за необходимости перепроверки условия, то почему возникает необходимость перепроверки ?
Как видно из плана, heap blocks lossy = 0.
2) Если перепроверка условия не выполняется, тогда зачем он лезет в Heap ?

Сообщение было отредактировано: 13 апр 21, 16:32
13 апр 21, 16:37    [22308356]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL с покрывающим индексом лезет в HEAP (heap blocks lossy = 0)...  [new]
Cyrax_02
Member

Откуда:
Сообщений: 1462
Cyrax_02
Как видно из плана, heap blocks lossy = 0
Впрочем, exact/lossy здесь вообще влиять ни на что не должны, т.к. индексы покрывающие.
Даже при (heap blocks lossy > 0) всё равно он не должен лезть в heap...
13 апр 21, 17:03    [22308375]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL с покрывающим индексом лезет в HEAP (heap blocks lossy = 0)...  [new]
Melkij
Member

Откуда: Санкт-Петербург
Сообщений: 1285
Cyrax_02
Heap Fetches: 2
Здесь всё нормально: используется только индекс mr_idx, записи не читаются.

Вам рассказать, что вы заблуждаетесь с самого начала?
За обеими строками сходили в таблицу перепроверить видимость для текущей транзакции. Index Only Scan - это не отсутствие обращения к heap, а возможность не обращаться к heap при некоторых условиях. В частности, если visibility map разрешит.
13 апр 21, 17:29    [22308391]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL с покрывающим индексом лезет в HEAP (heap blocks lossy = 0)...  [new]
Cyrax_02
Member

Откуда:
Сообщений: 1462
Melkij
За обеими строками сходили в таблицу перепроверить видимость для текущей транзакции
Т.е. поход в таблицу за перепроверкой условия сопровождается строкой "Bitmap Heap Scan",
А поход в таблицу за проверкой существования строки для текущей транзакции сопровождается строкой "Heap Fetches"
Так?

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

Сообщение было отредактировано: 13 апр 21, 18:13
13 апр 21, 18:18    [22308409]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL с покрывающим индексом лезет в HEAP (heap blocks lossy = 0)...  [new]
Cyrax_02
Member

Откуда:
Сообщений: 1462
Если выполнить
VACUUM tbl;
то оба запроса дают нормальный план: Index Only Scan + Heap Fetches: 0 (в таблицу не лезет)

Но если выполнить
VACUUM FULL tbl;
то для первого запроса получаем Index Only Scan + Heap Fetches: 2 (полез в таблицу)
для второго запроса получаем Bitmap Index Scan + Bitmap Heap Scan (застрял в таблице со всеми потрохами)

Вопросы:
1) Почему VACUUM FULL делает visibility map неактуальной ?
2) Почему во втором запросе он лезет в таблицу ? Причём не для проверки видимости в транзакции, а по какой-то иной нужде. Что он там забыл ?

Сообщение было отредактировано: 13 апр 21, 19:57
13 апр 21, 20:04    [22308438]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL с покрывающим индексом лезет в HEAP (heap blocks lossy = 0)...  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4719
Cyrax_02
Если выполнить
VACUUM tbl;
то оба запроса дают нормальный план: Index Only Scan + Heap Fetches: 0 (в таблицу не лезет)

Но если выполнить
VACUUM FULL tbl;
то для первого запроса получаем Index Only Scan + Heap Fetches: 2 (полез в таблицу)
для второго запроса получаем Bitmap Index Scan + Bitmap Heap Scan (застрял в таблице со всеми потрохами)

Вопросы:
1) Почему VACUUM FULL делает visibility map неактуальной ?
2) Почему во втором запросе он лезет в таблицу ? Причём не для проверки видимости в транзакции, а по какой-то иной нужде. Что он там забыл ?


1)потому что он с нуля перестраивает таблицу и visibility map теряется скорее всего (не сюрприз)
2)потому что bitmap scan не умеет index only вообще... т.е. bitmap scan ВСЕГДА будет данные таблицы читать.

PS: это уже уровень вопросов на которые лучший ответ будет - посмотрите в исходниках если вы в такие детали влезаете.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
13 апр 21, 20:10    [22308443]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL с покрывающим индексом лезет в HEAP (heap blocks lossy = 0)...  [new]
Cyrax_02
Member

Откуда:
Сообщений: 1462
автор
1)потому что он с нуля перестраивает таблицу и visibility map теряется скорее всего (не сюрприз)
Такое поведение больше похоже на ошибку в реализации команды VACUUM FULL. При выполнении VACUUM visibility map актуализируется (как и должно быть), а при выполнении VACUUM FULL после пересоздания таблицы visibility map не актуализируется. Явно, пробел в реализации...

автор
2)потому что bitmap scan не умеет index only вообще... т.е. bitmap scan ВСЕГДА будет данные таблицы читать.
Так вопрос в том, почему для 2-го запроса при неактуальном visibility map запускается Bitmap scan вместо Index Only Scan / Heap Fetches=2

Сообщение было отредактировано: 13 апр 21, 21:23
13 апр 21, 21:26    [22308462]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL с покрывающим индексом лезет в HEAP (heap blocks lossy = 0)...  [new]
Cyrax_02
Member

Откуда:
Сообщений: 1462
В РБД слабым звеном по части производительности являются таблицы связей и если покрывающие индексы на этих таблицах не выполняют свою основную функцию, естественным образом возникают сабжевые вопросы...
13 апр 21, 21:30    [22308465]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL с покрывающим индексом лезет в HEAP (heap blocks lossy = 0)...  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4719
Cyrax_02

автор
2)потому что bitmap scan не умеет index only вообще... т.е. bitmap scan ВСЕГДА будет данные таблицы читать.
Так вопрос в том, почему для 2-го запроса при неактуальном visibility map запускается Bitmap scan вместо Index Only Scan / Heap Fetches=2


А вот с этим уже можно и разбираться... выбор действительно странный...
покажите
1)размеры таблицы и обоих индексов
2)количество строк в таблице

PS: любое performance тестирование имеет смысл делать на таблицах от 100 страниц и 100.000 строк иначе там планы могут быть странные
хотя и быстрые.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru

Сообщение было отредактировано: 13 апр 21, 22:11
13 апр 21, 22:14    [22308484]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL с покрывающим индексом лезет в HEAP (heap blocks lossy = 0)...  [new]
Cyrax_02
Member

Откуда:
Сообщений: 1462
Maxim Boguk
Cyrax_02
Так вопрос в том, почему для 2-го запроса при неактуальном visibility map запускается Bitmap scan вместо Index Only Scan / Heap Fetches=2
А вот с этим уже можно и поразбиратся... выбор действительно странный...
покажите
1)размеры таблицы и обоих индексов
2)количество строк в таблице

PS: любое performance тестирование имеет смысл делать на таблицах от 100 страниц и 100.000 строк иначе там планы могут быть странные
хотя и быстрые.

Таблица маленькая: 8000 строк, индексы 192К и 264К, work_mem = 64M

Сообщение было отредактировано: 13 апр 21, 22:16
13 апр 21, 22:21    [22308487]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL с покрывающим индексом лезет в HEAP (heap blocks lossy = 0)...  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4719
Cyrax_02
Maxim Boguk
пропущено...
А вот с этим уже можно и поразбиратся... выбор действительно странный...
покажите
1)размеры таблицы и обоих индексов
2)количество строк в таблице

PS: любое performance тестирование имеет смысл делать на таблицах от 100 страниц и 100.000 строк иначе там планы могут быть странные
хотя и быстрые.

Таблица маленькая: 8000 строк, индексы 192К и 264К, work_mem = 64M


Ну вот сделайте раз в 100 побольше и тестируйте наздоровье.

Или отключите bitmap scan на текущих запросах и сравните время с ним и без него... если время близкое или bitmap scan быстрее - выбор базы вполне оправдан.
Если посмотреть на два начальных запроса то скорость работы у них фактически одинакова... bitmap scan даже немного быстрее в расчете на 1 строку результата.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
13 апр 21, 22:26    [22308494]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL с покрывающим индексом лезет в HEAP (heap blocks lossy = 0)...  [new]
Cyrax_02
Member

Откуда:
Сообщений: 1462
Maxim Boguk
если ... - выбор базы вполне оправдан...

А разве Bitmap scan (Bitmap Index Scan + Bitmap Heap Scan) может быть быстрее Index Only Scan / Heap Fetches ??
Если нет, то выбор плана выполнения объясняется не соображениями скорости...

Сообщение было отредактировано: 13 апр 21, 22:38
13 апр 21, 22:43    [22308498]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL с покрывающим индексом лезет в HEAP (heap blocks lossy = 0)...  [new]
Cyrax_02
Member

Откуда:
Сообщений: 1462
Ещё такой вопрос: в первом посте план запроса содержит строку "Index Only Scan" - здесь всё понятно.
(1) А что означает просто "Index Scan" (без Only) ??

Вот пример такого плана (без filter):
Index Scan using rel_type_flag_idx on test  (cost=0.28..55.79 rows=539 width=11) (actual time=0.257..0.792 rows=539 loops=1)
  Index Cond: ((rel_type_id = 11) AND (flag = true))
  Buffers: shared hit=531 read=4
Planning:
  Buffers: shared hit=176
Planning Time: 1.631 ms
Execution Time: 0.988 ms

И ещё пример (с filter):
Index Scan using main_rel_pk on test  (cost=0.28..2.50 rows=1 width=11) (actual time=0.040..0.042 rows=1 loops=1)
  Index Cond: (main_id = 31524)
  Filter: (flag AND (rel_type_id = 11))
  Rows Removed by Filter: 1
  Buffers: shared hit=10
Planning:
  Buffers: shared hit=4
Planning Time: 0.158 ms
Execution Time: 0.060 ms
(2) Здесь непонятно, как производится фильтрация (filter) - на основе другого индекса (который содержит нужные поля - есть такой в таблице), или лезет в таблицу (тогда почему не упоминается heap ?)

Сообщение было отредактировано: 13 апр 21, 22:57
13 апр 21, 23:04    [22308508]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL с покрывающим индексом лезет в HEAP (heap blocks lossy = 0)...  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4719
Cyrax_02
Ещё такой вопрос: в первом посте план запроса содержит строку "Index Only Scan" - здесь всё понятно.
(1) А что означает просто "Index Scan" (без Only) ??

Вот пример такого плана (без filter):
Index Scan using rel_type_flag_idx on test  (cost=0.28..55.79 rows=539 width=11) (actual time=0.257..0.792 rows=539 loops=1)
  Index Cond: ((rel_type_id = 11) AND (flag = true))
  Buffers: shared hit=531 read=4
Planning:
  Buffers: shared hit=176
Planning Time: 1.631 ms
Execution Time: 0.988 ms

И ещё пример (с filter):
Index Scan using main_rel_pk on test  (cost=0.28..2.50 rows=1 width=11) (actual time=0.040..0.042 rows=1 loops=1)
  Index Cond: (main_id = 31524)
  Filter: (flag AND (rel_type_id = 11))
  Rows Removed by Filter: 1
  Buffers: shared hit=10
Planning:
  Buffers: shared hit=4
Planning Time: 0.158 ms
Execution Time: 0.060 ms
(2) Здесь непонятно, как производится фильтрация (filter) - на основе другого индекса (который содержит нужные поля - есть такой в таблице), или лезет в таблицу (тогда почему не упоминается heap ?)


Index scan - результаты вам отдаются из ТАБЛИЦЫ а не из индекса напрямую.
filter - фильтрация результатов по данным из ТАБЛИЦЫ

heap не упоминается потому что он имеет смысл только для IOS/bitmap scan... в остальных случаях heap = rows+Rows Removed by Filter и как то специально его показывать смысла нет.

за сим я сей тред покидаю.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
13 апр 21, 23:10    [22308511]     Ответить | Цитировать Сообщить модератору
 Re: PostgreSQL с покрывающим индексом лезет в HEAP (heap blocks lossy = 0)...  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4719
Cyrax_02
Maxim Boguk
если ... - выбор базы вполне оправдан...

А разве Bitmap scan (Bitmap Index Scan + Bitmap Heap Scan) может быть быстрее Index Only Scan / Heap Fetches ??
Если нет, то выбор плана выполнения объясняется не соображениями скорости...


может конечно иначе бы этот механизм бы не делали вообще.
особенно на механических дисках - так вообще на раз потому что он последовательное более менее чтение дает в таблицы в отличии от index scan/ios.


--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
13 апр 21, 23:12    [22308512]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить