Cette requête chargera-t-elle toute la table en memory

Si j'ai une très grande table, cette requête chargera toute la table en memory avant de filterr les réinitialisations:

with parent as ( select * from a101 ) select * from parent where value1 = 159 

Comme vous pouvez le voir, la requête parent fait reference à toute la table. Est-ce que cela sera chargé en memory. Ceci est une version très simplifiée de la requête. La requête réelle a quelques jointures à d'autres tables. J'évalue sql server 2012 et postgrsql.

Dans PostgreSQL (true à partir de 9.4, au less), les CTE agissent comme des clôtures d'optimization .

L'optimiseur de requête n'affecte pas les termes CTE dans la requête externe, n'applique pas les qualificatifs, ni n'élève les qualificatifs, même dans des cas sortingviaux. Ainsi, un SELECT non qualifié à l'intérieur d'un terme CTE effectuera toujours une parsing de table complète (ou une parsing d'index uniquement s'il existe un index approprié).

Ainsi, dans PostgreSQL, ces deux choses sont très différentes, comme le montrerait un simple EXPLAIN :

 with parent as ( select * from a101 ) select * from parent where value1 = 159 

et

 SELECT * FROM ( SELECT * FROM a101 ) AS parent WHERE value1 = 159; 

Cependant, cela "va scanner toute la table" ne signifie pas nécessairement "chargera toute la table en memory". PostgreSQL utilisera un TupleStore, qui se répandra de manière transparente dans un file temporaire sur le disque à mesure qu'il s'agrandit.

La justification initiale était que la DML en termes de CTE était planifiée (et mise en œuvre par la suite). S'il y a du DML dans un terme CTE, il est vital que son exécution soit prévisible et complète. Cela peut également être vrai si le CTE appelle des fonctions de modification de données.

Malheureusement, personne ne semble avoir pensé "… mais si c'est juste un SELECT et que nous voulons l'intégrer?" .

Beaucoup dans la communauté semblent voir cela comme une caractéristique et la promulguer régulièrement comme une solution de contournement pour les problèmes d'optimiseur. Je trouve cette attitude complètement perplexe. Par conséquent, il sera très difficile de résoudre ce problème plus tard, car les personnes utilisent intentionnellement des CTE lorsqu'elles veulent empêcher l'optimiseur de modifier une requête.

En d'autres termes, PostgreSQL utilise abusivement les CTE comme des pseudo-requêtes-hints (avec le hack OFFSET 0 ), car la politique du projet indique que de véritables conseils de requête ne sont pas souhaités ou supportés.

AFAIK MS SQL Server peut optimiser les barrières CTE, mais peut également choisir de matérialiser un set de résultats.

Je viens de faire EXPLAIN pour cette requête dans PostgreSQL. Étonnamment, il effectue l'parsing de la séquence au lieu de la search d'index:

  CTE Scan on parent (cost=123.30..132.97 rows=2 width=1711) Filter: (value1 = 159) CTE parent -> Seq Scan on a101 (cost=0.00..123.30 rows=430 width=2060) 

J'ai un index de key primaire sur value1 et il est utilisé pour select * from a101 where value1 = 159 simplement select * from a101 where value1 = 159 query.

Donc, la réponse est qu'il va scanner toute la table. Je suis surpris, je pensais que cela fonctionnerait comme une vue ou sous-requête, mais ce n'est pas le cas. Vous pouvez utiliser ceci pour utiliser l'index:

 select * from (select * from a101) parent where value1 = 159` 

Les requêtes sont évaluées dans leur set. Si vous regardez le plan d'exécution, vous verrez que le prédicat de filter sera appliqué à la search interne. Malgré la sortingvialité de la search extérieure, je suis sûr que cela sera optimisé.

Vérifiez les plans d'exécution – il s'agit de connaissances de base et il vaut mieux apprendre à le faire rapidement. Une fois que vous rencontrez un problème de performance réel, vous devrez find le problème et c'est là qu'interviennent les plans d'exécution.

Les CTE sont juste une syntaxe de langage pour rendre le code plus lisible, ils n'ont aucun impact sur les performances d'exécution des requêtes.

Lorsque la requête est exécutée, elle sera exécutée en fonction des phases d'exécution de requête SQL Server prédéfinies

 1. FROM 2. ON 3. OUTER 4. WHERE 5. GROUP BY 6. CUBE | ROLLUP 7. HAVING 8. SELECT 9. DISTINCT 10 ORDER BY 11. TOP 

Donc, où le filter sera appliqué en premier, puis les loggings seront sélectionnés afin que la table complète ne soit pas récupérée en memory.