Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 Некорректный индекс при использовании ORDER BY c LIMIT n  [new]
bff7755a
Member

Откуда: Deutschland
Сообщений: 102
Есть таблица, на которой висит несколько индексов, в том числе:

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 планировщик выбирает второй индекс и запрос выполняется минуты, вместо нескольких миллисекунд. В качестве временного решения помогает следующее:

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 оставил снаружи. Это помогает, однако хочется понимать причину происходящего. В рассылке нашёл письмо с описанием этой проблемы, однако это было в 2005 году и хочется верить что с тех пор планировщик стал умнее.
27 май 21, 20:36    [22328213]     Ответить | Цитировать Сообщить модератору
 Re: Некорректный индекс при использовании ORDER BY c LIMIT n  [new]
Maxim Boguk
Member

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

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

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


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


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


SELECT * FROM table t;


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

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

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
27 май 21, 22:58    [22328260]     Ответить | Цитировать Сообщить модератору
 Re: Некорректный индекс при использовании ORDER BY c LIMIT n  [new]
bff7755a
Member

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

Версия
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)

Первый запрос
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)

Второй запрос
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)

Третий запрос
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)

Четвёртый запрос
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)
28 май 21, 09:20    [22328326]     Ответить | Цитировать Сообщить модератору
 Re: Некорректный индекс при использовании ORDER BY c LIMIT n  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4785
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 осенью то вариантов как это поправить на самом деле не много.
Но попробуем.

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

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

Сообщение было отредактировано: 28 май 21, 16:50
28 май 21, 16:57    [22328568]     Ответить | Цитировать Сообщить модератору
 Re: Некорректный индекс при использовании ORDER BY c LIMIT n  [new]
bff7755a
Member

Откуда: Deutschland
Сообщений: 102
Максим, спасибо, что решили помочь. Планы запросов ниже.

Запрос 1
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
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
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 лучше). Он всё равно не использовался.
28 май 21, 18:25    [22328610]     Ответить | Цитировать Сообщить модератору
 Re: Некорректный индекс при использовании ORDER BY c LIMIT n  [new]
Maxim Boguk
Member

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

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

Смотрите:
у вас 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
28 май 21, 19:48    [22328654]     Ответить | Цитировать Сообщить модератору
 Re: Некорректный индекс при использовании ORDER BY c LIMIT n  [new]
bff7755a
Member

Откуда: Deutschland
Сообщений: 102
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)
28 май 21, 21:25    [22328679]     Ответить | Цитировать Сообщить модератору
 Re: Некорректный индекс при использовании ORDER BY c LIMIT n  [new]
Maxim Boguk
Member

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

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

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
31 май 21, 05:36    [22329083]     Ответить | Цитировать Сообщить модератору
 Re: Некорректный индекс при использовании ORDER BY c LIMIT n  [new]
bff7755a
Member

Откуда: Deutschland
Сообщений: 102
автор
Это не касается вашей проблемы но случайно show enable_seqscan; не в off стоит?

Нет, разумеется, он включен )
31 май 21, 09:45    [22329142]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить