Index Scan vs Seq Scan

Работаю с одним запросом (хотя дело не в нем ситуация, достаточно типична):

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

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

"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:

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

"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'а я не нашел :(

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

Maxim Boguk,

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

"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 выставлять :) )

Nitro_Junkie,

Хотя fsync мы как раз для медленных HDD выставляем...

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 побольше.