První případ je většinou způsoben pochybeními administrátorů (zastaralé statistiky či chybně nastavené cost_* proměnné, v důsledku čehož nelze spočítat dostatečně přesný odhad ceny execution planu, apod.). Neaktuální statistiky byly podrobněji rozebrány části věnované administrátorským chybám, takže se jim znovu věnovat nebudeme.

V druhém případě je škála možných příčin podstatně širší - kromě již zmíněných neaktuálních statistik přichází v úvahu mnoho způsobů jak zprznit SQL dotaz tak aby k jeho vyhodnocení index použít nešlo. A právě těmto případům se budeme v následujících odstavcích věnovat ...

Chybějící index

První typ problémů je celkem triviální - dotaz sice o index přímo škemrá, ale na tabulce žádný použitelný index bohužel neexistuje. To může mít mnoho validních příčin - například tabulka původně mohla být velmi malá takže by index nic nepřinesl (malé tabulky je efektivnější načíst sekvenčně celé), a nikdo neočekával že její velikost výrazně naroste.

Každopádně jakmile najdete dotaz který velkou část času tráví sekvenčním čtením tabulky, a jeho podmínka je indexovatelná, je nejvyšší čas o indexu uvažovat. Uvažujme opět tabulku, naplněnou tentokrát opět 1.000.000 řádek:

CREATE TABLE test_table (id INTEGER, value VARCHAR(255));
INSERT INTO test_table SELECT i, REPEAT(MD5(i::text), 5)
                         FROM generate_series(1,1000000) AS s(i);
ANALYZE test_table;

A zkusme vyhledat jeden řádek podle hodnoty ve sloupci ID:

db=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE id = 741283;

                              QUERY PLAN
----------------------------------------------------------------------
 Seq Scan on test_table  (cost=0.00..36891.39 rows=1 width=168)
              (actual time=11447.660..15196.489 rows=1 loops=1)
   Filter: (id = 741283)
 Total runtime: 15196.525 ms
(3 rows)

No, a nyní vytvořme index nad sloupcem ID, a znovu se podívejme jaký execution plan získáme:

db=# CREATE INDEX test_table_idx ON test_table(id);
db=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE id = 741283;

                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using test_table_idx on test_table
       (cost=0.00..8.36 rows=1 width=168)
       (actual time=0.235..0.237 rows=1 loops=1)
   Index Cond: (id = 741283)
 Total runtime: 0.265 ms
(3 rows)

Jak vidíte, index se použil a dopad na výkon dotazu je markantní, a to jak při srovnání času potřebného na jeho vykonání, tak i při srovnání "abstraktní ceny" zahrnující diskové operace apod.

Poznámka: Ačkoliv by se zdálo že přidání indexu do databáze je triviální a bezproblémová záležitost, nemusí to platit vždy. Problémy nastávají například u databází s velkým množstvím aktualizací dat (vkládání nových řádků, mazání starých řádků a aktualizace stávajících) - každý index přináší overhead během úprav a údržby, na druhou stranu ho lze při těchto operacích často využít (například při mazání nebo aktualizaci s WHERE podmínkou). Obecně ale po přidání indexu mohou změnit execution plany dalších dotazů. Přidání indexu na produkční databázi bez otestování tedy doporučuji pouze v případě řešení akutních výkonnostních problémů - jinak se jedná o změnu která by měla být testována stejně jako běžné změny ve zdrojových kódech vaší aplikace.

Nízká selektivita podmínky

Dobře - neexistující index je skutečně triviální problém. Podívejme se tedy na komplikovanější situace kdy index sice existuje, ale z nějakého důvodu se nepoužije. První možná přičina souvisí s tím že použití indexu je efektivní jen při čtení malého zlomku tabulky - ve chvíli kdy podmínce odpovídá vpříliš velká část tabulky (přičemž konkrétní hodnota záleží na nastavení "cost" proměnných, typu sloupce apod.), je efektivnější sekvenčně načíst celou tabulku a filtrování dle podmínky provádět průběžně.

To je důsledkem rozdílů mezi sekvenčním a náhodným přístupem k datům uloženým na pevném disku, podrobnosti viz. například základní principy fungování a použití indexů v relačních databázích.

Uvažme opět tabulku test_table s 1.000.000 řádek a indexem nad sloupcem ID:

CREATE TABLE test_table (id INTEGER, value VARCHAR(255));
INSERT INTO test_table SELECT i, REPEAT(MD5(i::text), 5)
                         FROM generate_series(1,1000000) AS s(i);
CREATE INDEX test_table_idx ON test_table(id);
ANALYZE test_table;

a zkusme vyhledat řádky s ID menším než 50.000:

db=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE id <= 50000;

                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using test_table_idx on test_table
       (cost=0.00..2167.15 rows=42675 width=168)
       (actual time=0.084..114.564 rows=49999 loops=1)
   Index Cond: (id < 50000)
 Total runtime: 173.257 ms
(3 rows)

Dle očekávání se použil index scan - pokud výrazně zvýšíme limit (například na 800.000), dostaneme úplně jiný execution plan:

db=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE id <= 800000;

                              QUERY PLAN
----------------------------------------------------------------------
 Seq Scan on test_table
     (cost=0.00..36891.39 rows=806072 width=168)
     (actual time=0.032..1298.406 rows=799999 loops=1)
   Filter: (id < 800000)
 Total runtime: 2204.269 ms
(3 rows)

Ano, zvolil se sekvenční sken tabulky - že je skutečně efektivnější než index scan se můžeme přesvědčit "vypnutím" sekvenčních skenů pomocí proměnné enable_seqscan (ve skutečnosti se jedná pouze o znevýhodnění - sekvenční skeny nelze zcela vypnout)

db=# SET enable_seqscan = off;
db=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE id <= 800000;

                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using test_table_idx on test_table
       (cost=0.00..40858.60 rows=806072 width=168)
       (actual time=0.093..1303.429 rows=799999 loops=1)
   Index Cond: (id < 800000)
 Total runtime: 2236.221 ms
(3 rows)

Sekvenční sken je evidentně rychlejší než vynucený index scan (byť jenom drobně).

Pokud bychom použili složitější (a hlavně "větší") index, konkrétně složený index nad sloupci (id, value), budou výsledky ještě o něco zajímavější a sekvenční sken bude efektivnější "dříve":

CREATE TABLE test_table (id INTEGER, value VARCHAR(255));
INSERT INTO test_table SELECT i, REPEAT(MD5(i::text), 5)
                         FROM generate_series(1,1000000) AS s(i);
CREATE INDEX test_table_val_idx ON test_table(id, value);
ANALYZE test_table;

Ze začátku bude opět nejefektivnější index scan:

db=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE id <= 10000;

                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using test_table_idx on test_table
       (cost=0.00..13307.57 rows=8138 width=168)
       (actual time=0.023..16.709 rows=10000 loops=1)
   Index Cond: (id <= 10000)
 Total runtime: 28.436 ms
(3 rows)

Při zvýšení hodnoty bude namísto prostého index scanu zvolen bitmap index scan:

db=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE id <= 50000;

                              QUERY PLAN
----------------------------------------------------------------------
 Bitmap Heap Scan on test_table
        (cost=5294.69..30418.82 rows=47665 width=168)
        (actual time=11.261..75.096 rows=50000 loops=1)
   Recheck Cond: (id <= 50000)
   ->  Bitmap Index Scan on test_table_idx
              (cost=0.00..5282.77 rows=47665 width=0)
              (actual time=10.956..10.956 rows=50000 loops=1)
         Index Cond: (id <= 50000)
 Total runtime: 128.998 ms
(5 rows)

A po dalším zvýšení opět začne vítězit sekvenční sken:

db=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE id <= 200000;

                              QUERY PLAN
----------------------------------------------------------------------
 Seq Scan on test_table
     (cost=0.00..36891.39 rows=197642 width=168)
     (actual time=0.016..645.543 rows=200000 loops=1)
   Filter: (id <= 200000)
 Total runtime: 869.106 ms
(3 rows)

Poznámka: Hraniční hodnoty při jejichž překročení dochází k výběru různých execution planů velmi úzce souvisí s vypočteným odhadem nákladů, a tedy (a) se statistikami tabulek a (b) se konstantami určujícími ceny základních operací. Pokud máte nastaveny jiné hodnoty, bude se vaše databáze chovat jinak - pro výše uvedené ukázky byly použity výchozí hodnoty, tj. zejména random_page_cost = 4 a seq_page_cost = 1, ale roli hrají i další *_cost proměnné - podrobnosti viz. dokumentace.

Co z toho všeho plyne? Rozhodně neplatí že sekvenční sken je zlo které byste měli eliminovat za každou cenu. V některých situacích je sekvenční sken skutečně rychlejší a hospodárnější než použití indexu. Pokud máte podezření že ve vašem přípdě by index scan byl rychlejší, je třeba to ověřit a pokud se to potvrdí zjednat nápravu.

Ověření není obtížné - stačí v session "vypnout" sekvenční sken, a zkusit spustit dotaz - měl by se použít index scan a vy okamžitě uvidíte jestli sekvenční sken lepší je nebo není. Pokud je index scan (výrazně) rychlejší, je třeba zjednat nápravu. Zaprvé si můžete pohrát s přesnějším nastavením *_cost proměnných v postgresql.conf (ale buďte si vědomi že tím ovlivníte i všechny ostatní dotazy a pokud zvolíte špatné hodnoty můžete je "rozbít"). Tuto změnu tedy musíte dobře otestovat a ověřit že se skutečně jedná o úpravy vedoucí ke zpřesnění odhadů (a tedy volbě správného execution planu).

Zadruhé můžete "fixnout" pouze daný dotaz tím že před ním *_cost proměnné nastavíte na vhodné hodnoty, vypnete sekvenční sken a podobně. To ale nepovažuji za dobrý postup - sice tak neovlivníte ostatní dotazy (ty budou i nadále používat hodnoty z postgresql.conf), ale do aplikace tím zanášíte nepatřičné informace o hardware a podobně. Co se stane až budete migrovat na nový server? Budete procházet zdrojáky a měnit tyto hodnoty?

Třetí možnost představuje takový cimrmanovský "krok stranou" - pokud váš dotaz obsahuje několik podmínek na danou tabulku, zkuste najít nějakou (klidně i nad více sloupci) s větší selektivitou a definujte index nad ní.

Uvažujme například tabulku se dvěma nezávisle oindexovanými numerickými sloupci obsahujícími hodnoty 0..9 v náhodných kombinacích:

CREATE TABLE test_table (id1 INTEGER, id2 INTEGER);
INSERT INTO test_table SELECT mod(i, 10), round(random() * 9)
                         FROM generate_series(1,1000000) AS s(i)
CREATE INDEX test_table_idx1 ON test_table(id1);
CREATE INDEX test_table_idx2 ON test_table(id2);
ANALYZE test_table;

a podívejme se na dotaz

SELECT * FROM test_table WHERE id1 < 5 AND id2 < 5;

Bohužel, 50% selektivita nad každým ze sloupců nestačí a použije se tedy sekvenční sken:

db=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE id1 < 5 AND id2 < 5;

                              QUERY PLAN
----------------------------------------------------------------------
 Seq Scan on test_table
     (cost=0.00..28275.00 rows=256002 width=8)
     (actual time=80.712..682.514 rows=224585 loops=1)
   Filter: ((id1 < 5) AND (id2 < 5))
 Total runtime: 963.165 ms
(3 rows)

Pokud ale definujeme složený index nad oběma sloupci zároveň, situace se změní - namísto 50% selektivity najednou máme 25%, a index vítězí:

db=# CREATE INDEX test_table_idx ON test_table (id1, id2);
db=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE id1 < 5 AND id2 < 5;

                              QUERY PLAN
----------------------------------------------------------------------
 Bitmap Heap Scan on test_table
        (cost=10432.36..27547.39 rows=256002 width=8)
        (actual time=76.400..400.486 rows=224585 loops=1)
   Recheck Cond: ((id1 < 5) AND (id2 < 5))
   ->  Bitmap Index Scan on test_table_idx
              (cost=0.00..10368.36 rows=256002 width=0)
              (actual time=75.115..75.115 rows=224585 loops=1)
         Index Cond: ((id1 < 5) AND (id2 < 5))
 Total runtime: 657.373 ms
(5 rows)

Sice ne o mnoho, ale v praxi bývají výsledky výrazně lepší.

Špatně zformulovaná podmínka

Další možnou příčinou - a tady už se jedná vyloženě o chybu člověka který psal daný SQL dotaz - je chybná formulace podmínky, která nechtěně brání použití indexu. Uvažme v podstatě "dokonalou" tabulku

CREATE TABLE test_table (id INTEGER, value VARCHAR(255));
INSERT INTO test_table SELECT i, REPEAT(MD5(i::text), 5)
                         FROM generate_series(1,1000000) AS s(i);
ALTER TABLE test_table ADD PRIMARY KEY (id);
ANALYZE test_table;

A nyní si ukažme několik velmi hloupých SQL dotazů nad jejím primárním klíčem:

SELECT * FROM test_table WHERE id + 1 = 233449;
SELECT * FROM test_table WHERE id * id = 54756;
SELECT * FROM test_table WHERE id = 1::double;

Jsou to dotazy nad primárním klíčem, takže budou rychlé, že? Omyl - všechny spadnou na sekvenční sken tabulky o 1 milionu řádků a budou o několik řádů pomalejší. Problém je v tom že na levé straně podmínky není samotný sloupec ale výraz - v prvním případě je to aritmetická operace "plus", ve druhém druhá mocnina, ve třetím dokonce přetypování.

Existují v podstatě dva způsoby jak toto "opravit": (a) přeformulovat podmínky tak aby v podmínce na jedné straně figuroval samotný sloupec což umožní aplikaci indexu, a (b) vytvořit index nad výrazem.

Přeformulování podmínek je většinou výrazně jednodušší a efektivnější, ale ne vždy je to možné - v případě prvních dvou výše uvedených dotazů je přepis jednoduchý:

SELECT * FROM test_table WHERE id = 233448;
SELECT * FROM test_table WHERE id = sqrt(54756);
SELECT * FROM test_table WHERE id = 1;

Na podobné problémy velmi často narážím při řešení "chytrých" dotazů pracujících s datumy a časovými údaji obecně. Uvažujme tabulku

CREATE TABLE test_table (id INTEGER, value TIMESTAMP);
INSERT INTO test_table (id, value)
     SELECT i, to_timestamp(1248559618 + i * RANDOM())
       FROM generate_series(1,1000000) s(i);
CREATE INDEX test_table_idx ON test_table (value);
ANALYZE test_table;

Bohužel až příliš často narážím na dotazy typu (oba dělají to samé - vyberou záznamy pouze k jedinému dni):

-- načti záznamy pro daný den
SELECT * FROM test_table WHERE value LIKE '2009-08-%';
SELECT * FROM test_table WHERE date(value) = '2009-08-01';

-- načti záznamy za posledních 15 minut
SELECT * FROM test_table WHERE value - now() <= interval '15 minutes';

které všechny "spadnou" na sekvenční sken, což vzhledem k velikosti tabulky není dobré. A stejně jako v předchozím případě není těžké je přepsat tak aby se použil index:

-- první dva dotazy mají po opravě stejný tvar 
SELECT * FROM test_table WHERE value >= '2009-08-01'
                           AND value <  '2009-08-02';

-- třetí dotaz po opravě
SELECT * FROM test_table WHERE value <= now() + interval '15 minutes';

Existuje asi tak milion dalších obdobných způsobů jak zabránit použití indexů, ale pro ilustraci to myslím stačí.