Recipes pro infirmis SQL Queries

Menses abhinc denuntiavimus explain.tensor.ru - public servitium pro parsing et visualising query consilia ad PostgreSQL.

Iam plus quam 6000 vicibus usus es, sed una pluma habilis quae fortasse non latuit est structuram principalem extarede quo vide aliquid simile;

Audi illos, et petitiones tuae "lenis et sericeus fiet." 🙂

Sed graviter, multae condiciones quae rogant tardum et promptum esurientem sunt typicam et cognosci potest structura et notitia consilii.

Hoc in casu, unusquisque homo elit optionem optimam in se quaerere non debet, sola eius experientia fretus - dicere ei possumus quid hic agatur, quid sit ratio; quam accedere ad solutionem. Id fecimus.

Ad has causas propius inspiciamus - quomodo definiantur et quae commendationes ducunt.

Ut melius te in themate immergas, primum potes audire truncum respondentem e mea fama apud PGConf.Russia 2020et tunc demum progrediuntur ad accuratam cuiusque exempli analysim:

#1: index "undersorting"

cum facit

Monstra cautionem novissimam clienti "LLC Kolokolchik".

Quam ad identify?

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

suasiones

Index usus expand generis agri.

exempli gratia:

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;

[Aspice explain.tensor.ru]

Statim animadvertere tabulas plusquam 100 ex indice detractas esse, quae tum digestae sunt, tum solum relictum esse.

Corrigendum:

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

[Aspice explain.tensor.ru]

Etiam in tali exemplo primitivo - 8.5 temporibus citius ac XXXIII temporibus paucioribus legit. Quo magis res "pro unoquoque valore" habes, eo manifestius effectum fk.

Adnoto talem indicem operari ut "praepositionem" indicem non deteriorem quam ante in aliis quaestionibus cum fk, ubi digerere pk non erat et non est (plus de hoc legere potes in meo articulo de inveniendo inefficax indexes). Complectens, normalis providebit expressa aliena clavis auxilio in hoc campo.

#2: index intersectio (BitmapAnd)

cum facit

Monstra omnia pacta clienti "LLC Kolokolchik", conclusa pro "NAO Buttercup".

Quam ad identify?

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

suasiones

partum compositum index per prata ab utroque originali vel unum capitulum entium cum pratis de secundo.

exempli gratia:

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); -- отбор по конкретной паре

[Aspice explain.tensor.ru]

Corrigendum:

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

[Aspice explain.tensor.ru]

Payoff hic minor est, cum Bitmap acervus Scan est per se valde efficax. Sed usquam 7 temporibus citius ac XXXIII temporibus paucioribus legit.

#3: Merge index (BitmapOr)

cum facit

Ostende primum XX vetustissimas "nos" seu petitiones nullas sine ordine ad expediendas, cum tua in prioritate.

Quam ad identify?

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

suasiones

ad usum CONIUGATIO [OMNES] subquerias pro singulis condicionibus OR caudices componere.

exempli gratia:

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;

[Aspice explain.tensor.ru]

Corrigendum:

(
  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, больше и не надо

[Aspice explain.tensor.ru]

Nos eo quod omnes 20 tabulae inquisitae statim in primo trunco ​​receptae sunt, ita secunda, cum magis "pretiosa" Bitmap acervo Scan, ne in fine quidem exsecutus est. 22x citius, 44x pauciores legit!

A accuratiore fabula de hac optimization methodo per exempla specifica potest legi in vasa PostgreSQL Antipatterns: Noxia JOINs et ORs и PostgreSQL Antipatterna: narratio de elegantia iterativae investigationis nominatim vel "Optimizationis ultro citroque".

Generativus version ex pluribus claves electionem iussit (and not just the const/nULL pair) discussed in the article SQL HowTo: scribe directe in interrogatione vel "elementarii tres modos".

# IV: Non multum supervacuis legitur

cum facit

Pro regula oritur cum "alitum colum affigere" petitioni iam existente.

"Et non eandem habes, sed" cum matre-of-conchis bullarum? » film "Diammond Arm"

Exempli gratia, negotium supra modificando, primas 20 vetustissimas "criticas" petitiones monstrant pro dispensando, cuiuscumque propositi.

Quam ad identify?

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

suasiones

Create [more] specialized index ubi conditio aut additis agris in indice.

Si conditio colum est "stabilis" ad proposita tua - hoc est non importat expansionem index valorum in futuro - melius est uti WHERE index. Varii statuses boolean/enum huic categoriae bene conveniunt.

Si conditio eliquare non potest accipere de diversis significationibusergo melius est cum his agris indicem amplificare - sicut in situ cum BitmapAnd supra.

exempli gratia:

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;

[Aspice explain.tensor.ru]

Corrigendum:

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

[Aspice explain.tensor.ru]

Ut videre potes, eliquatio e consilio penitus evanuit et petitio facta est V temporibus citius.

#5:

cum facit

Varii conatus ad negotium tuum processui queue creandum, cum magnus numerus tabularum updates/deletionum in mensa ducunt ad rerum condicionem permultarum "mortuarum" monumentorum.

Quam ad identify?

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

suasiones

Extra manually regularly VACUUM [FULL] aut satis frequentes disciplina autovacuum by-tuning ejus parametri, comprehendo in propria mensa.

Pleraque huiusmodi problemata ex compositione interrogationis pauperis causantur, cum vocantem a logica negotiandi, sicut in tractatibus PostgreSQL Antipatterns: pugnae multitudinem "mortuum".

Sed necesse est ut etiam VACUUM PLENA semper adiuvet. Talibus in casibus dignum est algorithmum ex articulo cognoscere DBA: cum deficit VACUUM, mensam manually purgamus.

#6: Lectio "medii" indicis

cum facit

Videtur quod paululum legamus, et omnia indicentur, et aliquem non nimis eliquamus - sed tamen signanter plures paginas legimus quam velimus.

Quam ad identify?

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

suasiones

Prope inspice structuram indicis adhibitae et clavium agrorum in interrogatione - maxime probabile pars indicem non est certa. Verisimile erit tibi similem indicem creare, sine praepositione agrorum vel discere ad iterandum ipsorum values.

exempli gratia:

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;

[Aspice explain.tensor.ru]

Omnia denique videntur etiam secundum indicem, sed suspecta quodammodo est - pro singulis viginti libris legitur, habuimus detrahere 20 paginas notitiarum, 4KB per recordum - nonne audax est? Nomen autem index tbl_fk_org_fk_cli_idx cogitatione exasperans.

Corrigendum:

CREATE INDEX ON tbl(fk_cli);

[Aspice explain.tensor.ru]

Subito - 10 times velocius, et 4 minus legere!

Alia exempla casuum usui inefficaci indicium videri possunt in articulo DBA: inutile index.

#7: CTE CTE

cum facit

In request notatos "adipem" CTE ex diversis tabulis et inter eas facere placuit JOIN.

Casus pertinet pro versionibus infra v12 vel petitionibus cum WITH MATERIALIZED.

Quam ad identify?

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

suasiones

Diligenter resolvere petitionem - et Num hic omnino opus est CTES?? Si sic, tunc applicare "dictionnaire" in hstore/json secundum exemplar describit in Antipatterns PostgreSQL: scriptor ledo grave JOIN cum dictionary.

# VIII: VERTO ad disci (temp scriptum)

cum facit

Processus unius temporis (volutationis vel singularizationis) ex permultis monumentis in memoriam hoc partita non convenit.

Quam ad identify?

-> *
   && temp written > 0

suasiones

Si quantitas memoriae ab operatione adhibita non multum excedit valorem determinatum parametri work_memid emendandum est. Protinus in config pro omnibus potes, vel per potes SET [LOCAL] ad specifica petitionem / transaction.

exempli gratia:

SHOW work_mem;
-- "16MB"

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

[Aspice explain.tensor.ru]

Corrigendum:

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

[Aspice explain.tensor.ru]

Ob rationes manifestas, si memoria tantum adhibetur et non disco, quaesitio multo citius exsecuta erit. Eodem tempore etiam pars oneris ab HDD tollitur.

Sed intelligere debes quod sortes et sortes memoriae semper collocare non possis - simpliciter omnibus satis non erit.

#9: irrelevant statistics

cum facit

Multum in datorum statim effuderunt, sed illud tempus non fuit ANALYZE.

Quam ad identify?

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

suasiones

Extra, ANALYZE.

Haec res fusius describitur PostgreSQL Antipatterns: statistica omnia.

# X "aliquid abiit iniuriam"

cum facit

Exspectata erat cincinno a petitione certandi imposito vel copiae ferrariae CPU/hypervisoris insufficiens erant.

Quam ad identify?

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

suasiones

Utere externum magna ratio server consequat vel alius resource consummatio. Iam locuti sumus de nostra versione ordinandi hunc processum pro centum servientibus hic и hic.

Source: www.habr.com