Некорректный индекс при использовании ORDER BY c LIMIT n / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Некорректный индекс при использовании ORDER BY c LIMIT n

9 сообщений из 9, страница 1 из 1

Некорректный индекс при использовании ORDER BY c LIMIT n

#40073656

bff7755a

Гость

Есть таблица, на которой висит несколько индексов, в том числе:

Код: sql

1.
2.

1. table_has_symbol_package_id_index btree (has_symbol, package_id)
2. table_id_desc_idx btree (id DESC)

Селективность по первому индексу довольно хорошая, однако если в запросе присутсвует ORDER BY id LIMIT n планировщик выбирает второй индекс и запрос выполняется минуты, вместо нескольких миллисекунд. В качестве временного решения помогает следующее:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.

WITH t as (
SELECT t.id as t_id,
       -- other columns from t
       t2.col1
  FROM table t
  LEFT OUTER JOIN table2 t2 ON (t.manuf_id = t2.id)
  WHERE (t.has_symbol = 1 AND t.package_id = 6360)
) SELECT * FROM t
  ORDER BY t_id DESC LIMIT 10

То есть я вынес сам запрос в WITH, а сортировку и LIMIT оставил снаружи. Это помогает, однако хочется понимать причину происходящего. В рассылке нашёл https://www.postgresql.org/message-id/43A998D4.4070300@gmail.com]письмо с описанием этой проблемы, однако это было в 2005 году и хочется верить что с тех пор планировщик стал умнее.

...

Рейтинг:

0 / 0

27.05.2021, 20:36

| Ответить | Цитировать | Написать

Некорректный индекс при использовании ORDER BY c LIMIT n

#40073681

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

bff7755a,

Вы бы планы чтоли показали сначала... планировщик вполне разумно может считать исходя из имеющейся статистики что план через второй индекс будет быстрее.
Покажите план (explain analyze) для

Код: sql

SELECT * FROM table t WHERE (t.has_symbol = 1 AND t.package_id = 6360);

Код: sql

SELECT * FROM table t WHERE (t.has_symbol = 1 AND t.package_id = 6360) ORDER BY t.id DESC LIMIT 10;

Код: sql

SELECT * FROM table t WHERE (t.has_symbol = 1 AND t.package_id = 6360) ORDER BY t.id+0 DESC LIMIT 10;

Код: sql

SELECT * FROM table t;

Как раз и проверим что база на счет селективностей думает себе. И скорее всего станет понятнее.

PS: к таким вопросам всегда версию базы стоит прилагать.

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

...

Рейтинг:

0 / 0

27.05.2021, 22:58

| Ответить | Цитировать | Написать

Некорректный индекс при использовании ORDER BY c LIMIT n

#40073725

bff7755a

Гость

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

Версия

Код: sql

1.
2.
3.
4.
5.

db=> select version();
                                                                     version                                                                     
-------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.22 on x86_64-pc-linux-gnu (Ubuntu 9.6.22-1.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.12) 5.4.0 20160609, 64-bit
(1 row)

Первый запрос

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

db=> EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT t.last_updated FROM table t WHERE (t.has_symbol = 1 AND t.package_id = 6360);
                                                                                     QUERY PLAN                                                                                      
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using table_has_symbol_package_id_index on public.table t  (cost=0.11..13720.30 rows=7685 width=8) (actual time=0.042..0.043 rows=1 loops=1)
   Output: last_updated
   Index Cond: ((t.has_symbol = 1) AND (t.package_id = 6360))
   Buffers: shared hit=3 read=2
   I/O Timings: read=0.018
 Planning time: 0.154 ms
 Execution time: 0.063 ms
(7 rows)

Второй запрос

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

db=> EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT t.last_updated FROM table t WHERE (t.has_symbol = 1 AND t.package_id = 6360) ORDER BY t.id DESC LIMIT 10;
                                                                                       QUERY PLAN                                                                                       
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.09..1544.39 rows=10 width=12) (actual time=202429.114..402959.158 rows=1 loops=1)
   Output: last_updated, id
   Buffers: shared hit=2041907 read=3757798 dirtied=18850 written=19
   I/O Timings: read=185860.338 write=0.939
   ->  Index Scan using table_id_desc_idx on public.table t  (cost=0.09..1186795.52 rows=7685 width=12) (actual time=202429.112..402959.155 rows=1 loops=1)
         Output: last_updated, id
         Filter: ((t.has_symbol = 1) AND (t.package_id = 6360))
         Rows Removed by Filter: 5725297
         Buffers: shared hit=2041907 read=3757798 dirtied=18850 written=19
         I/O Timings: read=185860.338 write=0.939
 Planning time: 0.247 ms
 Execution time: 402959.189 ms
(12 rows)

Третий запрос

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

db=> EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT t.last_updated FROM table t WHERE (t.has_symbol = 1 AND t.package_id = 6360) ORDER BY t.id+0 DESC LIMIT 10;
                                                                                            QUERY PLAN                                                                                       >
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
 Limit  (cost=13757.35..13757.36 rows=10 width=12) (actual time=0.082..0.083 rows=1 loops=1)
   Output: last_updated, ((id + 0))
   Buffers: shared hit=6 read=2
   I/O Timings: read=0.017
   ->  Sort  (cost=13757.35..13761.20 rows=7685 width=12) (actual time=0.082..0.083 rows=1 loops=1)
         Output: last_updated, ((id + 0))
         Sort Key: ((t.id + 0)) DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=6 read=2
         I/O Timings: read=0.017
         ->  Index Scan using table_has_symbol_package_id_index on public.table t  (cost=0.11..13724.14 rows=7685 width=12) (actual time=0.046..0.046 rows=1 loop>
               Output: last_updated, (id + 0)
               Index Cond: ((t.has_symbol = 1) AND (t.package_id = 6360))
               Buffers: shared hit=3 read=2
               I/O Timings: read=0.017
 Planning time: 0.222 ms
 Execution time: 0.113 ms
(17 rows)

Четвёртый запрос

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

db=> EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT t.last_updated FROM table t;
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on public.table t  (cost=0.00..512022.71 rows=5966235 width=8) (actual time=0.032..49402.672 rows=5725307 loops=1)
   Output: last_updated
   Buffers: shared hit=73815 read=420309 dirtied=373 written=51
   I/O Timings: read=10446.404 write=1.344
 Planning time: 0.088 ms
 Execution time: 52396.464 ms
(6 rows)

...

Рейтинг:

0 / 0

28.05.2021, 09:20

| Ответить | Цитировать | Написать

Некорректный индекс при использовании ORDER BY c LIMIT n

#40073881

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

bff7755a,

У вас все упирается в самый первый запрос
(cost=0.11..13720.30 rows=7685 width=8) (actual time=0.042..0.043 rows=1 loops=1)

база думает что таких строк 7685 а в реалности их одна поэтому и выбирает план перебора по id.
Т.е. у расходится оценка "Селективность по первому индексу довольно хорошая" с тем что база думает.

Учитывая что 9.6 база древняя как незнаю кто и будет EOL осенью то вариантов как это поправить на самом деле не много.
Но попробуем.

Покажите что показывает

Код: sql

1.
2.
3.
4.
5.

explain analyze select from table t WHERE (t.has_symbol = 1);

explain analyze select from table t WHERE (t.package_id = 6360);

explain analyze select distinct package_id from table t;

Попробуем правильно статистику подкрутить.

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

...

Рейтинг:

0 / 0

28.05.2021, 16:57

| Ответить | Цитировать | Написать

Некорректный индекс при использовании ORDER BY c LIMIT n

#40073912

bff7755a

Гость

Максим, спасибо, что решили помочь. Планы запросов ниже.

Запрос 1

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.

db=> explain analyze select from table t WHERE (t.has_symbol = 1);
                                                                                            QUERY PLAN                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using table_has_symbol_package_id_index on table t  (cost=0.11..195476.49 rows=2305552 width=0) (actual time=10.656..518602.926 rows=2201411 loops=1
   Index Cond: (has_symbol = 1)
   Heap Fetches: 522949
 Planning time: 1.727 ms
 Execution time: 518782.052 ms
(5 rows)

Запрос 2

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

db=> explain analyze select * from table t WHERE (t.package_id = 6360);
                                                                          QUERY PLAN                                                                          
-----------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on table t  (cost=1070.94..36258.26 rows=19887 width=634) (actual time=2325.114..26009.840 rows=18128 loops=1)
   Recheck Cond: (package_id = 6360)
   Heap Blocks: exact=16853
   ->  Bitmap Index Scan on table_package_id_index  (cost=0.00..1069.94 rows=19887 width=0) (actual time=2317.463..2317.463 rows=18130 loops=1)
         Index Cond: (package_id = 6360)
 Planning time: 24.091 ms
 Execution time: 26012.256 ms
(7 rows)

Запрос 3

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.

db=> explain analyze select distinct package_id from table t;
                                                                                          QUERY PLAN                                                                                          
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=0.11..394707.51 rows=5768 width=4) (actual time=0.017..1155383.194 rows=75357 loops=1)
   ->  Index Only Scan using table_package_id_index on table t  (cost=0.11..391724.40 rows=5966235 width=4) (actual time=0.017..1154684.383 rows=5725655 loops=1)
         Heap Fetches: 1304152
 Planning time: 0.145 ms
 Execution time: 1155390.305 ms
(5 rows)

Также я пробовал создать индекс с обратным порядком столбцов, т.е. (package_id, has_symbol) (т.к. селективность по package_id лучше). Он всё равно не использовался.

...

Рейтинг:

0 / 0

28.05.2021, 18:25

| Ответить | Цитировать | Написать

Некорректный индекс при использовании ORDER BY c LIMIT n

#40073935

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

bff7755a,

Все оценки нормальные...
т.е. и количество строк с t.has_symbol = 1 база корректно оценивает
и количество строк с t.package_id = 6360
тоже...
а вот количество строк с (t.has_symbol = 1 AND t.package_id = 6360) оценка базы отличается от реальности в 7685 раз.
Отсюда и кривой план.
Отсюда и вывод что данные у вас не равномерно статистически распределены... а значит нормальные планы можно только угадать.

Смотрите:

Код: plaintext

1.
2.

у вас 2305552 строк из 5966235 (т.е. около 1/3) имеют has_symbol = 1
количество строк с package_id = 6360 около 18130
база вполне логично считает что у вас  (t.has_symbol = 1 AND t.package_id = 6360) где то 1/3 от 18130 и будет т.е. 6000-7000

А оно в реальности не так - такая строка только одна.

И нормально никакими стат моделями такие ситуации на 9.6 не лечатся.

Вам нужна тут версия не младше 12 версии где появилось https://www.postgresql.org/docs/13/sql-createstatistics.html
Currently supported kinds are ndistinct, which enables n-distinct statistics, dependencies, which enables functional dependency statistics, and mcv which enables most-common values lists.
которая бы скорее всего эту проблему решило.

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

PS: а чтобы это реально быстро и всегда работало сделайте индекс по (package_id, has_symbol, t_id)
или если у вас всегда в этом запросе has_symbol = 1 то (package_id, t_id) where (has_symbol = 1).
Тогда базе не придется угадывать какой из 2х более менее подходящих индексов лучше.

PPS: было бы интересно просто для любопытства взглянуть на seq_page_cost/random_page_cost/effective_cache_size/default_statistics_target настройки у вас в базе.
уж больно чудный план для select distinct package_id у вас база выбрала с моей т.з. (особенно учитывая сильно ненулевой heap fetches).

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

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

...

Рейтинг:

0 / 0

28.05.2021, 19:48

| Ответить | Цитировать | Написать

Некорректный индекс при использовании ORDER BY c LIMIT n

#40073952

bff7755a

Гость

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.

db=> show random_page_cost ;
 random_page_cost 
------------------
 2
(1 row)

db=> show seq_page_cost ;
 seq_page_cost 
---------------
 1
(1 row)

db=> show effective_cache_size ;
 effective_cache_size 
----------------------
 10980000kB
(1 row)

db=> show default_statistics_target ;
 default_statistics_target 
---------------------------
 100
(1 row)

...

Рейтинг:

0 / 0

28.05.2021, 21:25

| Ответить | Цитировать | Написать

Некорректный индекс при использовании ORDER BY c LIMIT n

#40074233

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

bff7755a,

Это не касается вашей проблемы но случайно show enable_seqscan; не в off стоит?
При random_page_cost=2 выбор IOS вместо seq_scan+sort выглядит для меня странным (для distinct запроса).

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

...

Рейтинг:

0 / 0

31.05.2021, 05:36

| Ответить | Цитировать | Написать

Некорректный индекс при использовании ORDER BY c LIMIT n

#40074278

bff7755a

Гость

авторЭто не касается вашей проблемы но случайно show enable_seqscan; не в off стоит?
Нет, разумеется, он включен )

...

Рейтинг:

0 / 0

31.05.2021, 09:45

| Ответить | Цитировать | Написать

9 сообщений из 9, страница 1 из 1

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Некорректный индекс при использовании ORDER BY c LIMIT n

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

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

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&msg=40073681&tid=1994012]:	0ms
get settings:	12ms
get forum list:	16ms
check forum access:	4ms
check topic access:	4ms
track hit:	35ms
get topic data:	13ms
get forum data:	3ms
get page messages:	55ms
get tp. blocked users:	2ms
others:	256ms

total:	400ms