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

Код: sql
1.
2.
3.
4.
5.
6.
SELECT MAX(t2.key0) AS e0
      FROM Sale_userInvoice t1
      JOIN PriceList_priceListLedgerLedgerPriceListTypeStock t0 ON t0.key2=t1.Sale_supplierStockUserInvoice_UserInvoice
      AND t0.key1=457
      JOIN PriceList_priceListLedger t2 ON t2.key0=t0.key0
      WHERE t1.key0=40152341



При его выполнении получаю следующий план:

Код: 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.
"Aggregate  (cost=89223.56..89223.57 rows=1 width=4) (actual time=553.135..553.135 rows=1 loops=1)"
"  Output: max(t2.key0)"
"  Buffers: shared hit=4067 read=24704, temp read=4296 written=4170"
"  ->  Hash Join  (cost=62389.54..89004.19 rows=87747 width=4) (actual time=381.596..550.220 rows=76991 loops=1)"
"        Output: t2.key0"
"        Hash Cond: (t0.key0 = t2.key0)"
"        Buffers: shared hit=4067 read=24704, temp read=4296 written=4170"
"        ->  Nested Loop  (cost=454.38..19469.77 rows=87747 width=4) (actual time=3.699..16.052 rows=76991 loops=1)"
"              Output: t0.key0"
"              Buffers: shared hit=2445"
"              ->  Index Scan using pk_sale_userinvoice on public.sale_userinvoice t1  (cost=0.28..2.29 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=1)"
"                    Output: t1.key0, t1.sale_operationuserinvoice_userinvoice, t1.salepurchaseinvoice_createpurchaseinvoiceuserinvoice_userinvoic, t1.sale_isposteduserinvoice_userinvoice, t1.salepurchaseinvoiceshipment_createpurchaseshipmentuserinvoice_u,  (...)"
"                    Index Cond: (t1.key0 = 40152341)"
"                    Buffers: shared hit=3"
"              ->  Bitmap Heap Scan on public.pricelist_pricelistledgerledgerpricelisttypestock t0  (cost=454.10..19118.53 rows=34895 width=8) (actual time=3.691..11.292 rows=76991 loops=1)"
"                    Output: t0.key0, t0.key1, t0.key2, t0.pricelist_activepricelistledgerledgerpricelisttypestock_null_nu, t0.pricelist_fromdatetimepricelistledgerledgerpricelisttypestock_n, t0.pricelist_todatetimepricelistledgerledgerpricelisttypestock_nu (...)"
"                    Recheck Cond: ((t0.key1 = 457) AND (t0.key2 = t1.sale_supplierstockuserinvoice_userinvoice))"
"                    Buffers: shared hit=2442"
"                    ->  Bitmap Index Scan on pricelist_pricelistledgerledgerpricelisttypestock_key1_key2_idx  (cost=0.00..445.38 rows=34895 width=0) (actual time=3.486..3.486 rows=76991 loops=1)"
"                          Index Cond: ((t0.key1 = 457) AND (t0.key2 = t1.sale_supplierstockuserinvoice_userinvoice))"
"                          Buffers: shared hit=213"
"        ->  Hash  (cost=39810.96..39810.96 rows=1348496 width=4) (actual time=362.824..362.824 rows=1348496 loops=1)"
"              Output: t2.key0"
"              Buckets: 4096  Batches: 64  Memory Usage: 753kB"
"              Buffers: shared hit=1622 read=24704, temp written=3855"
"              ->  Seq Scan on public.pricelist_pricelistledger t2  (cost=0.00..39810.96 rows=1348496 width=4) (actual time=0.060..205.528 rows=1348496 loops=1)"
"                    Output: t2.key0"
"                    Buffers: shared hit=1622 read=24704"
"Total runtime: 553.626 ms"



Особенность его в том, что Postgres решает бежать по всему priceListLedger а там 1,3М записей.

Выключаю hash_join и merge_join:

План становится следующим:

Код: 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.
"Aggregate  (cost=177127.26..177127.27 rows=1 width=4) (actual time=156.679..156.679 rows=1 loops=1)"
"  Output: max(t2.key0)"
"  Buffers: shared hit=306508 read=4313"
"  ->  Nested Loop  (cost=454.81..176907.89 rows=87747 width=4) (actual time=6.319..152.687 rows=76991 loops=1)"
"        Output: t2.key0"
"        Buffers: shared hit=306508 read=4313"
"        ->  Nested Loop  (cost=454.38..19469.77 rows=87747 width=4) (actual time=6.102..34.438 rows=76991 loops=1)"
"              Output: t0.key0"
"              Buffers: shared read=2445"
"              ->  Index Scan using pk_sale_userinvoice on public.sale_userinvoice t1  (cost=0.28..2.29 rows=1 width=4) (actual time=0.028..0.030 rows=1 loops=1)"
"                    Output: t1.key0, t1.sale_operationuserinvoice_userinvoice, t1.salepurchaseinvoice_createpurchaseinvoiceuserinvoice_userinvoic, t1.sale_isposteduserinvoice_userinvoice, t1.salepurchaseinvoiceshipment_createpurchaseshipmentuserinvoice_u,  (...)"
"                    Index Cond: (t1.key0 = 40152341)"
"                    Buffers: shared read=3"
"              ->  Bitmap Heap Scan on public.pricelist_pricelistledgerledgerpricelisttypestock t0  (cost=454.10..19118.53 rows=34895 width=8) (actual time=6.067..30.114 rows=76991 loops=1)"
"                    Output: t0.key0, t0.key1, t0.key2, t0.pricelist_activepricelistledgerledgerpricelisttypestock_null_nu, t0.pricelist_fromdatetimepricelistledgerledgerpricelisttypestock_n, t0.pricelist_todatetimepricelistledgerledgerpricelisttypestock_nu (...)"
"                    Recheck Cond: ((t0.key1 = 457) AND (t0.key2 = t1.sale_supplierstockuserinvoice_userinvoice))"
"                    Buffers: shared read=2442"
"                    ->  Bitmap Index Scan on pricelist_pricelistledgerledgerpricelisttypestock_key1_key2_idx  (cost=0.00..445.38 rows=34895 width=0) (actual time=5.786..5.786 rows=76991 loops=1)"
"                          Index Cond: ((t0.key1 = 457) AND (t0.key2 = t1.sale_supplierstockuserinvoice_userinvoice))"
"                          Buffers: shared read=213"
"        ->  Index Only Scan using pk_pricelist_pricelistledger on public.pricelist_pricelistledger t2  (cost=0.43..1.78 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=76991)"
"              Output: t2.key0"
"              Index Cond: (t2.key0 = t0.key0)"
"              Heap Fetches: 76991"
"              Buffers: shared hit=306508 read=1868"
"Total runtime: 156.747 ms"



В 4 раза быстрее, и это на холодную базу (повторный запрос еще быстрее 123 сек). Особенность этого плана в том, что Postgres уже бежит по индексу, и делает это гораздо быстрее.

Что интересно cost'ы у них приблизительно равны, но у варианта с index'ом разброс больше 454.81..176907.89 против 62389.54..89004.19 у hash join, при этом верхний Aggregate берет пессимистичный вариант (наверное единственное место во всей СУБД, где постгрес идет по пессимистичному сценарию), и получается, что вариант с индексом СУБД кажется хуже. Конечно ее в чем то можно понять, она не знает что у меня очень быстрый SSD и дофига RAM'а под shared_buffer'ы, но тут вопрос как заставить СУБД в таком случае использовать индексы чаще scan'ов. По идее должен помогать random_page_cost, но я его и так в 1.0 поставил - меньше ставить не хочется, других настроек planner'а я не нашел :(
...
Рейтинг: 0 / 0
31.03.2015, 15:36
| Ответить | Цитировать | Написать  
Index Scan vs Seq Scan
    #38922712
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Nitro_Junkie,

попробуйте
seq_page_cost=0.1
random_page_cost=0.1
cpu_tuple_cost=0.05
effective_cache_size=адекватный вашему обьему памяти (про него часто забывают вообще)

Это ближе к случаю базы в памяти.


--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
31.03.2015, 15:40
| Ответить | Цитировать | Написать  
Index Scan vs Seq Scan
    #38922779
Nitro_Junkie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nitro_Junkie
Участник
Maxim Boguk,

Thx, вроде помогло:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
"Aggregate  (cost=53804.51..53804.56 rows=1 width=4) (actual time=122.646..122.646 rows=1 loops=1)"
"  Output: max(t2.key0)"
"  Buffers: shared hit=310821"
"  ->  Nested Loop  (cost=1.13..53585.14 rows=87747 width=4) (actual time=0.020..118.706 rows=76991 loops=1)"
"        Output: t2.key0"
"        Buffers: shared hit=310821"
"        ->  Nested Loop  (cost=0.71..5734.73 rows=87747 width=4) (actual time=0.015..16.895 rows=76991 loops=1)"
"              Output: t0.key0"
"              Buffers: shared hit=2445"
"              ->  Index Scan using pk_sale_userinvoice on public.sale_userinvoice t1  (cost=0.28..0.54 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)"
"                    Output: t1.key0, t1.sale_operationuserinvoice_userinvoice, t1.salepurchaseinvoice_createpurchaseinvoiceuserinvoice_userinvoic, t1.sale_isposteduserinvoice_userinvoice, t1.salepurchaseinvoiceshipment_createpurchaseshipmentuserinvoice_u,  (...)"
"                    Index Cond: (t1.key0 = 40152341)"
"                    Buffers: shared hit=3"
"              ->  Index Scan using pricelist_pricelistledgerledgerpricelisttypestock_key1_key2_idx on public.pricelist_pricelistledgerledgerpricelisttypestock t0  (cost=0.43..3989.45 rows=34895 width=8) (actual time=0.009..12.701 rows=76991 loops=1)"
"                    Output: t0.key0, t0.key1, t0.key2, t0.pricelist_activepricelistledgerledgerpricelisttypestock_null_nu, t0.pricelist_fromdatetimepricelistledgerledgerpricelisttypestock_n, t0.pricelist_todatetimepricelistledgerledgerpricelisttypestock_nu (...)"
"                    Index Cond: ((t0.key1 = 457) AND (t0.key2 = t1.sale_supplierstockuserinvoice_userinvoice))"
"                    Buffers: shared hit=2442"
"        ->  Index Only Scan using pk_pricelist_pricelistledger on public.pricelist_pricelistledger t2  (cost=0.43..0.50 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=76991)"
"              Output: t2.key0"
"              Index Cond: (t2.key0 = t0.key0)"
"              Heap Fetches: 76991"
"              Buffers: shared hit=308376"
"Total runtime: 122.686 ms"



А какие вообще есть важные параметры (кроме вышеперечисленных) для полу-OLTP, не ORM системы на SSD'ке с базой влезающей в shared_buffers, не критичными данными (то есть потеря 5 минут не критична)? (например мы любим fsync = off выставлять :) )
...
Рейтинг: 0 / 0
31.03.2015, 16:07
| Ответить | Цитировать | Написать  
Index Scan vs Seq Scan
    #38922799
Nitro_Junkie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nitro_Junkie
Участник
Nitro_Junkie,

Хотя fsync мы как раз для медленных HDD выставляем...
...
Рейтинг: 0 / 0
31.03.2015, 16:13
| Ответить | Цитировать | Написать  
Index Scan vs Seq Scan
    #38922829
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
Nitro_JunkieЧто интересно cost'ы у них приблизительно равны, но у варианта с index'ом разброс больше 454.81..176907.89 против 62389.54..89004.19 у hash join, при этом верхний Aggregate берет пессимистичный вариант (наверное единственное место во всей СУБД, где постгрес идет по пессимистичному сценарию)

454.81..176907.89

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

fsync выключать особого профита нет, если только нет желания из бэкапа при сбое восстанавливаться. тоже самое можно получить и с synchronous_commit=off, wal_writer_delay побольше.
...
Рейтинг: 0 / 0
31.03.2015, 16:25
| Ответить | Цитировать | Написать  
5 сообщений из 5, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Index Scan vs Seq Scan
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=38922712&tid=1998079]:
 0ms
get settings:
 9ms
get forum list:
 17ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 159ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 61ms
get tp. blocked users:
 1ms
others: 248ms
total:  516ms
созд. / загр.: 516ms
x
x
Закрыть


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