медленный index scan + limit / PostgreSQL

ReSQL.ru

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

Гость

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

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

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

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

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / медленный index scan + limit

32 сообщений из 32, показаны все 2 страниц

все

медленный index scan + limit

#39568283

kukurzik

Участник

Сообщения: 26

Рейтинг: 0 / 0

Имеем часто обновляемую таблицу insert/delete, куда пишется контент для клиентов (~500 строк),
при получении данных клиентом соответствующие кортежи удаляются.
Однако порой клиент появившись не торопится забирать данные.

Когда же он наконец, начинает радостно кормится, возникает у ПЖ крайняя степень натуги.

Приближенная схема таблицы:
id primary key,
content text,
client_number int index,
creation_date timestamp.

Данные развернуты из дампа.
Postgres (9.6, 10).
Таблица ~ 1.3GB

Код: sql

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

explain (analyze, buffers)
SELECT id
FROM   transfers
WHERE  client_number = ?
ORDER BY id, creation_date
LIMIT  50

--селективность ~ 8.5% 

"Limit  (cost=210155.42..210155.54 rows=50 width=16) (actual time=463.499..463.504 rows=50 loops=1)"
"  Buffers: shared hit=160334"
"  ->  Sort  (cost=210155.42..212059.22 rows=761521 width=16) (actual time=463.497..463.499 rows=50 loops=1)"
"        Sort Key: id, creation_date"
"        Sort Method: top-N heapsort  Memory: 29kB"
"        Buffers: shared hit=160334"
"        ->  Bitmap Heap Scan on transfers  (cost=14258.22..184858.24 rows=761521 width=16) (actual time=138.482..397.160 rows=776612 loops=1)"
"              Recheck Cond: (client_number = 812)"
"              Heap Blocks: exact=158209"
"              Buffers: shared hit=160334"
"              ->  Bitmap Index Scan on transfer_client_idx  (cost=0.00..14067.84 rows=761521 width=0) (actual time=106.154..106.154 rows=776612 loops=1)"
"                    Index Cond: (client_number = 812)"
"                    Buffers: shared hit=2125"
"Planning time: 0.251 ms"
"Execution time: 465.592 ms"

По мере разгребания время выполнения равномерно уменьшается.

Код: sql

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

--~ 1%
"Limit  (cost=15874.78..15874.91 rows=50 width=16) (actual time=113.584..113.588 rows=50 loops=1)"
"  Buffers: shared hit=47145"
"  ->  Sort  (cost=15874.78..15886.06 rows=4512 width=16) (actual time=113.582..113.583 rows=50 loops=1)"
"        Sort Key: id, creation_date"
"        Sort Method: top-N heapsort  Memory: 28kB"
"        Buffers: shared hit=47145"
"        ->  Bitmap Heap Scan on transfers  (cost=87.40..15724.90 rows=4512 width=16) (actual time=52.557..104.927 rows=85922 loops=1)"
"              Recheck Cond: (client_number = 199)"
"              Heap Blocks: exact=46908"
"              Buffers: shared hit=47145"
"              ->  Bitmap Index Scan on transfer_client_idx  (cost=0.00..86.28 rows=4512 width=0) (actual time=30.941..30.941 rows=85922 loops=1)"
"                    Index Cond: (client_number = 199)"
"                    Buffers: shared hit=237"
"Planning time: 0.253 ms"
"Execution time: 114.065 ms"

В штатном режиме все очень даже годно

Код: sql

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

"Limit  (cost=15874.78..15874.91 rows=50 width=16) (actual time=0.690..0.700 rows=50 loops=1)"
"  Buffers: shared hit=176"
"  ->  Sort  (cost=15874.78..15886.06 rows=4512 width=16) (actual time=0.689..0.694 rows=50 loops=1)"
"        Sort Key: id, creation_date"
"        Sort Method: top-N heapsort  Memory: 27kB"
"        Buffers: shared hit=176"
"        ->  Bitmap Heap Scan on transfers  (cost=87.40..15724.90 rows=4512 width=16) (actual time=0.165..0.582 rows=173 loops=1)"
"              Recheck Cond: (client_number = 60)"
"              Heap Blocks: exact=173"
"              Buffers: shared hit=176"
"              ->  Bitmap Index Scan on transfer_client_idx  (cost=0.00..86.28 rows=4512 width=0) (actual time=0.093..0.093 rows=173 loops=1)"
"                    Index Cond: (client_number = 60)"
"                    Buffers: shared hit=3"
"Planning time: 0.249 ms"
"Execution time: 0.797 ms"

Хочется, чтобы ПЖ не слишком тужился при разгребании.
Меняем поля сортировки...и... имеем дикую деградацию в обратном порядке.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.

explain (analyze, buffers)
SELECT id
FROM   transfers
WHERE  client_number = ?
ORDER BY id 
LIMIT  50

-- ~8.6% 
"Limit  (cost=0.44..41.44 rows=50 width=8) (actual time=1.201..1.750 rows=50 loops=1)"
"  Buffers: shared hit=955"
"  ->  Index Scan using transfers_pk on transfers  (cost=0.44..624513.38 rows=761521 width=8) (actual time=1.198..1.740 rows=50 loops=1)"
"        Filter: (client_number = 812)"
"        Rows Removed by Filter: 903"
"        Buffers: shared hit=955"
"Planning time: 0.254 ms"
"Execution time: 1.800 ms"


-- ~8.5%
-"Limit  (cost=0.44..40.91 rows=50 width=8) (actual time=55.040..55.184 rows=50 loops=1)"
"  Buffers: shared hit=50444"
"  ->  Index Scan using transfers_pk on transfers  (cost=0.44..624513.38 rows=771541 width=8) (actual time=55.039..55.180 rows=50 loops=1)"
"        Filter: (client_number = 811)"
"        Rows Removed by Filter: 60298"
"        Buffers: shared hit=50444"
"Planning time: 0.243 ms"
"Execution time: 55.222 ms"

--~ 1%
"Limit  (cost=0.44..6921.01 rows=50 width=8) (actual time=3251.508..3251.818 rows=50 loops=1)"
"  Buffers: shared hit=4173657"
"  ->  Index Scan using transfers_pk on transfers  (cost=0.44..624513.38 rows=4512 width=8) (actual time=3251.507..3251.816 rows=50 loops=1)"
"        Filter: (client_number = 199)"
"        Rows Removed by Filter: 6896044"
"        Buffers: shared hit=4173657"
"Planning time: 0.243 ms"
"Execution time: 3251.856 ms"

--~0.001% 
"Limit  (cost=0.44..6921.01 rows=50 width=8) (actual time=4013.534..4014.331 rows=50 loops=1)"
"  Buffers: shared hit=4934168"
"  ->  Index Scan using transfers_pk on transfers  (cost=0.44..624513.38 rows=4512 width=8) (actual time=4013.533..4014.327 rows=50 loops=1)"
"        Filter: (client_number = 60)"
"        Rows Removed by Filter: 8817426"
"        Buffers: shared hit=4934168"
"Planning time: 0.243 ms"
"Execution time: 4014.368 ms"

в последнем случае ПЖ кормится ~ 38GB.

Имеются ли способы лечения странного поведения?

...

Рейтинг:

0 / 0

12.12.2017, 13:16

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

медленный index scan + limit

#39568288

Maxim Boguk

Участник

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

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

Рейтинг: 0 / 0

kukurzik,

Сделать подходящие под эти запросы индексы естественно.

client_number, id, creation_date

он покроет оба варианта запросов

...

Рейтинг:

0 / 0

12.12.2017, 13:20

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

медленный index scan + limit

#39568315

kukurzik

Участник

Сообщения: 26

Рейтинг: 0 / 0

Maxim Boguk,

лопух! слона и не приметил.

благодарю премного.

//и все же.. чтение 38 Gb при размере таблицы в 30 раз меньше,
откуда здесь ноги растут?

...

Рейтинг:

0 / 0

12.12.2017, 13:42

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

медленный index scan + limit

#39568325

vyegorov

Участник

Откуда: Баньоло-ин-Пьяно

Сообщения: 1 237

Рейтинг: 0 / 0

kukurzikоткуда здесь ноги растут?
для второго случая обратите внимание на то, какой индекс используется.

...

Рейтинг:

0 / 0

12.12.2017, 13:51

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

медленный index scan + limit

#39568355

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

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

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

а так -- да -- тс-у достаточно составного индекса.

я бы дату выбросил совсем -- порядок и так полностью определяется парой ["что--то","ПК"]. далее уже перемешивание воздуха и тавтология.

...

Рейтинг:

0 / 0

12.12.2017, 14:25

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

медленный index scan + limit

#39568379

Maxim Boguk

Участник

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

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

Рейтинг: 0 / 0

[quot qwwq]vyegorovя бы дату выбросил совсем -- порядок и так полностью определяется парой ["что--то","ПК"]. далее уже перемешивание воздуха и тавтология.

Это правда... надо дату из индекса убрать и из запроса убрать.

...

Рейтинг:

0 / 0

12.12.2017, 15:07

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

медленный index scan + limit

#39568388

kukurzik

Участник

Сообщения: 26

Рейтинг: 0 / 0

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

Что касается статистики, то она свежа.
никакого блоата и дохлых записей, ибо замеры на базе из бэкапа развернутой.
Никаких экзотических правок в конфиги не вносилось.

...

Рейтинг:

0 / 0

12.12.2017, 15:28

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

медленный index scan + limit

#39568416

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

kukurzik,

сделайте order by id+0 , чтобы пж не сваливался на выборку по ид.
пока/если составной индекс не сделали. пусть по клиенту шукает.

//как показывает https://habrahabr.ru/post/343686/ -- составной не панацея. пж грязь найдёт. можно и тут индекс сделать сразу по (клиент,(ид+0)) , с таким же ордербаем в запросе -- чтобы избежать того, что по сцылке.

(!)но свои енейблы и косты могли бы и процитировать. для подумать.

...

Рейтинг:

0 / 0

12.12.2017, 15:59

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

медленный index scan + limit

#39568447

kukurzik

Участник

Сообщения: 26

Рейтинг: 0 / 0

qwwq,

все параметры кроме шаред буфферс (2GB) и ворк мем(16MB) по дефолту
# - Planner Method Configuration -

#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_indexonlyscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on

# - Planner Cost Constants -

#seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
#parallel_tuple_cost = 0.1 # same scale as above
#parallel_setup_cost = 1000.0 # same scale as above
#min_parallel_table_scan_size = 8MB
#min_parallel_index_scan_size = 512kB
#effective_cache_size = 4GB

...

Рейтинг:

0 / 0

12.12.2017, 16:31

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

медленный index scan + limit

#39568504

kukurzik

Участник

Сообщения: 26

Рейтинг: 0 / 0

qwwq,

авторможно и тут индекс сделать сразу по (клиент,(ид+0)) , с таким же ордербаем в запросе

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

индекс по (id+0) исправил это, пока противоправных действий не обнаружил.
однако, так ли необходим ордербай по (клиент,(ид+0),
кажется, что ордербай просто по (ид+0) справляется.

...

Рейтинг:

0 / 0

12.12.2017, 17:18

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

медленный index scan + limit

#39568530

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

kukurzikqwwq,

авторможно и тут индекс сделать сразу по (клиент,(ид+0)) , с таким же ордербаем в запросе

да, добавил чуток грязи и пж снова стал первичный ключ терзать.
печалька.
хотя надо бы вдумчиво посмотреть на обстоятельства
и на статистику -- тут Alexius её handjob--тюнинг асилил кажется, для больших табличек

kukurzikиндекс по (id+0) исправил это, пока противоправных действий не обнаружил.
грустно это. хотелось бы без оного. могабыть что--то не учли ? нет ?

одербай конечно при фиксе по пре--фиксу не нужен. тут пж пока сам справляется.

...

Рейтинг:

0 / 0

12.12.2017, 17:34

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

медленный index scan + limit

#39568903

kukurzik

Участник

Сообщения: 26

Рейтинг: 0 / 0

qwwq,

да, добавил чуток грязи и пж снова стал первичный ключ терзать.
хотя надо бы вдумчиво посмотреть на обстоятельства
и на статистику -- тут Alexius её handjob--тюнинг асилил кажется, для больших табличек

ошибся с высказыванием, грязь тут на 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.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.

explain (analyze, buffers)
SELECT *
FROM transfers
WHERE client_number = ?
ORDER BY    id
limit 50

-- 0.01 %
"Limit  (cost=0.43..193.65 rows=50 width=659) (actual time=0.050..0.180 rows=50 loops=1)"
"  Buffers: shared hit=53"
"  ->  Index Scan using transfers_client_number_id_idx on transfers  (cost=0.43..5557.34 rows=1438 width=659) (actual time=0.048..0.171 rows=50 loops=1)"
"        Index Cond: (client_number = 60)"
"        Buffers: shared hit=53"
"Planning time: 0.299 ms"
"Execution time: 0.255 ms"

-- ~2.5%
"Limit  (cost=0.43..116.76 rows=50 width=659) (actual time=0.030..0.109 rows=50 loops=1)"
"  Buffers: shared hit=53"
"  ->  Index Scan using transfers_client_number_id_idx on transfers  (cost=0.43..493100.92 rows=211947 width=659) (actual time=0.029..0.104 rows=50 loops=1)"
"        Index Cond: (client_number = 831)"
"        Buffers: shared hit=53"
"Planning time: 0.178 ms"
"Execution time: 0.142 ms"

-- ~6.5% и праздник закончился
"Limit  (cost=0.43..51.22 rows=50 width=659) (actual time=450.099..450.271 rows=50 loops=1)"
"  Buffers: shared hit=692852"
"  ->  Index Scan using transfers_pkey on transfers  (cost=0.43..604537.41 rows=595121 width=659) (actual time=450.097..450.266 rows=50 loops=1)"
"        Filter: (client_number = 110)"
"        Rows Removed by Filter: 951480"
"        Buffers: shared hit=692852"
"Planning time: 0.388 ms"
"Execution time: 450.332 ms"

Так что, спасёт правильный строй либо хакнутый индекс (клент, ид+0),
либо (клиен, ид, дата) с сортировкой по (ид, дате)

...

Рейтинг:

0 / 0

13.12.2017, 12:08

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

медленный index scan + limit

#39568918

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

kukurzik,

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

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

и как оно служит в очистке ? (зачем в 10--ке возможность сбора статистики по корреляциям -- если оно так легко перепрыгивает на независимую модель закрывая глаза на наличие безусловного запрета к применению оной модели).

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

...

Рейтинг:

0 / 0

13.12.2017, 12:38

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

медленный index scan + limit

#39568989

Maxim Boguk

Участник

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

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

Рейтинг: 0 / 0

kukurzik-- ~6.5% и праздник закончился
"Limit (cost=0.43..51.22 rows=50 width=659) (actual time=450.099..450.271 rows=50 loops=1)"
" Buffers: shared hit=692852"
" -> Index Scan using transfers_pkey on transfers (cost=0.43..604537.41 rows=595121 width=659) (actual time=450.097..450.266 rows=50 loops=1)"
" Filter: (client_number = 110)"
" Rows Removed by Filter: 951480"
" Buffers: shared hit=692852"
"Planning time: 0.388 ms"
"Execution time: 450.332 ms"

Hm... интересные дела творятся...
а если сделать вот что:

show default_statistics_target;
на всякий случай так же
analyze transfers;

а дальше
select count(*) from transfers;
select count(*) from transfers where client_number = 110;
select max(id), min(id) from transfers;
select max(id), min(id) from transfers where client_number = 110;
explain analyze select * from transfers where client_number = 110;

если не сложно приведите результаты всего этого...

так же на 10тке сделайте
create statistics trasfers_client_number_id_stat on client_number, id from transfers;
analyze transfers;
explain (analyze, buffers)
SELECT id
FROM transfers
WHERE client_number = 110
ORDER BY id
LIMIT 50;

похожу у вас client_number и id сильно скоррелированы что очень странно.

...

Рейтинг:

0 / 0

13.12.2017, 14:00

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

медленный index scan + limit

#39569014

kukurzik

Участник

Сообщения: 26

Рейтинг: 0 / 0

Maxim Boguk,

Код: sql

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

show default_statistics_target;
100

select count(*) from transfers;
7814572

select count(*) from transfers where client_number = 110;
593082

select max(id), min(id) from transfers;
6743817103;6284714398

select max(id), min(id) 
from transfers where client_number = 110;
6743817004;6388989057

explain analyze select * from transfers where client_number = 110
"Bitmap Heap Scan on transfers  (cost=14260.85..178031.79 rows=578635 width=659) (actual time=123.726..293.868 rows=593082 loops=1)"
"  Recheck Cond: (client_number = 110)"
"  Heap Blocks: exact=152245"
"  ->  Bitmap Index Scan on transfers_client_number_id_idx  (cost=0.00..14116.20 rows=578635 width=0) (actual time=91.655..91.655 rows=593082 loops=1)"
"        Index Cond: (client_number = 110)"
"Planning time: 0.213 ms"
"Execution time: 302.867 ms"

...

Рейтинг:

0 / 0

13.12.2017, 14:25

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

медленный index scan + limit

#39569017

kukurzik

Участник

Сообщения: 26

Рейтинг: 0 / 0

Maxim Boguk,

Сделал на 10-ке взлетело, но надолго ли полета этого хватит?

однако ж все равно еще долго иметь дело на проде с 9.4

Код: sql

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

create statistics trasfers_client_number_id_stat on client_number, id from transfers;
analyze transfers;
explain (analyze, buffers)
SELECT id
FROM transfers
WHERE client_number = 110
ORDER BY id 
LIMIT 50;

"Limit  (cost=0.43..2.15 rows=50 width=8) (actual time=0.044..0.068 rows=50 loops=1)"
"  Buffers: shared hit=6"
"  ->  Index Only Scan using transfers_client_number_id_idx on transfers  (cost=0.43..19091.01 rows=554890 width=8) (actual time=0.042..0.060 rows=50 loops=1)"
"        Index Cond: (client_number = 110)"
"        Heap Fetches: 0"
"        Buffers: shared hit=6"
"Planning time: 0.253 ms"
"Execution time: 0.115 ms"

...

Рейтинг:

0 / 0

13.12.2017, 14:29

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

медленный index scan + limit

#39569044

Павел Лузанов

Участник

Сообщения: 866

Рейтинг: 0 / 0

kukurzik,

На 9.4 сделайте запрос с SELECT id, а не SELECT *
Видите, на 10-ке уже INDEX ONLY SCAN подцепился, может и 9.4 планировщик посчитает это дешевле.

...

Рейтинг:

0 / 0

13.12.2017, 15:02

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

медленный index scan + limit

#39569101

kukurzik

Участник

Сообщения: 26

Рейтинг: 0 / 0

Радость за 10 была преждевременной.

Не тот запрос подсунули:

SELECT id
FROM transfers
WHERE client_number = 110
ORDER BY id
LIMIT 50;

вместо

SELECT *
FROM transfers
WHERE client_number = 110
ORDER BY id
LIMIT 50;

а именно он и нужен,
и наблюдаем прежнее соплежуйство.

Код: sql

1.
2.
3.
4.
5.
6.

"Limit  (cost=0.43..50.32 rows=50 width=659) (actual time=478.070..478.267 rows=50 loops=1)"
"  ->  Index Scan using transfers_pkey on transfers  (cost=0.43..597524.84 rows=598814 width=659) (actual time=478.068..478.264 rows=50 loops=1)"
"        Filter: (client_number = 110)"
"        Rows Removed by Filter: 951480"
"Planning time: 0.289 ms"
"Execution time: 478.314 ms"

на 9.4

SELECT id
FROM transfers
WHERE client_number = 110
ORDER BY id
LIMIT 50;

тоже индекс онли скан хорошо прожевывает.

оно, конечно, и со * легким вывертом встает на ноги,
но как же хочется без акробатики прожить.

Код: sql

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

explain analyze
SELECT * 
FROM transfers  
WHERE id in(
	SELECT id
	FROM transfers
	WHERE client_number = 110
	ORDER BY id 
	LIMIT 50)

"Nested Loop  (cost=3.21..425.78 rows=50 width=659) (actual time=1.339..1.756 rows=50 loops=1)"
"  ->  HashAggregate  (cost=2.78..3.28 rows=50 width=8) (actual time=1.297..1.316 rows=50 loops=1)"
"        Group Key: transfers_1.id"
"        ->  Limit  (cost=0.43..2.15 rows=50 width=8) (actual time=1.233..1.258 rows=50 loops=1)"
"              ->  Index Only Scan using transfers_client_number_id_idx on transfers transfers_1  (cost=0.43..20387.31 rows=592393 width=8) (actual time=1.231..1.252 rows=50 loops=1)"
"                    Index Cond: (client_number = 110)"
"                    Heap Fetches: 0"
"  ->  Index Scan using transfers_pkey on transfers  (cost=0.43..8.45 rows=1 width=659) (actual time=0.008..0.008 rows=1 loops=50)"
"        Index Cond: (id = transfers_1.id)"
"Planning time: 0.705 ms"
"Execution time: 0.867 ms"

...

Рейтинг:

0 / 0

13.12.2017, 15:53

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

медленный index scan + limit

#39569107

Maxim Boguk

Участник

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

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

Рейтинг: 0 / 0

kukurzik,

Hm

а что реально дает запрос

SELECT id
FROM transfers
WHERE client_number = 110
ORDER BY id
LIMIT 50;

какие 50 цифр? и нет ли ситуации что client_id крайне неравномерно распределены по пространству id ?

...

Рейтинг:

0 / 0

13.12.2017, 16:02

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

медленный index scan + limit

#39569114

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

kukurzik,

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

т.ч. копите хитрые отвертки с левым винтом -- они вам ещё пригодятся.

...

Рейтинг:

0 / 0

13.12.2017, 16:04

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

медленный index scan + limit

#39569120

kukurzik

Участник

Сообщения: 26

Рейтинг: 0 / 0

Maxim Boguk,

6388990746
6388991673
6388991982
6388992600
6388993218
6388993836
6388994454
6388995072
6388995674
6388995999
6388996926
6388997544
6388998162
6388998780
6388999398
6389000016
6389000634
6389001252
6389001870
6389002488
6389003106
6389003724
6389004033
6389005423
6389005578
6389006196
6389006814
6389007123
6389007432
6389008359
6389008668
6389009286
6389009904
6389010522
6389011449
6389012067
6389012799
6389013612
6389014230
6389015036
6389015635
6389015775
6389016084
6389016393
6389017011
6389017614
6389018247

и нет ли ситуации что client_id крайне неравномерно распределены по пространству id ?

всё должно быть примерно равномерно размазано.

...

Рейтинг:

0 / 0

13.12.2017, 16:12

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

медленный index scan + limit

#39569121

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Maxim Boguk,

там классическая логическая ошибка -- применение допущения в ситуации, когда она опровергается наличным планом по подходящему индексу
см:
https://habrahabr.ru/post/343686/#comment_10557452

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

...

Рейтинг:

0 / 0

13.12.2017, 16:13

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

медленный index scan + limit

#39569904

Dany305

Участник

Сообщения: 103

Рейтинг: 0 / 0

kukurzikИмеем часто обновляемую таблицу insert/delete, куда пишется контент для клиентов (~500 строк),
при получении данных клиентом соответствующие кортежи удаляются.
Однако порой клиент появившись не торопится забирать данные.

Когда же он наконец, начинает радостно кормится, возникает у ПЖ крайняя степень натуги.

Хочется, чтобы ПЖ не слишком тужился при разгребании.
...
Меняем поля сортировки...и... имеем дикую деградацию в обратном порядке.

Код: sql

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

explain (analyze, buffers)
SELECT id
FROM   transfers
WHERE  client_number = ?
ORDER BY id 
LIMIT  50

-- ~8.6% 
"Limit  (cost=0.44..41.44 rows=50 width=8) (actual time=1.201..1.750 rows=50 loops=1)"
"  Buffers: shared hit=955"
"  ->  Index Scan using transfers_pk on transfers  (cost=0.44..624513.38 rows=761521 width=8) (actual time=1.198..1.740 rows=50 loops=1)"
"        Filter: (client_number = 812)"
"        Rows Removed by Filter: 903"
"        Buffers: shared hit=955"
"Planning time: 0.254 ms"
"Execution time: 1.800 ms"

вот тут похоже на проблему

(cost=0.44..624513.38 rows=761521 width=8) (actual time=1.198..1.740 rows=50 loops=1)"

планировщик оценивает число записей в таблице по reltuples из pg_class,
прикол в том, что vacuum пишет туда "Live Tuples" + "Dead Tuples", а analyze "Live Tuples"

для обычных таблиц эти значения близки и особой разницы нет, но для очередей это не так

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

после analyze в reltuples запишется маленькое число и планировщик будет выбирать быстрый seq scan

после vacuum в reltuples запишется большое число и планировщик будет выбирать медленный index scan

...

Рейтинг:

0 / 0

14.12.2017, 19:09

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

медленный index scan + limit

#39569909

vyegorov

Участник

Откуда: Баньоло-ин-Пьяно

Сообщения: 1 237

Рейтинг: 0 / 0

Dany305,

Это поведение из-за LIMIT 50.

...

Рейтинг:

0 / 0

14.12.2017, 19:25

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

медленный index scan + limit

#39569933

fte

Участник

Откуда: Moscow

Сообщения: 402

Рейтинг: 0 / 0

Dany305,

Dany305для обычных таблиц эти значения близки и особой разницы нет, но для очередей это не так
а эта таблица работает как очередь - интенсивно лопатиться, но живых записей мало
после analyze в reltuples запишется маленькое число и планировщик будет выбирать быстрый seq scan
после vacuum в reltuples запишется большое число и планировщик будет выбирать медленный index scan

+1
ИМНО: индивидуальные(агрессивные) настройки autovacuum для таблицы должны помочь...

...

Рейтинг:

0 / 0

14.12.2017, 20:14

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

медленный index scan + limit

#39569937

Dany305

Участник

Сообщения: 103

Рейтинг: 0 / 0

fteDany305,

Dany305для обычных таблиц эти значения близки и особой разницы нет, но для очередей это не так
а эта таблица работает как очередь - интенсивно лопатиться, но живых записей мало
после analyze в reltuples запишется маленькое число и планировщик будет выбирать быстрый seq scan
после vacuum в reltuples запишется большое число и планировщик будет выбирать медленный index scan

+1
ИМНО: индивидуальные(агрессивные) настройки autovacuum для таблицы должны помочь...

вряд ли помогут в решение данной проблемы,
кажется не существует настроек autovacuum,
которые бы гарантировали порядок исполнения: vacuum, analyze

в сходной проблеме мне помогло выключение autovacuum + ручной vacuum analyze

...

Рейтинг:

0 / 0

14.12.2017, 20:30

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

медленный index scan + limit

#39569938

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Dany305,
тут же всё расписано :

//думается кост из инд-скана не берется, а берется из лимита (т.к. ре--сорта нет)
если это не так -- они ещё тупее, чем я думайу

kukurzik ...
дело в селективности, при определенном пороге пж переключается
с правильного индекса (клиент, ид) на "неправильный"(ид)

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.

explain (analyze, buffers)
SELECT *
FROM transfers
WHERE client_number = ?
ORDER BY    id
limit 50

-- 0.01 %
"Limit  (cost=0.43..193.65 rows=50 width=659) (actual time=0.050..0.180 rows=50 loops=1)"
"  Buffers: shared hit=53"
"  ->  Index Scan using transfers_client_number_id_idx on transfers  (cost=0.43..5557.34 rows=1438 width=659) (actual time=0.048..0.171 rows=50 loops=1)"
"        Index Cond: (client_number = 60)"
"        Buffers: shared hit=53"
"Planning time: 0.299 ms"
"Execution time: 0.255 ms"

-- ~2.5%
"Limit  (cost=0.43..116.76 rows=50 width=659) (actual time=0.030..0.109 rows=50 loops=1)"
"  Buffers: shared hit=53"
"  ->  Index Scan using transfers_client_number_id_idx on transfers  (cost=0.43..493100.92 rows=211947 width=659) (actual time=0.029..0.104 rows=50 loops=1)"
"        Index Cond: (client_number = 831)"
"        Buffers: shared hit=53"
"Planning time: 0.178 ms"
"Execution time: 0.142 ms"

-- ~6.5% и праздник закончился
"Limit  (cost=0.43..51.22 rows=50 width=659) (actual time=450.099..450.271 rows=50 loops=1)"
--- тут равномерная модель с фильтрацией 7% вдоль ПК оказалась перекосоёбленной
--- но она строго мясимясисьсьски ВСЕГДА херовее прямой выборки по спец инд-у БЕЗ фильтра
-- мораль -- сидеть и не чирикать, пока не отползут из этого чуда 
-- и даже на мускуль не гнать волну, или там фаерберда
"  Buffers: shared hit=692852"
"  ->  Index Scan using transfers_pkey on transfers  (cost=0.43..604537.41 rows=595121 width=659) (actual time=450.097..450.266 rows=50 loops=1)"
"        Filter: (client_number = 110)"
"        Rows Removed by Filter: 951480"
"        Buffers: shared hit=692852"
"Planning time: 0.388 ms"
"Execution time: 450.332 ms"

за счет чего так просела стоимость лимита по пк -- не ясно. неужели за счет одной лишь ширины инлекса ? на 50 записей доступов д.б. с гулькин нос (по спецу). а в пк же множитель 100/7 как минимум д.б. в костах ? с какого потолка он 51.22 высосал ? или кост этих 100/7 ещё где--то отдельно написан ?
-- судя по всему -- там все херово даже с арифметикой.

или пусть кто--то разъяснит, как стоимость лимита вдоль выборки (без ресорта) расписана в плане . не собирается же он сначала выфетчить унутре в себя те самые 1438, по цене 5557.34

Код: plaintext

"  ->  Index Scan using transfers_client_number_id_idx on transfers  (cost=0.43..5557.34 rows=1438 width=659) ...)"

и токмо опосля лимитится
(на кой хер там вообще эта цифра ?или стюдент 100 лет тому написал -- и хай буде ?)

...

Рейтинг:

0 / 0

14.12.2017, 20:32

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

медленный index scan + limit

#39569944

vyegorov

Участник

Откуда: Баньоло-ин-Пьяно

Сообщения: 1 237

Рейтинг: 0 / 0

qwwqили пусть кто--то разъяснит, как стоимость лимита вдоль выборки (без ресорта) расписана в плане . не собирается же он сначала выфетчить унутре в себя те самые 1438, по цене 5557.34

Код: plaintext

"  ->  Index Scan using transfers_client_number_id_idx on transfers  (cost=0.43..5557.34 rows=1438 width=659) ...)"

и токмо опосля лимитится
Ну вот потому и считается оценка как 2 числа: цена вывода первых записей в узле и цена на всю работу узла. Собственно, цена вывода первых записей (первая цена) и используется в случаях с LIMIT. Как оно оценивается для LIMIT 1 и/или LIMIT 50 я точно не скажу, надо в исходники лезть.

...

Рейтинг:

0 / 0

14.12.2017, 20:51

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

медленный index scan + limit

#39618932

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

вдогонку:

из 2-х индексов not unique (id) , unique (id,indexed) при линковке по id и без выборки поля "индексед" планер выбирает составной уникъю. (при неуникальности ид в десятые процента)

Вангую -- он может прикинуть ограничение количество ид по нему. и насчитать плану меньший кост. а что это количество ограничено для всей таблы (может быть разнесено на прочие оценки) -- то пусть лошадь думает. И пусть сама связывает факт с более компактным не уникъю.

забавно.

...

Рейтинг:

0 / 0

22.03.2018, 14:53

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

медленный index scan + limit

#39619040

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

qwwqвдогонку:

из 2-х индексов not unique (id) , unique (id,indexed) при линковке по id и без выборки поля "индексед" планер выбирает составной уникъю. (при неуникальности ид в десятые процента)

Вангую -- он может прикинуть ограничение количество ид по нему. и насчитать плану меньший кост. а что это количество ограничено для всей таблы (может быть разнесено на прочие оценки) -- то пусть лошадь думает. И пусть сама связывает факт с более компактным не уникъю.

забавно.

а индекс по id точно более компактный (если \di+ indexname cравнить) ?

...

Рейтинг:

0 / 0

22.03.2018, 16:56

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

медленный index scan + limit

#39619068

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Alexiusа индекс по id точно более компактный (если \di+ indexname cравнить) ?
их у меня 90 штук. т.ч. какие-то точно компактнее.

вернее на паре кейсов отреиндексил (ещё тогда) оба -- поведение не изменилось.
отпишусь позже -- сейчас дисковую нагрузил замером.

и да, пж умеет извлекать из уникальности поправки к планам и костам (по лейтералам из пк видно -- планирует по 1). но кажется не умеет обобществлять общезначимые следствия из. (так же как выше по теме получая лучший план из недостаточной модели (равновероятности неизвестного)-- пренебрегает "худшим" из точного индекса-- отбросить дурной оптимистический план, который де-факто д.б. зарезан планом по верному индексу он не умеет)

...

Рейтинг:

0 / 0

22.03.2018, 17:27

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

медленный index scan + limit

#39619327

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Alexius,

сделал

Код: sql

ALTER DATABASE jsons  SET default_statistics_target = '1000';

переиндексировал (чтобы без дырок) и аналайзнул.

появились сканы по узкому инд-у. но остались и по широкому (если записей в плане мало).

Код: sql

1.
2.
3.
4.
5.

"Nested Loop  (cost=0.85..120713.14 rows=8339 width=28)"
"  ->  Index Only Scan using listofdoi_pkey on listofdoi l  (cost=0.43..1159.55 rows=22156 width=24)"
"        Index Cond: ((doi >= '10.1001/10-v4n2-hsf10003'::text) AND (doi <= '10.1002/9780470166123.indsub'::text))"
"  ->  Index Scan using jitems_0000_doi_idx on jitems_0000 t  (cost=0.42..5.39 rows=1 width=38)"
"        Index Cond: (doi = l.doi)"

Код: sql

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

SELECT l.doi 
	,"is-referenced-by-count" 
FROM public.listofdoi  l inner join   doi.jitems_0092 t on t.doi = l.doi
WHERE   l.doi between '10.5300/2001-4/153' AND '10.5840/gps1992439';
---------------
"Nested Loop  (cost=0.85..766.96 rows=34 width=28) (actual time=0.120..17.216 rows=2379 loops=1)"
"  ->  Index Only Scan using listofdoi_pkey on listofdoi l  (cost=0.43..6.23 rows=90 width=24) (actual time=0.085..0.703 rows=2578 loops=1)"
"        Index Cond: ((doi >= '10.5300/2001-4/153'::text) AND (doi <= '10.5840/gps1992439'::text))"
"        Heap Fetches: 0"
"  ->  Index Scan using "jitems_0092_doi_indexed.date-time_idx" on jitems_0092 t  (cost=0.42..8.44 rows=1 width=28) (actual time=0.006..0.006 rows=1 loops=2578)"
"        Index Cond: (doi = l.doi)"
"Planning time: 3.360 ms"
"Execution time: 17.339 ms"
-----------------------------
 Schema |                 Name                  | Type  |  Owner   |    Table    | Size  | Description 
--------+---------------------------------------+-------+----------+-------------+-------+-------------
 doi    | jitems_0092_doi_indexed.date-time_idx | index | postgres | jitems_0092 | 52 MB |

--тут по уникъю планируется

drop UNIQUE:

Код: sql

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

"Nested Loop  (cost=0.85..766.96 rows=34 width=28) (actual time=0.121..16.827 rows=2379 loops=1)"
"  ->  Index Only Scan using listofdoi_pkey on listofdoi l  (cost=0.43..6.23 rows=90 width=24) (actual time=0.086..0.681 rows=2578 loops=1)"
"        Index Cond: ((doi >= '10.5300/2001-4/153'::text) AND (doi <= '10.5840/gps1992439'::text))"
"        Heap Fetches: 0"
"  ->  Index Scan using jitems_0092_doi_idx on jitems_0092 t  (cost=0.42..8.44 rows=1 width=28) (actual time=0.006..0.006 rows=1 loops=2578)"
"        Index Cond: (doi = l.doi)"
"Planning time: 3.256 ms"
"Execution time: 16.943 ms"
-----------------
 Schema |        Name         | Type  |  Owner   |    Table    | Size  | Description 
--------+---------------------+-------+----------+-------------+-------+-------------
 doi    | jitems_0092_doi_idx | index | postgres | jitems_0092 | 43 MB |

и , при массовом плане от 2--5% ожидаемо падаем в хеш

Код: sql

1.
2.
3.
4.
5.
6.

"Hash Join  (cost=3098.77..156501.77 rows=18000 width=28)"
"  Hash Cond: (t.doi = l.doi)"
"  ->  Seq Scan on jitems_0001 t  (cost=0.00..149473.00 rows=1000000 width=29)"
"  ->  Hash  (cost=2500.95..2500.95 rows=47826 width=24)"
"        ->  Index Only Scan using listofdoi_pkey on listofdoi l  (cost=0.43..2500.95 rows=47826 width=24)"
"              Index Cond: ((doi >= '10.1002/9780470166130'::text) AND (doi <= '10.1002/art.1780380723'::text))"

при таргете в 500 все было либо хешем, либо по уникъю.

откачусь. там интереснее. если поймаю разницу в планах по ожиданиям -- отпишусь.

пока же резюме -- при равных костах предпочитается уникъю, даже если шире.

...

Рейтинг:

0 / 0

23.03.2018, 10:41

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

32 сообщений из 32, показаны все 2 страниц

все

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / медленный index scan + limit

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

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

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

start [/forum/topic.php?all=1&fid=53&tid=1995875]:	0ms
get settings:	10ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	35ms
get topic data:	12ms
get forum data:	3ms
get page messages:	70ms
get tp. blocked users:	2ms
others:	301ms

total:	455ms