🥇Antimodèles PostgreSQL : CTE x CTE

En raison de mon métier, je dois faire face à des situations où un développeur rédige une demande et pense «La base est intelligente, elle peut tout gérer elle-même !«

Dans certains cas (en partie par méconnaissance des capacités de la base de données, en partie par optimisations prématurées), cette approche conduit à l'apparition de « Frankenstein ».

Tout d'abord, je vais donner un exemple d'une telle demande :

-- для каждой ключевой пары находим ассоциированные значения полей
WITH RECURSIVE cte_bind AS (
  SELECT DISTINCT ON (key_a, key_b)
    key_a a
  , key_b b
  , fld1 bind_fld1
  , fld2 bind_fld2
  FROM
    tbl
)
-- находим min/max значений для каждого первого ключа
, cte_max AS (
  SELECT
    a
  , max(bind_fld1) bind_fld1
  , min(bind_fld2) bind_fld2
  FROM
    cte_bind
  GROUP BY
    a
)
-- связываем по первому ключу ключевые пары и min/max-значения
, cte_a_bind AS (
  SELECT
    cte_bind.a
  , cte_bind.b
  , cte_max.bind_fld1
  , cte_max.bind_fld2
  FROM
    cte_bind
  INNER JOIN
    cte_max
      ON cte_max.a = cte_bind.a
)
SELECT * FROM cte_a_bind;

Pour évaluer de manière substantielle la qualité d'une requête, créons un ensemble de données arbitraires :

CREATE TABLE tbl AS
SELECT
  (random() * 1000)::integer key_a
, (random() * 1000)::integer key_b
, (random() * 10000)::integer fld1
, (random() * 10000)::integer fld2
FROM
  generate_series(1, 10000);
CREATE INDEX ON tbl(key_a, key_b);

Il se trouve que la lecture des données a pris moins d'un quart du temps exécution de la requête :

[regardez expliquer.tensor.ru]

Le démonter pièce par pièce

Examinons la demande de plus près et soyons perplexes :

Pourquoi With RECURSIVE est-il ici s'il n'y a pas de CTE récursifs ?
Pourquoi regrouper les valeurs min/max dans un CTE distinct si elles sont ensuite liées à l'échantillon d'origine de toute façon ?
+25% de temps
Pourquoi utiliser un 'SELECT * FROM' inconditionnel à la fin pour répéter le CTE précédent ?
+14% de temps

Dans ce cas, nous avons eu beaucoup de chance que Hash Join ait été choisi pour la connexion, et non Nested Loop, car nous aurions alors reçu non pas un seul pass CTE Scan, mais 10K !

un peu sur le CTE ScanIci, nous devons nous rappeler que CTE Scan est similaire à Seq Scan - c'est-à-dire pas d'indexation, mais seulement une recherche complète, ce qui nécessiterait 10 0.3 x XNUMX ms = 3000ms pour les cycles par cte_max ou 1 1.5 x XNUMX ms = 1500ms lors d'une boucle par cte_bind!
Au fait, que souhaitiez-vous obtenir comme résultat ? Oui, c'est généralement la question qui revient quelque part dans la 5ème minute d'analyse des requêtes « à trois étages ».

Nous voulions obtenir un résultat pour chaque paire de clés unique min/max du groupe par key_a.
Alors utilisons-le pour ça fonctions de fenêtre:

SELECT DISTINCT ON(key_a, key_b)
	key_a a
,	key_b b
,	max(fld1) OVER(w) bind_fld1
,	min(fld2) OVER(w) bind_fld2
FROM
	tbl
WINDOW
	w AS (PARTITION BY key_a);

[regardez expliquer.tensor.ru]

Étant donné que la lecture des données dans les deux options prend environ 4 à 5 ms, nous gagnons alors tout notre temps. -32% - c'est dans sa forme la plus pure charge supprimée du processeur de base, si une telle requête est exécutée assez souvent.

En général, il ne faut pas forcer la base à « porter le rond, faire rouler le carré ».

Source: habr.com

Antimodèles PostgreSQL : CTE x CTE

Le démonter pièce par pièce

Ajouter un commentaire Annuler la réponse