Recepty na churavějící SQL dotazy

Před měsíci oznámili jsme vysvětlit.tensor.ru - veřejnost služba pro analýzu a vizualizaci plánů dotazů do PostgreSQL.

Už jste to použili více než 6000 XNUMXkrát, ale jedna užitečná funkce, která možná zůstala nepovšimnuta, je strukturální stopy, které vypadají nějak takto:

Poslouchejte je a vaše požadavky budou „hladké a hedvábné“. 🙂

Ale vážně, v mnoha situacích je požadavek pomalý a náročný na zdroje jsou typické a lze je rozpoznat podle struktury a dat plánu.

V tomto případě nemusí každý jednotlivý vývojář sám hledat možnost optimalizace, spoléhat se pouze na své zkušenosti – můžeme mu říci, co se zde děje, co by mohlo být důvodem a jak přistupovat k řešení. To jsme udělali.

Pojďme se na tyto případy podívat blíže – jak jsou definovány a k jakým doporučením vedou.

Abyste se lépe ponořili do tématu, můžete si nejprve poslechnout odpovídající blok od moje zpráva na PGConf.Russia 2020a teprve poté přejděte k podrobné analýze každého příkladu:

#1: index „podřazení“

Když se objeví

Ukažte poslední fakturu pro klienta "LLC Kolokolchik".

Jak identifikovat

-> Limit
   -> Sort
      -> Index [Only] Scan [Backward] | Bitmap Heap Scan

Doporučení

Použitý index rozšířit o třídicí pole.

Příklad:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 1 -- отбор по конкретной связи
ORDER BY
  pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;

[podívejte se na explain.tensor.ru]

Okamžitě si můžete všimnout, že z indexu bylo odečteno více než 100 záznamů, které byly následně všechny setříděny a poté zůstal jediný.

Oprava:

DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки

[podívejte se na explain.tensor.ru]

I na tak primitivním vzorku - 8.5krát rychlejší a 33krát méně čtení. Čím více „faktů“ pro každou hodnotu máte, tím je efekt zjevnější fk.

Podotýkám, že takový index bude fungovat jako „prefixový“ index o nic horší než dříve pro jiné dotazy s fk, kde seřadit podle pk nebylo a není (můžete si o tom přečíst více v mém článku o hledání neúčinných indexů). Včetně bude poskytovat normální explicitní podpora cizích klíčů na tomto poli.

#2: průnik indexu (BitmapAnd)

Když se objeví

Zobrazit všechny smlouvy pro klienta „LLC Kolokolchik“, uzavřené jménem „NAO Buttercup“.

Jak identifikovat

-> BitmapAnd
   -> Bitmap Index Scan
   -> Bitmap Index Scan

Doporučení

vytvořit složený index o pole z obou původních nebo rozšířit jedno ze stávajících o pole z druhého.

Příklad:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "фактов"
, (random() *  100)::integer fk_org  -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  (fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре

[podívejte se na explain.tensor.ru]

Oprava:

DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);

[podívejte se na explain.tensor.ru]

Výplata je zde menší, protože Bitmap Heap Scan je docela efektivní sám o sobě. Ale stejně 7krát rychlejší a 2.5krát méně čtení.

#3: Sloučit indexy (BitmapOr)

Když se objeví

Zobrazte prvních 20 nejstarších „nás“ nebo nepřiřazených požadavků ke zpracování, přičemž vaše má prioritu.

Jak identifikovat

-> BitmapOr
   -> Bitmap Index Scan
   -> Bitmap Index Scan

Doporučení

Chcete-li použít UNION [VŠE] zkombinovat poddotazy pro každý z bloků NEBO podmínek.

Příklad:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own;

CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой

SELECT
  *
FROM
  tbl
WHERE
  fk_own = 1 OR -- свои
  fk_own IS NULL -- ... или "ничьи"
ORDER BY
  pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;

[podívejte se na explain.tensor.ru]

Oprava:

(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own = 1 -- сначала "свои" 20
  ORDER BY
    pk
  LIMIT 20
)
UNION ALL
(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own IS NULL -- потом "ничьи" 20
  ORDER BY
    pk
  LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо

[podívejte se na explain.tensor.ru]

Využili jsme toho, že všech 20 požadovaných záznamů bylo okamžitě přijato v prvním bloku, takže druhý, s „dražším“ Bitmap Heap Scan, se ani neprovedl - nakonec 22x rychlejší, 44x méně čtení!

Podrobnější příběh o této optimalizační metodě na konkrétních příkladech lze číst v článcích PostgreSQL Antipatterns: škodlivé JOINy ​​a OR и PostgreSQL Antipatterns: Příběh o iterativním zpřesňování vyhledávání podle názvu nebo „optimalizace tam a zpět“.

Zobecněná verze uspořádaný výběr na základě několika klíčů (a nejen páru const/NULL) je diskutováno v článku SQL HowTo: zápis smyčky while přímo v dotazu nebo „Základní tři kroky“.

#4: Čteme spoustu zbytečných věcí

Když se objeví

Zpravidla vzniká, když chcete „připojit další filtr“ k již existujícímu požadavku.

„A ty nemáš stejný, ale s perleťovými knoflíky? " film "Diamantové rameno"

Například úpravou výše uvedené úlohy zobrazte prvních 20 nejstarších „kritických“ požadavků na zpracování, bez ohledu na jejich účel.

Jak identifikovat

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rows < RRbF -- отфильтровано >80% прочитанного
   && loops × RRbF > 100 -- и при этом больше 100 записей суммарно

Doporučení

Vytvořte [více] specializované index s podmínkou WHERE nebo do indexu zahrnout další pole.

Pokud je stav filtru pro vaše účely "statický" - tzn neznamená expanzi seznam hodnot v budoucnu - je lepší použít index WHERE. Do této kategorie dobře zapadají různé booleovské/výčtové stavy.

Pokud je podmínka filtrování může nabývat různých významů, pak je lepší rozšířit index o tato pole – jako v situaci s BitmapAnd výše.

Příklad:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk -- 100K "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"

CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);

SELECT
  *
FROM
  tbl
WHERE
  critical
ORDER BY
  pk
LIMIT 20;

[podívejte se na explain.tensor.ru]

Oprava:

CREATE INDEX ON tbl(pk)
  WHERE critical; -- добавили "статичное" условие фильтрации

[podívejte se na explain.tensor.ru]

Jak vidíte, filtrování z plánu úplně zmizelo a požadavek se stal 5x rychleji.

#5: řídký stůl

Když se objeví

Různé pokusy o vytvoření vlastní fronty zpracování úloh, kdy velké množství aktualizací/mazání záznamů v tabulce vede k situaci velkého počtu „mrtvých“ záznamů.

Jak identifikovat

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

Doporučení

Pravidelně provádějte ručně VAKUUM [PLNÉ] nebo dosáhnout přiměřeně častého školení autovakuum doladěním jeho parametrů vč pro konkrétní stůl.

Ve většině případů jsou takové problémy způsobeny špatným složením dotazů při volání z obchodní logiky, jak je popsáno v PostgreSQL Antipatterns: boj s hordami „mrtvých“.

Ale musíte pochopit, že ani VACUUM FULL nemusí vždy pomoci. V takových případech stojí za to seznámit se s algoritmem z článku DBA: když selže VACUUM, vyčistíme stůl ručně.

#6: Čtení od „středu“ rejstříku

Když se objeví

Zdá se, že jsme málo četli a vše bylo indexováno a nikoho jsme nadbytečně nefiltrovali – ale přesto čteme podstatně více stránek, než bychom chtěli.

Jak identifikovat

-> Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

Doporučení

Podívejte se pozorně na strukturu použitého indexu a klíčová pole uvedená v dotazu – s největší pravděpodobností část indexu není nastavena. S největší pravděpodobností budete muset vytvořit podobný index, ale bez prefixových polí resp naučit se opakovat jejich hodnoty.

Příklad:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "фактов"
, (random() *  100)::integer fk_org  -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;

[podívejte se na explain.tensor.ru]

Všechno se zdá být v pořádku, dokonce i podle indexu, ale je to nějak podezřelé - pro každý z 20 přečtených záznamů jsme museli odečíst 4 stránky dat, 32 kB na záznam - není to tučné? A název indexu tbl_fk_org_fk_cli_idx k zamyšlení.

Oprava:

CREATE INDEX ON tbl(fk_cli);

[podívejte se na explain.tensor.ru]

Najednou - 10krát rychlejší a 4krát méně čtení!

Další příklady situací neefektivního využití indexů si můžete prohlédnout v článku DBA: hledání zbytečných indexů.

#7: CTE × CTE

Když se objeví

V žádosti dosáhl „tučného“ CTE z různých stolů a pak se rozhodl to udělat mezi nimi JOIN.

Případ je relevantní pro verze nižší než v12 nebo požadavky s WITH MATERIALIZED.

Jak identifikovat

-> CTE Scan
   && loops > 10
   && loops × (rows + RRbF) > 10000
      -- слишком большое декартово произведение CTE

Doporučení

Pečlivě analyzujte žádost - a Jsou zde CTE vůbec potřeba?? Pokud ano, pak použijte "slovník" v hstore/json podle vzoru popsaného v PostgreSQL Antipatterns: pojďme na to JOIN se slovníkem.

#8: swap na disk (temp zápis)

Když se objeví

Jednorázové zpracování (třídění nebo unifikace) velkého množství záznamů se nevejde do paměti k tomu určené.

Jak identifikovat

-> *
   && temp written > 0

Doporučení

Pokud množství paměti použité operací výrazně nepřekračuje zadanou hodnotu parametru work_mem, vyplatí se to opravit. Můžete okamžitě v konfiguraci pro všechny, nebo můžete projít SET [LOCAL] pro konkrétní požadavek/transakci.

Příklad:

SHOW work_mem;
-- "16MB"

SELECT
  random()
FROM
  generate_series(1, 1000000)
ORDER BY
  1;

[podívejte se na explain.tensor.ru]

Oprava:

SET work_mem = '128MB'; -- перед выполнением запроса

[podívejte se na explain.tensor.ru]

Ze zřejmých důvodů, pokud se používá pouze paměť a ne disk, bude dotaz proveden mnohem rychleji. Současně je také odstraněna část zátěže z HDD.

Ale musíte pochopit, že ne vždy budete moci alokovat spoustu a spoustu paměti - prostě nebude dost pro každého.

#9: irelevantní statistika

Když se objeví

Nasypali do databáze hodně najednou, ale neměli čas to zahnat ANALYZE.

Jak identifikovat

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && ratio >> 10

Doporučení

Proveďte to ANALYZE.

Tato situace je podrobněji popsána v PostgreSQL Antipatterns: statistiky jsou všechno.

#10: „něco se pokazilo“

Když se objeví

Čekalo se na uzamčení vyvolané konkurenčním požadavkem nebo byly nedostatečné hardwarové prostředky CPU/hypervizoru.

Jak identifikovat

-> *
   && (shared hit / 8K) + (shared read / 1K) < time / 1000
      -- RAM hit = 64MB/s, HDD read = 8MB/s
   && time > 100ms -- читали мало, но слишком долго

Doporučení

Použijte externí monitorovací systém server kvůli blokování nebo abnormální spotřebě zdrojů. Již jsme hovořili o naší verzi organizace tohoto procesu pro stovky serverů zde и zde.

Zdroj: www.habr.com