Гость
Войти | Профиль | Очистить
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Некорректный индекс при использовании ORDER BY c LIMIT n / 9 сообщений из 9, страница 1 из 1
27.05.2021, 20:36
    #40073656
bff7755a
bff7755a
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Некорректный индекс при использовании ORDER BY c LIMIT n
Есть таблица, на которой висит несколько индексов, в том числе:

Код: 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, 22:58
    #40073681
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Некорректный индекс при использовании ORDER BY c LIMIT n
bff7755a,

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

Код: sql
1.
SELECT * FROM table t WHERE (t.has_symbol = 1 AND t.package_id = 6360);



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



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



Код: sql
1.
SELECT * FROM table t;



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

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

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.05.2021, 09:20
    #40073725
bff7755a
bff7755a
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Некорректный индекс при использовании ORDER BY c LIMIT n
В планах заменил список всех столбцов на один из тех, которых нет в индексах, чтобы сократить вывод. Версию забыл, прошу прощения.

Версия
Код: 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, 16:57
    #40073881
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Некорректный индекс при использовании ORDER BY c LIMIT n
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, 18:25
    #40073912
bff7755a
bff7755a
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Некорректный индекс при использовании ORDER BY c LIMIT n
Максим, спасибо, что решили помочь. Планы запросов ниже.

Запрос 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, 19:48
    #40073935
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Некорректный индекс при использовании ORDER BY c LIMIT n
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, 21:25
    #40073952
bff7755a
bff7755a
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Некорректный индекс при использовании ORDER BY c LIMIT n
Код: 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
| Ответить | Цитировать | Написать  
31.05.2021, 05:36
    #40074233
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Некорректный индекс при использовании ORDER BY c LIMIT n
bff7755a,

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

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.05.2021, 09:45
    #40074278
bff7755a
bff7755a
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Некорректный индекс при использовании ORDER BY c LIMIT n
авторЭто не касается вашей проблемы но случайно show enable_seqscan; не в off стоит?
Нет, разумеется, он включен )
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Некорректный индекс при использовании ORDER BY c LIMIT n / 9 сообщений из 9, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 135ms
Закрыть
start [/forum/topic.php?fid=53&mobile=1&tid=1994012]:
 0ms
get settings:
 9ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 30ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 48ms
get tp. blocked users:
 1ms
others: 12ms
total:  135ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]