Inlined WITH Queries (Common Table Expressions)
Recorded source states and original language descriptions. Missing version evidence stays unknown.
The source snapshot records transitions for PostgreSQL 8.1–18. The historical matrix records independent cells for 7.4–18. Neither records support for PostgreSQL 19 or 20; newer versions remain unknown. These source claims are distinct from runtime measurements.
Performance
Description
A WITH query that is neither recursive nor has any side-effects (e.g. an INSERT/UPDATE/DELETE) can be executed inline, which can lead to performance improvements. This behavior can be forced on a query by using the "NOT MATERIALIZED" clause, e.g.
WITH cte AS NOT MATERIALIZED (
SELECT * FROM a
)
SELECT * FROM cte
JOIN b ON b.id = cte.id;
For more information, please visit https://www.postgresql.org/docs/12/queries-with.html
Selected version: PostgreSQL 18
Yes
Compare versions
Original documentation links
Recorded legacy identifiers: 321
Complete source facts and provenance
yaml · center · 1dd22bf9a7f3ec325b894e2836fda535a25529f246a94dd41d6abbfe9997f7fd
{
"Key": "inlined-with-queries-common-table-expressions",
"SourceKind": "yaml",
"SourceDatabase": "center",
"SourceTable": "featurematrix.yaml",
"SourceKey": "Inlined WITH Queries (Common Table Expressions)",
"SourceHash": "1dd22bf9a7f3ec325b894e2836fda535a25529f246a94dd41d6abbfe9997f7fd",
"Name": "Inlined WITH Queries (Common Table Expressions)",
"NameLocale": "en",
"GroupKey": "performance",
"GroupName": "Performance",
"GroupLocale": "en",
"Ordinal": 24,
"GroupOrder": 5,
"Facts": {
"feature": {
"description": "A WITH query that is neither recursive nor has any side-effects (e.g. an INSERT/UPDATE/DELETE) can be executed inline, which can lead to performance improvements. This behavior can be forced on a query by using the \"NOT MATERIALIZED\" clause, e.g.\r\n\r\n```\r\nWITH cte AS NOT MATERIALIZED (\r\n SELECT * FROM a\r\n)\r\nSELECT * FROM cte\r\nJOIN b ON b.id = cte.id;\r\n```\r\n\r\nFor more information, please visit [https://www.postgresql.org/docs/12/queries-with.html](https://www.postgresql.org/docs/12/queries-with.html)",
"name": "Inlined WITH Queries (Common Table Expressions)",
"versions": {
"12": "Yes"
}
},
"group": "Performance",
"versions": {
"max": 18,
"min": 8.1
}
},
"Cells": [
{
"Version": "10",
"State": "no",
"EvidenceKind": "yaml-default-no",
"TransitionVersion": "",
"Supported": false,
"Original": "No"
},
{
"Version": "11",
"State": "no",
"EvidenceKind": "yaml-default-no",
"TransitionVersion": "",
"Supported": false,
"Original": "No"
},
{
"Version": "12",
"State": "yes",
"EvidenceKind": "yaml-transition",
"TransitionVersion": "12",
"Supported": true,
"Original": "Yes"
},
{
"Version": "13",
"State": "yes",
"EvidenceKind": "yaml-transition",
"TransitionVersion": "12",
"Supported": true,
"Original": "Yes"
},
{
"Version": "14",
"State": "yes",
"EvidenceKind": "yaml-transition",
"TransitionVersion": "12",
"Supported": true,
"Original": "Yes"
},
{
"Version": "15",
"State": "yes",
"EvidenceKind": "yaml-transition",
"TransitionVersion": "12",
"Supported": true,
"Original": "Yes"
},
{
"Version": "16",
"State": "yes",
"EvidenceKind": "yaml-transition",
"TransitionVersion": "12",
"Supported": true,
"Original": "Yes"
},
{
"Version": "17",
"State": "yes",
"EvidenceKind": "yaml-transition",
"TransitionVersion": "12",
"Supported": true,
"Original": "Yes"
},
{
"Version": "18",
"State": "yes",
"EvidenceKind": "yaml-transition",
"TransitionVersion": "12",
"Supported": true,
"Original": "Yes"
},
{
"Version": "8.1",
"State": "no",
"EvidenceKind": "yaml-default-no",
"TransitionVersion": "",
"Supported": false,
"Original": "No"
},
{
"Version": "8.2",
"State": "no",
"EvidenceKind": "yaml-default-no",
"TransitionVersion": "",
"Supported": false,
"Original": "No"
},
{
"Version": "8.3",
"State": "no",
"EvidenceKind": "yaml-default-no",
"TransitionVersion": "",
"Supported": false,
"Original": "No"
},
{
"Version": "8.4",
"State": "no",
"EvidenceKind": "yaml-default-no",
"TransitionVersion": "",
"Supported": false,
"Original": "No"
},
{
"Version": "9.0",
"State": "no",
"EvidenceKind": "yaml-default-no",
"TransitionVersion": "",
"Supported": false,
"Original": "No"
},
{
"Version": "9.1",
"State": "no",
"EvidenceKind": "yaml-default-no",
"TransitionVersion": "",
"Supported": false,
"Original": "No"
},
{
"Version": "9.2",
"State": "no",
"EvidenceKind": "yaml-default-no",
"TransitionVersion": "",
"Supported": false,
"Original": "No"
},
{
"Version": "9.3",
"State": "no",
"EvidenceKind": "yaml-default-no",
"TransitionVersion": "",
"Supported": false,
"Original": "No"
},
{
"Version": "9.4",
"State": "no",
"EvidenceKind": "yaml-default-no",
"TransitionVersion": "",
"Supported": false,
"Original": "No"
},
{
"Version": "9.5",
"State": "no",
"EvidenceKind": "yaml-default-no",
"TransitionVersion": "",
"Supported": false,
"Original": "No"
},
{
"Version": "9.6",
"State": "no",
"EvidenceKind": "yaml-default-no",
"TransitionVersion": "",
"Supported": false,
"Original": "No"
}
],
"Texts": [
{
"Locale": "en",
"Title": "Inlined WITH Queries (Common Table Expressions)",
"BodyHTML": "\u003cp\u003eA WITH query that is neither recursive nor has any side-effects (e.g. an INSERT/UPDATE/DELETE) can be executed inline, which can lead to performance improvements. This behavior can be forced on a query by using the \u0026#34;NOT MATERIALIZED\u0026#34; clause, e.g.\u003c/p\u003e\n\u003cpre\u003e\u003ccode\u003eWITH cte AS NOT MATERIALIZED (\n SELECT * FROM a\n)\nSELECT * FROM cte\nJOIN b ON b.id = cte.id;\n\u003c/code\u003e\u003c/pre\u003e\n\u003cp\u003eFor more information, please visit \u003ca href=\"https://www.postgresql.org/docs/12/queries-with.html\" rel=\"nofollow\"\u003ehttps://www.postgresql.org/docs/12/queries-with.html\u003c/a\u003e\u003c/p\u003e\n",
"SourceHash": "1dd22bf9a7f3ec325b894e2836fda535a25529f246a94dd41d6abbfe9997f7fd",
"ContentHash": "70fb334a930fc85c35a7ee7ab8c135d7c2bffeb26fd3c1f2e1d4ddd5a863fb30",
"Payload": {
"description": "A WITH query that is neither recursive nor has any side-effects (e.g. an INSERT/UPDATE/DELETE) can be executed inline, which can lead to performance improvements. This behavior can be forced on a query by using the \"NOT MATERIALIZED\" clause, e.g.\r\n\r\n```\r\nWITH cte AS NOT MATERIALIZED (\r\n SELECT * FROM a\r\n)\r\nSELECT * FROM cte\r\nJOIN b ON b.id = cte.id;\r\n```\r\n\r\nFor more information, please visit [https://www.postgresql.org/docs/12/queries-with.html](https://www.postgresql.org/docs/12/queries-with.html)",
"name": "Inlined WITH Queries (Common Table Expressions)",
"versions": {
"12": "Yes"
}
}
},
{
"Locale": "zh-Hans",
"Title": "内联 WITH 查询 (公用表表达式)",
"BodyHTML": "\u003cp\u003e既非递归也没有任何副作用（例如 INSERT/UPDATE/DELETE）的 WITH 查询可以内联执行，从而带来性能改进。可以通过使用 \u0026#34;NOT MATERIALIZED\u0026#34; 子句强制对查询执行此行为，例如：\u003c/p\u003e\n\u003cpre\u003e\u003ccode\u003eWITH cte AS NOT MATERIALIZED (\n SELECT * FROM a\n)\nSELECT * FROM cte\nJOIN b ON b.id = cte.id;\n\u003c/code\u003e\u003c/pre\u003e\n\u003cp\u003e更多信息请访问 \u003ca href=\"https://www.postgresql.org/docs/12/queries-with.html\" rel=\"nofollow\"\u003ehttps://www.postgresql.org/docs/12/queries-with.html\u003c/a\u003e\u003c/p\u003e\n",
"SourceHash": "071091951021357a5f492adb04caed6febf4d315ac2626ca027a48dd8ec01d50",
"ContentHash": "79b15445a9e0098c663415436419f09c9c2aa59a1307ab0548f1bb152d9e985c",
"Payload": {
"description": "既非递归也没有任何副作用（例如 INSERT/UPDATE/DELETE）的 WITH 查询可以内联执行，从而带来性能改进。可以通过使用 \"NOT MATERIALIZED\" 子句强制对查询执行此行为，例如：\n\n```\nWITH cte AS NOT MATERIALIZED (\n SELECT * FROM a\n)\nSELECT * FROM cte\nJOIN b ON b.id = cte.id;\n```\n\n更多信息请访问 [https://www.postgresql.org/docs/12/queries-with.html](https://www.postgresql.org/docs/12/queries-with.html)",
"name": "内联 WITH 查询 (公用表表达式)"
}
}
],
"GroupTitles": {
"en": "Performance",
"zh-Hans": "性能"
},
"LegacyIDs": [
"321"
]
}