ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / медленный index scan + limit
25 сообщений из 32, страница 1 из 2
  1  все
медленный index scan + limit
    #39568283
kukurzik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kukurzik
Участник
Имеем часто обновляемую таблицу 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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
kukurzik,

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

client_number, id, creation_date

он покроет оба варианта запросов
...
Рейтинг: 0 / 0
12.12.2017, 13:20
| Ответить | Цитировать | Написать  
медленный index scan + limit
    #39568315
kukurzik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kukurzik
Участник
Maxim Boguk,


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

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


//и все же.. чтение 38 Gb при размере таблицы в 30 раз меньше,
откуда здесь ноги растут?
...
Рейтинг: 0 / 0
12.12.2017, 13:42
| Ответить | Цитировать | Написать  
медленный index scan + limit
    #39568325
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
kukurzikоткуда здесь ноги растут?
для второго случая обратите внимание на то, какой индекс используется.
...
Рейтинг: 0 / 0
12.12.2017, 13:51
| Ответить | Цитировать | Написать  
медленный index scan + limit
    #39568355
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
vyegorovkukurzikоткуда здесь ноги растут?
для второго случая обратите внимание на то, какой индекс используется.
при "нормальной" оценке и "нормальной" статистике (в неком гипотетическом смысле слова "нормальный") естественно ожидать секскана, задолго до.

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

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

я бы дату выбросил совсем -- порядок и так полностью определяется парой ["что--то","ПК"]. далее уже перемешивание воздуха и тавтология.
...
Рейтинг: 0 / 0
12.12.2017, 14:25
| Ответить | Цитировать | Написать  
медленный index scan + limit
    #39568379
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
[quot qwwq]vyegorovя бы дату выбросил совсем -- порядок и так полностью определяется парой ["что--то","ПК"]. далее уже перемешивание воздуха и тавтология.

Это правда... надо дату из индекса убрать и из запроса убрать.
...
Рейтинг: 0 / 0
12.12.2017, 15:07
| Ответить | Цитировать | Написать  
медленный index scan + limit
    #39568388
kukurzik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kukurzik
Участник
Подозреваю, что дату впихнули в сортировку, чтобы ускорить выдачу,
ибо до нужного индекса не дожевали.
Разумеется, дата ни к чему.

Что касается статистики, то она свежа.
никакого блоата и дохлых записей, ибо замеры на базе из бэкапа развернутой.
Никаких экзотических правок в конфиги не вносилось.
...
Рейтинг: 0 / 0
12.12.2017, 15:28
| Ответить | Цитировать | Написать  
медленный index scan + limit
    #39568416
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
kukurzik,

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

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


(!)но свои енейблы и косты могли бы и процитировать. для подумать.
...
Рейтинг: 0 / 0
12.12.2017, 15:59
| Ответить | Цитировать | Написать  
медленный index scan + limit
    #39568447
kukurzik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kukurzik
Участник
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kukurzik
Участник
qwwq,

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


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

индекс по (id+0) исправил это, пока противоправных действий не обнаружил.
однако, так ли необходим ордербай по (клиент,(ид+0),
кажется, что ордербай просто по (ид+0) справляется.
...
Рейтинг: 0 / 0
12.12.2017, 17:18
| Ответить | Цитировать | Написать  
медленный index scan + limit
    #39568530
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
kukurzikqwwq,

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


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

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

одербай конечно при фиксе по пре--фиксу не нужен. тут пж пока сам справляется.
...
Рейтинг: 0 / 0
12.12.2017, 17:34
| Ответить | Цитировать | Написать  
медленный index scan + limit
    #39568903
kukurzik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kukurzik
Участник
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
kukurzik,

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

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

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

есть подозрение ,что немеряно математики придётся изменить, чтобы оно так себя больше не вело.
...
Рейтинг: 0 / 0
13.12.2017, 12:38
| Ответить | Цитировать | Написать  
медленный index scan + limit
    #39568989
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kukurzik
Участник
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kukurzik
Участник
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
Павел Лузанов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Лузанов
Участник
kukurzik,

На 9.4 сделайте запрос с SELECT id, а не SELECT *
Видите, на 10-ке уже INDEX ONLY SCAN подцепился, может и 9.4 планировщик посчитает это дешевле.
...
Рейтинг: 0 / 0
13.12.2017, 15:02
| Ответить | Цитировать | Написать  
медленный index scan + limit
    #39569101
kukurzik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kukurzik
Участник
Радость за 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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
kukurzik,

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

т.ч. копите хитрые отвертки с левым винтом -- они вам ещё пригодятся.
...
Рейтинг: 0 / 0
13.12.2017, 16:04
| Ответить | Цитировать | Написать  
медленный index scan + limit
    #39569120
kukurzik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kukurzik
Участник
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Maxim Boguk,

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

т.ч. надрачивать на статистику бессмысленно
надо пж математике начинать учить. -- когда левые допущения считаются протухшими, например.
...
Рейтинг: 0 / 0
13.12.2017, 16:13
| Ответить | Цитировать | Написать  
медленный index scan + limit
    #39569904
Dany305
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dany305
Участник
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Dany305,

Это поведение из-за LIMIT 50.
...
Рейтинг: 0 / 0
14.12.2017, 19:25
| Ответить | Цитировать | Написать  
медленный index scan + limit
    #39569933
fte
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fte
Участник
Dany305,

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

+1
ИМНО: индивидуальные(агрессивные) настройки autovacuum для таблицы должны помочь...
...
Рейтинг: 0 / 0
14.12.2017, 20:14
| Ответить | Цитировать | Написать  
25 сообщений из 32, страница 1 из 2
  1  все
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / медленный index scan + limit
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&fpage=57&tid=1995875]:
 0ms
get settings:
 7ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 33ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 96ms
get tp. blocked users:
 2ms
others: 14ms
total:  186ms
созд. / загр.: 186ms
x
x
Закрыть


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