ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Filter cost в Index Scan
7 сообщений из 7, страница 1 из 1
Filter cost в Index Scan
    #39222298
Nitro_Junkie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nitro_Junkie
Участник
Есть большая таблица ( >3млн записей). Там выполняется примитивный запрос:

Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT t1.key0 AS jkey0
FROM ReceiptSkuLedger_ReceiptSaleSkuLedger t1
JOIN
  t_65 t0 ON t0.k0=t1.ReceiptSkuLedger_sku_ReceiptSaleSkuLedger
AND t0.k1=t1.ReceiptSkuLedger_date_ReceiptSaleSkuLedger
AND t0.k2=t1.ReceiptSkuLedger_price_ReceiptSaleSkuLedger
AND t0.k3=t1.ReceiptSkuLedger_departmentStore_ReceiptSaleSkuLedger



По сути берется временная таблица и в большой таблице отбираются все записи с 4 полями из этой временной таблицы.

В таблице есть несколько индексов. В частности по всем 4 полям, но есть по 2 и 3-м (price+date например). При выполнении postgres почему-то выбирает индекс с наименьшим числом полей и получает такой план:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
"Nested Loop  (cost=0.56..279.95 rows=1 width=4) (actual time=0.050..125.175 rows=300 loops=1)"
"  Output: t1.key0"
"  Buffers: shared hit=142319, local hit=2"
"  ->  Seq Scan on pg_temp_2.t_65  (cost=0.00..15.20 rows=300 width=18) (actual time=0.003..0.024 rows=300 loops=1)"
"        Output: t_65.k0, t_65.k1, t_65.k2, t_65.k3"
"        Buffers: local hit=2"
"  ->  Index Scan using receiptskuledger_price_receiptsaleskuledger_receiptskuledger_sk on public.receiptskuledger_receiptsaleskuledger t1  (cost=0.56..0.83 rows=1 width=22) (actual time=0.220..0.417 rows=1 loops=300)"
"        Output: t1.key0, t1.receiptskuledger_date_receiptsaleskuledger, t1.receiptskuledger_sku_receiptsaleskuledger, t1.receiptskuledger_price_receiptsaleskuledger, t1.receiptskuledger_departmentstore_receiptsaleskuledger"
"        Index Cond: ((t1.receiptskuledger_price_receiptsaleskuledger = t_65.k2) AND (t1.receiptskuledger_sku_receiptsaleskuledger = t_65.k0))"
"        Filter: ((t_65.k1 = t1.receiptskuledger_date_receiptsaleskuledger) AND (t_65.k3 = t1.receiptskuledger_departmentstore_receiptsaleskuledger))"
"        Rows Removed by Filter: 470"
"        Buffers: shared hit=142319"
"Planning time: 0.577 ms"
"Execution time: 125.221 ms"



Если убрать этот индекс, он начинает использовать индекс по 3, и только когда никакого другого индекса нет, начинает использовать индекс по 4 и получает следующий план:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
"Nested Loop  (cost=0.56..281.45 rows=1 width=4) (actual time=0.017..1.769 rows=300 loops=1)"
"  Output: t1.key0"
"  Buffers: shared hit=1503, local hit=2"
"  ->  Seq Scan on pg_temp_8.t_65  (cost=0.00..15.20 rows=300 width=18) (actual time=0.003..0.016 rows=300 loops=1)"
"        Output: t_65.k0, t_65.k1, t_65.k2, t_65.k3"
"        Buffers: local hit=2"
"  ->  Index Only Scan using receiptskuledger_date_receiptsaleskuledger_receiptskuledger_dep on public.receiptskuledger_receiptsaleskuledger t1  (cost=0.56..0.84 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=300)"
"        Output: t1.receiptskuledger_date_receiptsaleskuledger, t1.receiptskuledger_departmentstore_receiptsaleskuledger, t1.receiptskuledger_sku_receiptsaleskuledger, t1.receiptskuledger_price_receiptsaleskuledger, t1.key0"
"        Index Cond: ((t1.receiptskuledger_date_receiptsaleskuledger = t_65.k1) AND (t1.receiptskuledger_departmentstore_receiptsaleskuledger = t_65.k3) AND (t1.receiptskuledger_sku_receiptsaleskuledger = t_65.k0) AND (t1.receiptskuledger_price_receiptsales (...)"
"        Heap Fetches: 300"
"        Buffers: shared hit=1503"
"Planning time: 0.487 ms"
"Execution time: 1.809 ms"



Проблема с использованием индекса усугубляется, когда shared_buffers'ов не хватает и план становится таким:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
"Nested Loop  (cost=0.56..279.95 rows=1 width=4) (actual time=0.269..970.659 rows=300 loops=1)"
"  Output: t1.key0"
"  Buffers: shared hit=28900 read=113419 written=15, local hit=2"
"  ->  Seq Scan on pg_temp_8.t_65  (cost=0.00..15.20 rows=300 width=18) (actual time=0.004..0.164 rows=300 loops=1)"
"        Output: t_65.k0, t_65.k1, t_65.k2, t_65.k3"
"        Buffers: local hit=2"
"  ->  Index Scan using receiptskuledger_price_receiptsaleskuledger_receiptskuledger_sk on public.receiptskuledger_receiptsaleskuledger t1  (cost=0.56..0.83 rows=1 width=22) (actual time=1.649..3.233 rows=1 loops=300)"
"        Output: t1.key0, t1.receiptskuledger_date_receiptsaleskuledger, t1.receiptskuledger_sku_receiptsaleskuledger, t1.receiptskuledger_price_receiptsaleskuledger, t1.receiptskuledger_departmentstore_receiptsaleskuledger"
"        Index Cond: ((t1.receiptskuledger_price_receiptsaleskuledger = t_65.k2) AND (t1.receiptskuledger_sku_receiptsaleskuledger = t_65.k0))"
"        Filter: ((t_65.k1 = t1.receiptskuledger_date_receiptsaleskuledger) AND (t_65.k3 = t1.receiptskuledger_departmentstore_receiptsaleskuledger))"
"        Rows Removed by Filter: 470"
"        Buffers: shared hit=28900 read=113419 written=15"
"Planning time: 0.776 ms"
"Execution time: 970.736 ms"



(то есть еще переполняется shared_buffers, что совсем не айс).

Причем плохой план она выбирает, потому что cost чуть-чуть меньше. По идее на него должен влиять effective_cache_size, но он ни на что не влияет (если его поставить даже 20МБ план остается)

Также при изменении page_cost, tuple_cost, cost'ы увеличиваются:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
"Nested Loop  (cost=0.56..2585.75 rows=1 width=4) (actual time=0.072..126.142 rows=300 loops=1)"
"  Output: t1.key0"
"  Buffers: shared hit=142319, local hit=2"
"  ->  Seq Scan on pg_temp_2.t_65  (cost=0.00..5.00 rows=300 width=18) (actual time=0.003..0.030 rows=300 loops=1)"
"        Output: t_65.k0, t_65.k1, t_65.k2, t_65.k3"
"        Buffers: local hit=2"
"  ->  Index Scan using receiptskuledger_price_receiptsaleskuledger_receiptskuledger_sk on public.receiptskuledger_receiptsaleskuledger t1  (cost=0.56..8.59 rows=1 width=22) (actual time=0.219..0.420 rows=1 loops=300)"
"        Output: t1.key0, t1.receiptskuledger_date_receiptsaleskuledger, t1.receiptskuledger_sku_receiptsaleskuledger, t1.receiptskuledger_price_receiptsaleskuledger, t1.receiptskuledger_departmentstore_receiptsaleskuledger"
"        Index Cond: ((t1.receiptskuledger_price_receiptsaleskuledger = t_65.k2) AND (t1.receiptskuledger_sku_receiptsaleskuledger = t_65.k0))"
"        Filter: ((t_65.k1 = t1.receiptskuledger_date_receiptsaleskuledger) AND (t_65.k3 = t1.receiptskuledger_departmentstore_receiptsaleskuledger))"
"        Rows Removed by Filter: 470"
"        Buffers: shared hit=142319"
"Planning time: 0.601 ms"
"Execution time: 126.192 ms"



Но при этом этот план все равно приоритетнее. Причем на другом компе с такой же версией БД и теми же настройками она выбирает правильный план (но на большинстве все же не правильный).

Вопрос что влияет на определения cost'а при Filter в Index Scan. Почему он такой маленький и что на него еще может влиять?
...
Рейтинг: 0 / 0
22.04.2016, 12:22
| Ответить | Цитировать | Написать  
Filter cost в Index Scan
    #39222347
Nitro_Junkie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nitro_Junkie
Участник
Хотя видимо понятна причина, она не видит корреляцию между sku и price (товаром и его ценой)

Соответственно если взглянуть на pg_stats

Колонки:

"receiptskuledger_receiptsaleskuledger";"key0"
"receiptskuledger_receiptsaleskuledger";"receiptskuledger_date_receiptsaleskuledger"
"receiptskuledger_receiptsaleskuledger";"receiptskuledger_price_receiptsaleskuledger"
"receiptskuledger_receiptsaleskuledger";"receiptskuledger_departmentstore_receiptsaleskuledger"
"receiptskuledger_receiptsaleskuledger";"receiptskuledger_sku_receiptsaleskuledger"
"t_65";"k0"
"t_65";"k1"
"t_65";"k2"
"t_65";"k3"

n_distinct:
-1
461
1414
43
13837
-0.99
-0.666667
-0.533333
-0.13

Она берет price * sku и считает selectivity очень большой и поэтому использует более короткий индекс. Вопрос как с этим можно бороться :( Кроме переконфигурации индексов чтобы не ставить коррелированные колонки рядом
...
Рейтинг: 0 / 0
22.04.2016, 12:57
| Ответить | Цитировать | Написать  
Filter cost в Index Scan
    #39222470
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Nitro_Junkie,

Цена узла строится из:
- цены доступа к блокам, причём не важно, холодным или горячим
- цены доступа к записи
- цены каждой операции
Цена доступа к блоку самая большая, поэтому “в среднем” база будет “предпочитать” индексы поменьше.
Играть параметрами смысла нет, т.к. везде IndexScan, а он базируется на `random_page_cost` для блоков, т.е. цена будет меняться одинаково для всех вариантов.
...
Рейтинг: 0 / 0
22.04.2016, 14:09
| Ответить | Цитировать | Написать  
Filter cost в Index Scan
    #39222479
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
поспешил...

В плане с IOS, присутствуют HeapFetches — планировщик их не любит. Что-то поменяется, если сделать `VACUUM ANALYZE` таблице перед запросом?

И да — было бы интересно глянуть на определения объектов.
...
Рейтинг: 0 / 0
22.04.2016, 14:13
| Ответить | Цитировать | Написать  
Filter cost в Index Scan
    #39222526
Nitro_Junkie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nitro_Junkie
Участник
vyegorovпоспешил...

В плане с IOS, присутствуют HeapFetches — планировщик их не любит. Что-то поменяется, если сделать `VACUUM ANALYZE` таблице перед запросом?

И да — было бы интересно глянуть на определения объектов.

Хм... Вообще базовая проблема с неправильным Index selectivity (планировщик думает что sku+price отберет одну запись, но это не так из-за их корреляции), но действительно, непонятно почему не смотря на это планировщик не предпочитает использовать Index only scan вместо просто Index scan.

Сейчас сделал VACUUM ANALYZE и везде план стал нормальным. Но непонятно почему. На всех базах по этой таблице делается VACUUM FULL раз в неделю, при приросте всего на 2-3% за этот период. Этого получается недостаточно?
...
Рейтинг: 0 / 0
22.04.2016, 14:38
| Ответить | Цитировать | Написать  
Filter cost в Index Scan
    #39222536
Nitro_Junkie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nitro_Junkie
Участник
Вот план новый (тут default'ые *_cost, поэтому сравнивать его имеет смысл с последним планом)
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
"Nested Loop  (cost=0.56..1387.25 rows=1 width=4) (actual time=0.045..1.329 rows=300 loops=1)"
"  Output: t1.key0"
"  Buffers: shared hit=1379, local hit=2"
"  ->  Seq Scan on pg_temp_2.t_65  (cost=0.00..5.00 rows=300 width=18) (actual time=0.003..0.013 rows=300 loops=1)"
"        Output: t_65.k0, t_65.k1, t_65.k2, t_65.k3"
"        Buffers: local hit=2"
"  ->  Index Only Scan using receiptskuledger_date_receiptsaleskuledger_receiptskuledger_dep on public.receiptskuledger_receiptsaleskuledger t1  (cost=0.56..4.60 rows=1 width=22) (actual time=0.004..0.004 rows=1 loops=300)"
"        Output: t1.receiptskuledger_date_receiptsaleskuledger, t1.receiptskuledger_departmentstore_receiptsaleskuledger, t1.receiptskuledger_sku_receiptsaleskuledger, t1.receiptskuledger_price_receiptsaleskuledger, t1.key0"
"        Index Cond: ((t1.receiptskuledger_date_receiptsaleskuledger = t_65.k1) AND (t1.receiptskuledger_departmentstore_receiptsaleskuledger = t_65.k3) AND (t1.receiptskuledger_sku_receiptsaleskuledger = t_65.k0) AND (t1.receiptskuledger_price_receiptsales (...)"
"        Heap Fetches: 0"
"        Buffers: shared hit=1379"
"Planning time: 0.621 ms"
"Execution time: 1.363 ms"
...
Рейтинг: 0 / 0
22.04.2016, 14:43
| Ответить | Цитировать | Написать  
Filter cost в Index Scan
    #39222717
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Nitro_JunkieСейчас сделал VACUUM ANALYZE и везде план стал нормальным. Но непонятно почему. На всех базах по этой таблице делается VACUUM FULL раз в неделю, при приросте всего на 2-3% за этот период. Этого получается недостаточно?
Недостаточно. Помимо прироста бывают ещё и измения. Для большой таблицы 2% прироста легко проскакивают порог, после которого планировщик считает таблицу "слишком грязной" и игнорирует IOS.

VACUUM FULL делать часто не надо, это специальная команда которая перестраивает таблицу и индексы. Она думана для случаев, когда совсем всё плохо.
Настройте autovacuum агрессивно, я делаю так:
параметрзначениеautovacuum_analyze_scale_factor0.02autovacuum_vacuum_scale_factor0.005autovacuum_vacuum_cost_delay-1autovacuum_vacuum_cost_limit-1autovacuum_max_workers35 (не меньше 10)autovacuum_work_mem256MBlog_autovacuum_min_duration1000vacuum_cost_delay0vacuum_freeze_min_age10000000
...
Рейтинг: 0 / 0
22.04.2016, 16:52
| Ответить | Цитировать | Написать  
7 сообщений из 7, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Filter cost в Index Scan
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&fpage=93&tid=1997279]:
 0ms
get settings:
 5ms
get forum list:
 8ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 42ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 33ms
get tp. blocked users:
 1ms
others: 199ms
total:  301ms
созд. / загр.: 301ms
x
x
Закрыть


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