4.06 Můj dotaz je pomalý a nepoužívá vytvořené indexy. Proč?

Každý dotaz nemusí nutně použít existující indexy. Index se použije tehdy, když je tabulka větší než určitá minimální velikost, a dotaz vybírá pouze procentuálně malou část řádků tabulky. To proto, že náhodný přístup k disku daný čtením indexu může být pomalejší než lineární čtení tabulky nebo sekvenční čtení.

PostgreSQL rozhoduje o použití indexů na základě statistiky přístupů k tabulce. Tyto statistiky se shromažďují příkazy VACUUM ANALYZE nebo ANALYZE. Díky statistikám má optimizer informaci o počtu řádek v tabulce a může lépe rozhodnout o použití indexů. Statistiky se uplatní při určení optimálního pořadí a metody spojení tabulek. Statistiky by se měli aktualizovat opakovaně, tak jak se mění obsah tabulek.

Indexy nejsou obyčejně použity pro setřídění nebo spojení tabulek. Sekvenční zpracování následované explicitním tříděním je obyčejně rychlejší než použití indexu na velké tabulce.

Jinak je tomu v případě použití LIMIT a ORDER BY, při kterém se většinou index použije, jelikož je výsledkem pouze malá část tabulky.

Pokud si myslíte, že optimizer mylně zvolil sekvenční prohledávání tabulky, použijte příkaz SET enable_seqscan TO 'off' a zkuste zda je prohledávání s indexem rychlejší.

Při vyhledávání na základě vzoru jako je např. operátor LIKE nebo ~ se indexy použijí pouze za určitých skutečností:

• začátek hledaného vzoru musí být ukotven k začátku, tj.
  • vzor LIKE nesmí začínat %
  • ~ regulární výraz musí začínat ^
• vzor nesmí začínat intervalem, např. [a-e]
• vyhledávaní, které není Case sensitiv jako je ILIKE nebo ~* nepoužívá indexy. Můžete ale použít funkcionální indexy, které jsou popsány v sekci 4.8
• při inicializaci databáze (initdb) musí být použito C locale nebo vytvořte speciální text_pattern_index, který umožní, při respektování zmíněných podmínek použití indexu operací LIKE. Pro vyhledávání celých slov je možné a výhodné použít fulltext.