{"kind": "plan", "major": "18", "item": {"slug": "nested-loop", "name": "Nested Loop", "name_zh": "NestLoop", "category": "Join", "summary": "Rescans the inner plan for outer rows and evaluates the join qualifications.", "aliases": ["NestLoop", "Nested Loop", "T_NestLoop"], "content_hash": "8924657d1a8499ae4140bc841e5d37b17617e3af649bff66b5d149e1eff46c5a", "versions": {"10": {"facts": [{"label": "Core node tag", "value": "T_NestLoop"}, {"label": "Structured EXPLAIN Node Type", "value": "Nested Loop"}, {"label": "Inputs", "value": "Outer and inner child plans"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitNestLoop"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "4dc75baf53195f309c751e1a40db2eca0b948d4cc8aba71549fc4a80110f03fd", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": []}, "tables": [{"key": "explain-labels", "rows": [{"label": "Nested Loop", "identity": "Nested Loop"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=10", "label": "EXPLAIN"}, {"url": "/docs/10/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/10/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_nestloop/?v=10", "label": "enable_nestloop"}], "release": {"ref": "PostgreSQL 10.23 source archive", "label": "10.23", "major": "10", "channel": "historical", "revision": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9", "source_url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "line": 921, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:921", "sha256": "a785298532047cfeda969e78c3597a343dc1c56d61ba85830b0f16a02a14b5a1", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "line": 290, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:290", "sha256": "cea76648bb38ae55f18f989768bee1a4ee025691ceea0f86bccb29dcdc166acc", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "4dc75baf53195f309c751e1a40db2eca0b948d4cc8aba71549fc4a80110f03fd", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "d562c321108844798cd234303fffb618f13d4ee3f3a5ac79bfd963b077e47c22", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "/docs/10/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 10.23 \u00b7 parallel-plans", "sha256": "cd37ed0ef7e2cf177707f50d9a7258574b4cefdcc087e6ee99a3bf377d960fcb"}, {"url": "/docs/10/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 10.23 \u00b7 using-explain", "sha256": "a4b4304aedb0a2da0145cc7c35b03b319a5c7ee3df35a8ef84ab6d3a610f98f3"}], "node_tag": "T_NestLoop", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Nested Loop.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["Just as in a non-parallel plan, the driving table may be joined to one or more other tables using a nested loop, hash join, or merge join. The inner side of the join may be any kind of non-parallel plan that is otherwise supported by the planner provided that it is safe to run within a parallel worker. For example, if a nested loop join is chosen, the inner plan may be an index scan which looks up a value taken from the outer side of the join."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;\n\n                                      QUERY PLAN\n--------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..118.62 rows=10 width=488)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.91 rows=1 width=244)\n         Index Cond: (unique2 = t1.unique2)", "source": {"url": "/docs/10/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 10.23 \u00b7 using-explain", "sha256": "a4b4304aedb0a2da0145cc7c35b03b319a5c7ee3df35a8ef84ab6d3a610f98f3"}, "paragraphs": ["Example copied from the PostgreSQL 10.23 manual; it was not executed for this collection.", "Let's try joining two tables, using the columns we have been discussing:"]}, {"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;\n\n                                         QUERY PLAN\n---------------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..49.46 rows=33 width=488)\n   Join Filter: (t1.hundred < t2.hundred)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Materialize  (cost=0.29..8.51 rows=10 width=244)\n         ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)\n               Index Cond: (unique2 < 10)", "source": {"url": "/docs/10/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 10.23 \u00b7 using-explain", "sha256": "a4b4304aedb0a2da0145cc7c35b03b319a5c7ee3df35a8ef84ab6d3a610f98f3"}, "paragraphs": ["Example copied from the PostgreSQL 10.23 manual; it was not executed for this collection.", "In this example the join's output row count is the same as the product of the two scans' row counts, but that's not true in all cases because there can be additional WHERE clauses that mention both tables and so can only be applied at the join point, not to either input scan. Here's an example:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["old comments Returns the tuple joined from inner and outer tuples which satisfies the qualification clause.", "It scans the inner relation to join with current outer tuple.", "If none is found, next tuple from the outer relation is retrieved and the inner relation is scanned from the beginning again to join with the outer tuple.", "NULL is returned if all the remaining outer tuples are tried and all fail to join with the inner tuples.", "NULL is also returned if there is no tuple from inner relation."]}, {"code": "case T_NestLoop:\n\t\t\tpname = sname = \"Nested Loop\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Rescans the inner plan for outer rows and evaluates the join qualifications."], "evidence_kind": "source and documentation", "explain_names": ["Nested Loop"], "partial_modes": [], "comparison_data": {"node_tag": "T_NestLoop", "strategies": [], "text_names": ["Nested Loop"], "initializer": "ExecInitNestLoop", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "852ca4868c795edb229c6a1a6741f21e4cec6ee99e843b68527b4563765adf7d", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeNestloop.c"}, "parallel_callbacks": []}, "11": {"facts": [{"label": "Core node tag", "value": "T_NestLoop"}, {"label": "Structured EXPLAIN Node Type", "value": "Nested Loop"}, {"label": "Inputs", "value": "Outer and inner child plans"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitNestLoop"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "67c6f5546bc05d8121c560fb229e8a7e4c7eed2c8699e6c763915783fe10b6d6", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": []}, "tables": [{"key": "explain-labels", "rows": [{"label": "Nested Loop", "identity": "Nested Loop"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=11", "label": "EXPLAIN"}, {"url": "/docs/11/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/11/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_nestloop/?v=11", "label": "enable_nestloop"}], "release": {"ref": "PostgreSQL 11.22 source archive", "label": "11.22", "major": "11", "channel": "historical", "revision": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0", "source_url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "line": 1046, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1046", "sha256": "9df8400c1a4377179572ceb916d6020fca4e2760f74bf416d77ed97476523bbd", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "line": 290, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:290", "sha256": "95ef4d4a5df4c29f14af9763fae2c530449bdacdf3853d9ff297adf1fed6153b", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "67c6f5546bc05d8121c560fb229e8a7e4c7eed2c8699e6c763915783fe10b6d6", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "5e0511194183800e8d6eb293fd4b40639c7d3118e2d199c8e7865ba4fa4cf67f", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "/docs/11/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 11.22 \u00b7 parallel-plans", "sha256": "353df5869034b7665159a74037d9cf3e1800d15efcea3390daaa99aae89fa288"}, {"url": "/docs/11/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 11.22 \u00b7 using-explain", "sha256": "8411bc78085d4737e32d7cca103d859da6c6539e33ec5e2fba46e74c6f199d8a"}], "node_tag": "T_NestLoop", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Nested Loop.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["Just as in a non-parallel plan, the driving table may be joined to one or more other tables using a nested loop, hash join, or merge join. The inner side of the join may be any kind of non-parallel plan that is otherwise supported by the planner provided that it is safe to run within a parallel worker. Depending on the join type, the inner side may also be a parallel plan.", "In a nested loop join , the inner side is always non-parallel. Although it is executed in full, this is efficient if the inner side is an index scan, because the outer tuples and thus the loops that look up values in the index are divided over the cooperating processes."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;\n\n                                      QUERY PLAN\n--------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..118.62 rows=10 width=488)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.91 rows=1 width=244)\n         Index Cond: (unique2 = t1.unique2)", "source": {"url": "/docs/11/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 11.22 \u00b7 using-explain", "sha256": "8411bc78085d4737e32d7cca103d859da6c6539e33ec5e2fba46e74c6f199d8a"}, "paragraphs": ["Example copied from the PostgreSQL 11.22 manual; it was not executed for this collection.", "Let's try joining two tables, using the columns we have been discussing:"]}, {"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;\n\n                                         QUERY PLAN\n---------------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..49.46 rows=33 width=488)\n   Join Filter: (t1.hundred < t2.hundred)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Materialize  (cost=0.29..8.51 rows=10 width=244)\n         ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)\n               Index Cond: (unique2 < 10)", "source": {"url": "/docs/11/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 11.22 \u00b7 using-explain", "sha256": "8411bc78085d4737e32d7cca103d859da6c6539e33ec5e2fba46e74c6f199d8a"}, "paragraphs": ["Example copied from the PostgreSQL 11.22 manual; it was not executed for this collection.", "In this example the join's output row count is the same as the product of the two scans' row counts, but that's not true in all cases because there can be additional WHERE clauses that mention both tables and so can only be applied at the join point, not to either input scan. Here's an example:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["old comments Returns the tuple joined from inner and outer tuples which satisfies the qualification clause.", "It scans the inner relation to join with current outer tuple.", "If none is found, next tuple from the outer relation is retrieved and the inner relation is scanned from the beginning again to join with the outer tuple.", "NULL is returned if all the remaining outer tuples are tried and all fail to join with the inner tuples.", "NULL is also returned if there is no tuple from inner relation."]}, {"code": "case T_NestLoop:\n\t\t\tpname = sname = \"Nested Loop\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Rescans the inner plan for outer rows and evaluates the join qualifications."], "evidence_kind": "source and documentation", "explain_names": ["Nested Loop"], "partial_modes": [], "comparison_data": {"node_tag": "T_NestLoop", "strategies": [], "text_names": ["Nested Loop"], "initializer": "ExecInitNestLoop", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "852ca4868c795edb229c6a1a6741f21e4cec6ee99e843b68527b4563765adf7d", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeNestloop.c"}, "parallel_callbacks": []}, "12": {"facts": [{"label": "Core node tag", "value": "T_NestLoop"}, {"label": "Structured EXPLAIN Node Type", "value": "Nested Loop"}, {"label": "Inputs", "value": "Outer and inner child plans"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitNestLoop"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "6a021c6ba5e91d57b2dfd7bb676342a107072b9cc9c9fbd8fcefff491d85a279", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": []}, "tables": [{"key": "explain-labels", "rows": [{"label": "Nested Loop", "identity": "Nested Loop"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=12", "label": "EXPLAIN"}, {"url": "/docs/12/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/12/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_nestloop/?v=12", "label": "enable_nestloop"}], "release": {"ref": "PostgreSQL 12.22 source archive", "label": "12.22", "major": "12", "channel": "historical", "revision": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b", "source_url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "line": 1117, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1117", "sha256": "d02ea84fdaa201de5d9360645a9f24bfbd2c31f7d45a639e09560ac0e6b6471d", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "line": 290, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:290", "sha256": "311b17379fe54e3f342fe5ad41c43afbdfa1b844978db2bb2eb22b82520d3256", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "6a021c6ba5e91d57b2dfd7bb676342a107072b9cc9c9fbd8fcefff491d85a279", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "b0c4a0aeb48660ce06e5e700d5529ca9066fd16682bd15783d6e71b5420b07b0", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "/docs/12/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 12.22 \u00b7 parallel-plans", "sha256": "fa33380814ee65998f524524f8681d70cf3b1198d54b39847b00462b01517ffb"}, {"url": "/docs/12/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 12.22 \u00b7 using-explain", "sha256": "06297525f2180b07e752837a3351be9c871b56559f535bcd0a67dec9baac10c0"}], "node_tag": "T_NestLoop", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Nested Loop.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["Just as in a non-parallel plan, the driving table may be joined to one or more other tables using a nested loop, hash join, or merge join. The inner side of the join may be any kind of non-parallel plan that is otherwise supported by the planner provided that it is safe to run within a parallel worker. Depending on the join type, the inner side may also be a parallel plan.", "In a nested loop join , the inner side is always non-parallel. Although it is executed in full, this is efficient if the inner side is an index scan, because the outer tuples and thus the loops that look up values in the index are divided over the cooperating processes."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;\n\n                                      QUERY PLAN\n--------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..118.62 rows=10 width=488)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.91 rows=1 width=244)\n         Index Cond: (unique2 = t1.unique2)", "source": {"url": "/docs/12/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 12.22 \u00b7 using-explain", "sha256": "06297525f2180b07e752837a3351be9c871b56559f535bcd0a67dec9baac10c0"}, "paragraphs": ["Example copied from the PostgreSQL 12.22 manual; it was not executed for this collection.", "Let's try joining two tables, using the columns we have been discussing:"]}, {"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;\n\n                                         QUERY PLAN\n---------------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..49.46 rows=33 width=488)\n   Join Filter: (t1.hundred < t2.hundred)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Materialize  (cost=0.29..8.51 rows=10 width=244)\n         ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)\n               Index Cond: (unique2 < 10)", "source": {"url": "/docs/12/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 12.22 \u00b7 using-explain", "sha256": "06297525f2180b07e752837a3351be9c871b56559f535bcd0a67dec9baac10c0"}, "paragraphs": ["Example copied from the PostgreSQL 12.22 manual; it was not executed for this collection.", "In this example the join's output row count is the same as the product of the two scans' row counts, but that's not true in all cases because there can be additional WHERE clauses that mention both tables and so can only be applied at the join point, not to either input scan. Here's an example:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["old comments Returns the tuple joined from inner and outer tuples which satisfies the qualification clause.", "It scans the inner relation to join with current outer tuple.", "If none is found, next tuple from the outer relation is retrieved and the inner relation is scanned from the beginning again to join with the outer tuple.", "NULL is returned if all the remaining outer tuples are tried and all fail to join with the inner tuples.", "NULL is also returned if there is no tuple from inner relation."]}, {"code": "case T_NestLoop:\n\t\t\tpname = sname = \"Nested Loop\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Rescans the inner plan for outer rows and evaluates the join qualifications."], "evidence_kind": "source and documentation", "explain_names": ["Nested Loop"], "partial_modes": [], "comparison_data": {"node_tag": "T_NestLoop", "strategies": [], "text_names": ["Nested Loop"], "initializer": "ExecInitNestLoop", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "852ca4868c795edb229c6a1a6741f21e4cec6ee99e843b68527b4563765adf7d", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeNestloop.c"}, "parallel_callbacks": []}, "13": {"facts": [{"label": "Core node tag", "value": "T_NestLoop"}, {"label": "Structured EXPLAIN Node Type", "value": "Nested Loop"}, {"label": "Inputs", "value": "Outer and inner child plans"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitNestLoop"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "b2140108e1833594bb83ee427a277b3fdd72d7482e790dd0cdf8efed62ea8327", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": []}, "tables": [{"key": "explain-labels", "rows": [{"label": "Nested Loop", "identity": "Nested Loop"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=13", "label": "EXPLAIN"}, {"url": "/docs/13/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/13/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_nestloop/?v=13", "label": "enable_nestloop"}], "release": {"ref": "PostgreSQL 13.23 source archive", "label": "13.23", "major": "13", "channel": "historical", "revision": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6", "source_url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "line": 1175, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1175", "sha256": "541713e0e7f1c9cc352c2b6028964d440c19d2678a4463000094c24a88c1e730", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "line": 290, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:290", "sha256": "d085ee99acfa00587e6ade3a1d9f8108a0566beedbbee3f54a50c9fc0cc2e875", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "b2140108e1833594bb83ee427a277b3fdd72d7482e790dd0cdf8efed62ea8327", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "dcb296833777b02008c4b6bae8e8f7c6423b7ffba21f36702597c9d596d039ab", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "/docs/13/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 13.23 \u00b7 parallel-plans", "sha256": "025ad8564a8461b676c9153f9e084429cca0ef86c968090689b082120060f3d0"}, {"url": "/docs/13/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 13.23 \u00b7 using-explain", "sha256": "650fd8629382d5dc8f9f8412c50ec5f32442a9ad2f98ca88e5348a7c2bd0ac7a"}], "node_tag": "T_NestLoop", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Nested Loop.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["Just as in a non-parallel plan, the driving table may be joined to one or more other tables using a nested loop, hash join, or merge join. The inner side of the join may be any kind of non-parallel plan that is otherwise supported by the planner provided that it is safe to run within a parallel worker. Depending on the join type, the inner side may also be a parallel plan.", "In a nested loop join , the inner side is always non-parallel. Although it is executed in full, this is efficient if the inner side is an index scan, because the outer tuples and thus the loops that look up values in the index are divided over the cooperating processes."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;\n\n                                      QUERY PLAN\n--------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..118.62 rows=10 width=488)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.91 rows=1 width=244)\n         Index Cond: (unique2 = t1.unique2)", "source": {"url": "/docs/13/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 13.23 \u00b7 using-explain", "sha256": "650fd8629382d5dc8f9f8412c50ec5f32442a9ad2f98ca88e5348a7c2bd0ac7a"}, "paragraphs": ["Example copied from the PostgreSQL 13.23 manual; it was not executed for this collection.", "Let's try joining two tables, using the columns we have been discussing:"]}, {"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;\n\n                                         QUERY PLAN\n---------------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..49.46 rows=33 width=488)\n   Join Filter: (t1.hundred < t2.hundred)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Materialize  (cost=0.29..8.51 rows=10 width=244)\n         ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)\n               Index Cond: (unique2 < 10)", "source": {"url": "/docs/13/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 13.23 \u00b7 using-explain", "sha256": "650fd8629382d5dc8f9f8412c50ec5f32442a9ad2f98ca88e5348a7c2bd0ac7a"}, "paragraphs": ["Example copied from the PostgreSQL 13.23 manual; it was not executed for this collection.", "In this example the join's output row count is the same as the product of the two scans' row counts, but that's not true in all cases because there can be additional WHERE clauses that mention both tables and so can only be applied at the join point, not to either input scan. Here's an example:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["old comments Returns the tuple joined from inner and outer tuples which satisfies the qualification clause.", "It scans the inner relation to join with current outer tuple.", "If none is found, next tuple from the outer relation is retrieved and the inner relation is scanned from the beginning again to join with the outer tuple.", "NULL is returned if all the remaining outer tuples are tried and all fail to join with the inner tuples.", "NULL is also returned if there is no tuple from inner relation."]}, {"code": "case T_NestLoop:\n\t\t\tpname = sname = \"Nested Loop\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Rescans the inner plan for outer rows and evaluates the join qualifications."], "evidence_kind": "source and documentation", "explain_names": ["Nested Loop"], "partial_modes": [], "comparison_data": {"node_tag": "T_NestLoop", "strategies": [], "text_names": ["Nested Loop"], "initializer": "ExecInitNestLoop", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "852ca4868c795edb229c6a1a6741f21e4cec6ee99e843b68527b4563765adf7d", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeNestloop.c"}, "parallel_callbacks": []}, "14": {"facts": [{"label": "Core node tag", "value": "T_NestLoop"}, {"label": "Structured EXPLAIN Node Type", "value": "Nested Loop"}, {"label": "Inputs", "value": "Outer and inner child plans"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitNestLoop"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "80758065c0b960875bd64a1fa41a7237cc2d2454f1534abd2c7962f16786f19b", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": []}, "tables": [{"key": "explain-labels", "rows": [{"label": "Nested Loop", "identity": "Nested Loop"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=14", "label": "EXPLAIN"}, {"url": "/docs/14/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/14/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_nestloop/?v=14", "label": "enable_nestloop"}], "release": {"ref": "PostgreSQL 14.24 source archive", "label": "14.24", "major": "14", "channel": "stable", "revision": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897", "source_url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "line": 1211, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1211", "sha256": "e091be4e2a083b8dea39ccd09beedede22c1716ef974da66c214a44f48be8c41", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "line": 297, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:297", "sha256": "72da1c5ad457f1d92a39ab73531701794df858419e3b89d6e6cb7079634e68fa", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "80758065c0b960875bd64a1fa41a7237cc2d2454f1534abd2c7962f16786f19b", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "302f51a16b570dba7ec4e7bc045f7df5800d21630280354d1a24025f3baec75d", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "/docs/14/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 14.24 \u00b7 parallel-plans", "sha256": "71fc3525781b74150364925e123cd8598fdebe0e05326706f2bc2e1ffa0b88a4"}, {"url": "/docs/14/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 14.24 \u00b7 using-explain", "sha256": "7f5ab59cb21a035ada45ea3426c5d1cca3f781273483677f73fdd76753555206"}], "node_tag": "T_NestLoop", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Nested Loop.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["Just as in a non-parallel plan, the driving table may be joined to one or more other tables using a nested loop, hash join, or merge join. The inner side of the join may be any kind of non-parallel plan that is otherwise supported by the planner provided that it is safe to run within a parallel worker. Depending on the join type, the inner side may also be a parallel plan.", "In a nested loop join , the inner side is always non-parallel. Although it is executed in full, this is efficient if the inner side is an index scan, because the outer tuples and thus the loops that look up values in the index are divided over the cooperating processes."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;\n\n                                      QUERY PLAN\n--------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..118.62 rows=10 width=488)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.91 rows=1 width=244)\n         Index Cond: (unique2 = t1.unique2)", "source": {"url": "/docs/14/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 14.24 \u00b7 using-explain", "sha256": "7f5ab59cb21a035ada45ea3426c5d1cca3f781273483677f73fdd76753555206"}, "paragraphs": ["Example copied from the PostgreSQL 14.24 manual; it was not executed for this collection.", "Let's try joining two tables, using the columns we have been discussing:"]}, {"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;\n\n                                         QUERY PLAN\n---------------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..49.46 rows=33 width=488)\n   Join Filter: (t1.hundred < t2.hundred)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Materialize  (cost=0.29..8.51 rows=10 width=244)\n         ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)\n               Index Cond: (unique2 < 10)", "source": {"url": "/docs/14/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 14.24 \u00b7 using-explain", "sha256": "7f5ab59cb21a035ada45ea3426c5d1cca3f781273483677f73fdd76753555206"}, "paragraphs": ["Example copied from the PostgreSQL 14.24 manual; it was not executed for this collection.", "In this example the join's output row count is the same as the product of the two scans' row counts, but that's not true in all cases because there can be additional WHERE clauses that mention both tables and so can only be applied at the join point, not to either input scan. Here's an example:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["old comments Returns the tuple joined from inner and outer tuples which satisfies the qualification clause.", "It scans the inner relation to join with current outer tuple.", "If none is found, next tuple from the outer relation is retrieved and the inner relation is scanned from the beginning again to join with the outer tuple.", "NULL is returned if all the remaining outer tuples are tried and all fail to join with the inner tuples.", "NULL is also returned if there is no tuple from inner relation."]}, {"code": "case T_NestLoop:\n\t\t\tpname = sname = \"Nested Loop\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Rescans the inner plan for outer rows and evaluates the join qualifications."], "evidence_kind": "source and documentation", "explain_names": ["Nested Loop"], "partial_modes": [], "comparison_data": {"node_tag": "T_NestLoop", "strategies": [], "text_names": ["Nested Loop"], "initializer": "ExecInitNestLoop", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "852ca4868c795edb229c6a1a6741f21e4cec6ee99e843b68527b4563765adf7d", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeNestloop.c"}, "parallel_callbacks": []}, "15": {"facts": [{"label": "Core node tag", "value": "T_NestLoop"}, {"label": "Structured EXPLAIN Node Type", "value": "Nested Loop"}, {"label": "Inputs", "value": "Outer and inner child plans"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitNestLoop"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "f9143333cd84337d5168f8f9ad0d3ea90ff347c8bb49c5630a19b6b888c173e4", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": []}, "tables": [{"key": "explain-labels", "rows": [{"label": "Nested Loop", "identity": "Nested Loop"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=15", "label": "EXPLAIN"}, {"url": "/docs/15/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/15/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_nestloop/?v=15", "label": "enable_nestloop"}], "release": {"ref": "PostgreSQL 15.19 source archive", "label": "15.19", "major": "15", "channel": "stable", "revision": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89", "source_url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "line": 1214, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1214", "sha256": "bb3b442d0f1b098aa8707335250102f027a596cd94117308bd16d1d36b258f5c", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "line": 297, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:297", "sha256": "19836c50a272741a4eac653541e655437c2e00710a541e5348d6a277d0669d7c", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "f9143333cd84337d5168f8f9ad0d3ea90ff347c8bb49c5630a19b6b888c173e4", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "fb4a4c8165495299131173680bc02a950d88e1ff610231fd97997bc0c9afc1d7", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "/docs/15/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 15.19 \u00b7 parallel-plans", "sha256": "ec9345488a15cdc05d3e0b0849763e2bf0b864ad17de786c5f023db5dab1ea96"}, {"url": "/docs/15/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 15.19 \u00b7 using-explain", "sha256": "d1f509457c647da453d2575c772d91022a0f115dd84f9a3b20f5ff25a243648f"}], "node_tag": "T_NestLoop", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Nested Loop.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["Just as in a non-parallel plan, the driving table may be joined to one or more other tables using a nested loop, hash join, or merge join. The inner side of the join may be any kind of non-parallel plan that is otherwise supported by the planner provided that it is safe to run within a parallel worker. Depending on the join type, the inner side may also be a parallel plan.", "In a nested loop join , the inner side is always non-parallel. Although it is executed in full, this is efficient if the inner side is an index scan, because the outer tuples and thus the loops that look up values in the index are divided over the cooperating processes."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;\n\n                                      QUERY PLAN\n--------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..118.62 rows=10 width=488)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.91 rows=1 width=244)\n         Index Cond: (unique2 = t1.unique2)", "source": {"url": "/docs/15/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 15.19 \u00b7 using-explain", "sha256": "d1f509457c647da453d2575c772d91022a0f115dd84f9a3b20f5ff25a243648f"}, "paragraphs": ["Example copied from the PostgreSQL 15.19 manual; it was not executed for this collection.", "Let's try joining two tables, using the columns we have been discussing:"]}, {"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;\n\n                                         QUERY PLAN\n---------------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..49.46 rows=33 width=488)\n   Join Filter: (t1.hundred < t2.hundred)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Materialize  (cost=0.29..8.51 rows=10 width=244)\n         ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)\n               Index Cond: (unique2 < 10)", "source": {"url": "/docs/15/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 15.19 \u00b7 using-explain", "sha256": "d1f509457c647da453d2575c772d91022a0f115dd84f9a3b20f5ff25a243648f"}, "paragraphs": ["Example copied from the PostgreSQL 15.19 manual; it was not executed for this collection.", "In this example the join's output row count is the same as the product of the two scans' row counts, but that's not true in all cases because there can be additional WHERE clauses that mention both tables and so can only be applied at the join point, not to either input scan. Here's an example:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["old comments Returns the tuple joined from inner and outer tuples which satisfies the qualification clause.", "It scans the inner relation to join with current outer tuple.", "If none is found, next tuple from the outer relation is retrieved and the inner relation is scanned from the beginning again to join with the outer tuple.", "NULL is returned if all the remaining outer tuples are tried and all fail to join with the inner tuples.", "NULL is also returned if there is no tuple from inner relation."]}, {"code": "case T_NestLoop:\n\t\t\tpname = sname = \"Nested Loop\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Rescans the inner plan for outer rows and evaluates the join qualifications."], "evidence_kind": "source and documentation", "explain_names": ["Nested Loop"], "partial_modes": [], "comparison_data": {"node_tag": "T_NestLoop", "strategies": [], "text_names": ["Nested Loop"], "initializer": "ExecInitNestLoop", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "852ca4868c795edb229c6a1a6741f21e4cec6ee99e843b68527b4563765adf7d", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeNestloop.c"}, "parallel_callbacks": []}, "16": {"facts": [{"label": "Core node tag", "value": "T_NestLoop"}, {"label": "Structured EXPLAIN Node Type", "value": "Nested Loop"}, {"label": "Inputs", "value": "Outer and inner child plans"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitNestLoop"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "2d444bb2b96df019d9ca6f7dc475aabe24e8d2c225606706020d5d29300f6318", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": []}, "tables": [{"key": "explain-labels", "rows": [{"label": "Nested Loop", "identity": "Nested Loop"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=16", "label": "EXPLAIN"}, {"url": "/docs/16/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/16/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_nestloop/?v=16", "label": "enable_nestloop"}], "release": {"ref": "PostgreSQL 16.15 source archive", "label": "16.15", "major": "16", "channel": "stable", "revision": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed", "source_url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "line": 1247, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1247", "sha256": "8e017f0116dbea471339b40c37a667cc9f95039e7e0329c783e5e8ce194de7e1", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "line": 297, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:297", "sha256": "e48c08e555f8cb4e4bb43df516c4b8906ce9bc374b2a745d98a1fc8c22cc5099", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "2d444bb2b96df019d9ca6f7dc475aabe24e8d2c225606706020d5d29300f6318", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "97db47353db76326b874589a5ad0a04501cc74cd72e237e7bd956e7472c41f1f", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "/docs/16/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 16.15 \u00b7 parallel-plans", "sha256": "53d83f63f97381fe1e4c0cfe5ceb81d30c1542b03e35a144684afd8fb3b9686c"}, {"url": "/docs/16/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 16.15 \u00b7 using-explain", "sha256": "bd8b86e5281cf0e52ad6e0e4bb8b6ff6510dac61982b44f0c1dd07d54012db3c"}], "node_tag": "T_NestLoop", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Nested Loop.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["Just as in a non-parallel plan, the driving table may be joined to one or more other tables using a nested loop, hash join, or merge join. The inner side of the join may be any kind of non-parallel plan that is otherwise supported by the planner provided that it is safe to run within a parallel worker. Depending on the join type, the inner side may also be a parallel plan.", "In a nested loop join , the inner side is always non-parallel. Although it is executed in full, this is efficient if the inner side is an index scan, because the outer tuples and thus the loops that look up values in the index are divided over the cooperating processes."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;\n\n                                      QUERY PLAN\n--------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..118.62 rows=10 width=488)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.91 rows=1 width=244)\n         Index Cond: (unique2 = t1.unique2)", "source": {"url": "/docs/16/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 16.15 \u00b7 using-explain", "sha256": "bd8b86e5281cf0e52ad6e0e4bb8b6ff6510dac61982b44f0c1dd07d54012db3c"}, "paragraphs": ["Example copied from the PostgreSQL 16.15 manual; it was not executed for this collection.", "Let's try joining two tables, using the columns we have been discussing:"]}, {"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;\n\n                                         QUERY PLAN\n---------------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..49.46 rows=33 width=488)\n   Join Filter: (t1.hundred < t2.hundred)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Materialize  (cost=0.29..8.51 rows=10 width=244)\n         ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)\n               Index Cond: (unique2 < 10)", "source": {"url": "/docs/16/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 16.15 \u00b7 using-explain", "sha256": "bd8b86e5281cf0e52ad6e0e4bb8b6ff6510dac61982b44f0c1dd07d54012db3c"}, "paragraphs": ["Example copied from the PostgreSQL 16.15 manual; it was not executed for this collection.", "In this example the join's output row count is the same as the product of the two scans' row counts, but that's not true in all cases because there can be additional WHERE clauses that mention both tables and so can only be applied at the join point, not to either input scan. Here's an example:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["old comments Returns the tuple joined from inner and outer tuples which satisfies the qualification clause.", "It scans the inner relation to join with current outer tuple.", "If none is found, next tuple from the outer relation is retrieved and the inner relation is scanned from the beginning again to join with the outer tuple.", "NULL is returned if all the remaining outer tuples are tried and all fail to join with the inner tuples.", "NULL is also returned if there is no tuple from inner relation."]}, {"code": "case T_NestLoop:\n\t\t\tpname = sname = \"Nested Loop\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Rescans the inner plan for outer rows and evaluates the join qualifications."], "evidence_kind": "source and documentation", "explain_names": ["Nested Loop"], "partial_modes": [], "comparison_data": {"node_tag": "T_NestLoop", "strategies": [], "text_names": ["Nested Loop"], "initializer": "ExecInitNestLoop", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "852ca4868c795edb229c6a1a6741f21e4cec6ee99e843b68527b4563765adf7d", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeNestloop.c"}, "parallel_callbacks": []}, "17": {"facts": [{"label": "Core node tag", "value": "T_NestLoop"}, {"label": "Structured EXPLAIN Node Type", "value": "Nested Loop"}, {"label": "Inputs", "value": "Outer and inner child plans"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitNestLoop"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "2faaa62770359ff17e74aa3c91615c5e30ef42fbb094e3437a549f6dae844105", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": []}, "tables": [{"key": "explain-labels", "rows": [{"label": "Nested Loop", "identity": "Nested Loop"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=17", "label": "EXPLAIN"}, {"url": "/docs/17/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/17/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_nestloop/?v=17", "label": "enable_nestloop"}], "release": {"ref": "PostgreSQL 17.11 source archive", "label": "17.11", "major": "17", "channel": "stable", "revision": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979", "source_url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "line": 1436, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1436", "sha256": "741251b1a3b6d269a52a673d42eb63b02e13a5872db7b359b137086ab21b63c8", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "line": 297, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:297", "sha256": "a77576e158b94cb01fa8c5174ba133004eabdd727660323f8afc66c8d2e757b8", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "2faaa62770359ff17e74aa3c91615c5e30ef42fbb094e3437a549f6dae844105", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "d390dd69e2d3f5085beb42b33e46ff0676a2959b916a12b82118a7e545f8e562", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "/docs/17/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 17.11 \u00b7 parallel-plans", "sha256": "784fe3d3b7ae7d1a34e466aa551bde484a40a3b60dc5d2ada6e1675f40713bbc"}, {"url": "/docs/17/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 17.11 \u00b7 using-explain", "sha256": "8e3422c77496cc53bfc225ccadda3e82eb23c8c362b8eb95e7fb02cdb315ea78"}], "node_tag": "T_NestLoop", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Nested Loop.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["Just as in a non-parallel plan, the driving table may be joined to one or more other tables using a nested loop, hash join, or merge join. The inner side of the join may be any kind of non-parallel plan that is otherwise supported by the planner provided that it is safe to run within a parallel worker. Depending on the join type, the inner side may also be a parallel plan.", "In a nested loop join , the inner side is always non-parallel. Although it is executed in full, this is efficient if the inner side is an index scan, because the outer tuples and thus the loops that look up values in the index are divided over the cooperating processes."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;\n\n                                      QUERY PLAN\n--------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..118.50 rows=10 width=488)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244)\n         Index Cond: (unique2 = t1.unique2)", "source": {"url": "/docs/17/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 17.11 \u00b7 using-explain", "sha256": "8e3422c77496cc53bfc225ccadda3e82eb23c8c362b8eb95e7fb02cdb315ea78"}, "paragraphs": ["Example copied from the PostgreSQL 17.11 manual; it was not executed for this collection.", "Let's try joining two tables, using the columns we have been discussing:"]}, {"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;\n\n                                         QUERY PLAN\n---------------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..49.36 rows=33 width=488)\n   Join Filter: (t1.hundred < t2.hundred)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Materialize  (cost=0.29..8.51 rows=10 width=244)\n         ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)\n               Index Cond: (unique2 < 10)", "source": {"url": "/docs/17/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 17.11 \u00b7 using-explain", "sha256": "8e3422c77496cc53bfc225ccadda3e82eb23c8c362b8eb95e7fb02cdb315ea78"}, "paragraphs": ["Example copied from the PostgreSQL 17.11 manual; it was not executed for this collection.", "In this example the join's output row count is the same as the product of the two scans' row counts, but that's not true in all cases because there can be additional WHERE clauses that mention both tables and so can only be applied at the join point, not to either input scan. Here's an example:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["old comments Returns the tuple joined from inner and outer tuples which satisfies the qualification clause.", "It scans the inner relation to join with current outer tuple.", "If none is found, next tuple from the outer relation is retrieved and the inner relation is scanned from the beginning again to join with the outer tuple.", "NULL is returned if all the remaining outer tuples are tried and all fail to join with the inner tuples.", "NULL is also returned if there is no tuple from inner relation."]}, {"code": "case T_NestLoop:\n\t\t\tpname = sname = \"Nested Loop\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Rescans the inner plan for outer rows and evaluates the join qualifications."], "evidence_kind": "source and documentation", "explain_names": ["Nested Loop"], "partial_modes": [], "comparison_data": {"node_tag": "T_NestLoop", "strategies": [], "text_names": ["Nested Loop"], "initializer": "ExecInitNestLoop", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "852ca4868c795edb229c6a1a6741f21e4cec6ee99e843b68527b4563765adf7d", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeNestloop.c"}, "parallel_callbacks": []}, "18": {"facts": [{"label": "Core node tag", "value": "T_NestLoop"}, {"label": "Structured EXPLAIN Node Type", "value": "Nested Loop"}, {"label": "Inputs", "value": "Outer and inner child plans"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitNestLoop"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "2d8fbc2a3a31ce10400f0524107d2d083721071b6a384f5d635c11d1d2aeb49c", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": []}, "tables": [{"key": "explain-labels", "rows": [{"label": "Nested Loop", "identity": "Nested Loop"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=18", "label": "EXPLAIN"}, {"url": "/docs/18/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/18/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_nestloop/?v=18", "label": "enable_nestloop"}], "release": {"ref": "PostgreSQL 18.6 source archive", "label": "18.6", "major": "18", "channel": "stable", "revision": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f", "source_url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 1421, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1421", "sha256": "34c86d6070224a0e981efef51f79101d6d505e5874f1684ace183034bab14bb4", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 297, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:297", "sha256": "f8a06a3f539077249b20664b2812433db6d7bd12b2c0ca633525db43d06f112a", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "2d8fbc2a3a31ce10400f0524107d2d083721071b6a384f5d635c11d1d2aeb49c", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "52422b327a8049fbbb20d8b96008a0fc0a6fafa60f7eff3c695d5b2e83830120", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "/docs/18/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 18.6 \u00b7 parallel-plans", "sha256": "62207d207bead82b01b59dc119c4f95856f08655cc11d4699a05a40867ed2070"}, {"url": "/docs/18/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}], "node_tag": "T_NestLoop", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Nested Loop.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["Just as in a non-parallel plan, the driving table may be joined to one or more other tables using a nested loop, hash join, or merge join. The inner side of the join may be any kind of non-parallel plan that is otherwise supported by the planner provided that it is safe to run within a parallel worker. Depending on the join type, the inner side may also be a parallel plan.", "In a nested loop join , the inner side is always non-parallel. Although it is executed in full, this is efficient if the inner side is an index scan, because the outer tuples and thus the loops that look up values in the index are divided over the cooperating processes."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;\n\n                                      QUERY PLAN\n--------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..118.50 rows=10 width=488)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244)\n         Index Cond: (unique2 = t1.unique2)", "source": {"url": "/docs/18/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}, "paragraphs": ["Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.", "Let's try joining two tables, using the columns we have been discussing:"]}, {"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;\n\n                                         QUERY PLAN\n---------------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..49.36 rows=33 width=488)\n   Join Filter: (t1.hundred < t2.hundred)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Materialize  (cost=0.29..8.51 rows=10 width=244)\n         ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)\n               Index Cond: (unique2 < 10)", "source": {"url": "/docs/18/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}, "paragraphs": ["Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.", "In this example the join's output row count is the same as the product of the two scans' row counts, but that's not true in all cases because there can be additional WHERE clauses that mention both tables and so can only be applied at the join point, not to either input scan. Here's an example:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["old comments Returns the tuple joined from inner and outer tuples which satisfies the qualification clause.", "It scans the inner relation to join with current outer tuple.", "If none is found, next tuple from the outer relation is retrieved and the inner relation is scanned from the beginning again to join with the outer tuple.", "NULL is returned if all the remaining outer tuples are tried and all fail to join with the inner tuples.", "NULL is also returned if there is no tuple from inner relation."]}, {"code": "case T_NestLoop:\n\t\t\tpname = sname = \"Nested Loop\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Rescans the inner plan for outer rows and evaluates the join qualifications."], "evidence_kind": "source and documentation", "explain_names": ["Nested Loop"], "partial_modes": [], "comparison_data": {"node_tag": "T_NestLoop", "strategies": [], "text_names": ["Nested Loop"], "initializer": "ExecInitNestLoop", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "852ca4868c795edb229c6a1a6741f21e4cec6ee99e843b68527b4563765adf7d", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeNestloop.c"}, "parallel_callbacks": []}, "19": {"facts": [{"label": "Core node tag", "value": "T_NestLoop"}, {"label": "Structured EXPLAIN Node Type", "value": "Nested Loop"}, {"label": "Inputs", "value": "Outer and inner child plans"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitNestLoop"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "896f531538a403706ca8267e378d5fd25262bf94a6480fac10f4daf4926e1759", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": []}, "tables": [{"key": "explain-labels", "rows": [{"label": "Nested Loop", "identity": "Nested Loop"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=19", "label": "EXPLAIN"}, {"url": "/docs/19/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/19/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_nestloop/?v=19", "label": "enable_nestloop"}], "release": {"ref": "PostgreSQL 19beta4 source archive", "label": "19beta4", "major": "19", "channel": "preview", "revision": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86", "source_url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "line": 1433, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1433", "sha256": "8b115b1c194a4b54ae630209a741e293b1df49a9052f10b2de9ca092a48998e3", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "line": 297, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:297", "sha256": "5e39b2037bed672da55104229ecc32da5abde44c26bcad01479edcfa044d09ed", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "896f531538a403706ca8267e378d5fd25262bf94a6480fac10f4daf4926e1759", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "1c65d5d6b6c81c71531685843647869bcae630779d815a5036b06e070c6c06c7", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "/docs/19/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 19beta4 \u00b7 parallel-plans", "sha256": "f99fee3456a48a5ce2c399d6b35ab93183c2c9012e8ed60c53c0c253f79c3756"}, {"url": "/docs/19/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 19beta4 \u00b7 using-explain", "sha256": "52f111fbd213e2200146319d01a9dcc2ea90001617d2a28edc52f7954c2dd70b"}], "node_tag": "T_NestLoop", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Nested Loop.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["Just as in a non-parallel plan, the driving table may be joined to one or more other tables using a nested loop, hash join, or merge join. The inner side of the join may be any kind of non-parallel plan that is otherwise supported by the planner provided that it is safe to run within a parallel worker. Depending on the join type, the inner side may also be a parallel plan.", "In a nested loop join , the inner side is always non-parallel. Although it is executed in full, this is efficient if the inner side is an index scan, because the outer tuples and thus the loops that look up values in the index are divided over the cooperating processes."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;\n\n                                      QUERY PLAN\n--------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..118.50 rows=10 width=488)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244)\n         Index Cond: (unique2 = t1.unique2)", "source": {"url": "/docs/19/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 19beta4 \u00b7 using-explain", "sha256": "52f111fbd213e2200146319d01a9dcc2ea90001617d2a28edc52f7954c2dd70b"}, "paragraphs": ["Example copied from the PostgreSQL 19beta4 manual; it was not executed for this collection.", "Let's try joining two tables, using the columns we have been discussing:"]}, {"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;\n\n                                         QUERY PLAN\n---------------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..49.36 rows=33 width=488)\n   Join Filter: (t1.hundred < t2.hundred)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Materialize  (cost=0.29..8.51 rows=10 width=244)\n         ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)\n               Index Cond: (unique2 < 10)", "source": {"url": "/docs/19/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 19beta4 \u00b7 using-explain", "sha256": "52f111fbd213e2200146319d01a9dcc2ea90001617d2a28edc52f7954c2dd70b"}, "paragraphs": ["Example copied from the PostgreSQL 19beta4 manual; it was not executed for this collection.", "In this example the join's output row count is the same as the product of the two scans' row counts, but that's not true in all cases because there can be additional WHERE clauses that mention both tables and so can only be applied at the join point, not to either input scan. Here's an example:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["old comments Returns the tuple joined from inner and outer tuples which satisfies the qualification clause.", "It scans the inner relation to join with current outer tuple.", "If none is found, next tuple from the outer relation is retrieved and the inner relation is scanned from the beginning again to join with the outer tuple.", "NULL is returned if all the remaining outer tuples are tried and all fail to join with the inner tuples.", "NULL is also returned if there is no tuple from inner relation."]}, {"code": "case T_NestLoop:\n\t\t\tpname = sname = \"Nested Loop\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Rescans the inner plan for outer rows and evaluates the join qualifications."], "evidence_kind": "source and documentation", "explain_names": ["Nested Loop"], "partial_modes": [], "comparison_data": {"node_tag": "T_NestLoop", "strategies": [], "text_names": ["Nested Loop"], "initializer": "ExecInitNestLoop", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "852ca4868c795edb229c6a1a6741f21e4cec6ee99e843b68527b4563765adf7d", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeNestloop.c"}, "parallel_callbacks": []}, "20": {"facts": [{"label": "Core node tag", "value": "T_NestLoop"}, {"label": "Structured EXPLAIN Node Type", "value": "Nested Loop"}, {"label": "Inputs", "value": "Outer and inner child plans"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitNestLoop"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "6c3138ef3c49998b88715923d3c47e9e2a300fe07f72df16f0d4beabe653f1fc", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": []}, "tables": [{"key": "explain-labels", "rows": [{"label": "Nested Loop", "identity": "Nested Loop"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=20", "label": "EXPLAIN"}, {"url": "/docs/devel/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/devel/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_nestloop/?v=20", "label": "enable_nestloop"}], "release": {"ref": "PostgreSQL 20devel source archive", "label": "20devel", "major": "20", "channel": "devel", "revision": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41", "source_url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "source_snapshot_utc": "26-Sep-2026 20:22"}, "sources": [{"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "line": 1433, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1433", "sha256": "13402758013520451539427b5993db06d463ca11c4e2d4cc5444e82367688077", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "line": 297, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:297", "sha256": "5e39b2037bed672da55104229ecc32da5abde44c26bcad01479edcfa044d09ed", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "6c3138ef3c49998b88715923d3c47e9e2a300fe07f72df16f0d4beabe653f1fc", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "7a94ed1652f0d74d50c39971d1cd3e8051dbc0d6058f31b6de71a433ca343521", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "/docs/devel/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 20devel \u00b7 parallel-plans", "sha256": "6451d4254d26d789b8697ba7207288ac26e75e56f9711c8a5a3f5480171a4724"}, {"url": "/docs/devel/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 20devel \u00b7 using-explain", "sha256": "99cfea3035876ea63f88b75ba8c964b51a32a70606544e09f769c0b60a234b31"}], "node_tag": "T_NestLoop", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Nested Loop.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["Just as in a non-parallel plan, the driving table may be joined to one or more other tables using a nested loop, hash join, or merge join. The inner side of the join may be any kind of non-parallel plan that is otherwise supported by the planner provided that it is safe to run within a parallel worker. Depending on the join type, the inner side may also be a parallel plan.", "In a nested loop join , the inner side is always non-parallel. Although it is executed in full, this is efficient if the inner side is an index scan, because the outer tuples and thus the loops that look up values in the index are divided over the cooperating processes."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;\n\n                                      QUERY PLAN\n--------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..118.50 rows=10 width=488)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244)\n         Index Cond: (unique2 = t1.unique2)", "source": {"url": "/docs/devel/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 20devel \u00b7 using-explain", "sha256": "99cfea3035876ea63f88b75ba8c964b51a32a70606544e09f769c0b60a234b31"}, "paragraphs": ["Example copied from the PostgreSQL 20devel manual; it was not executed for this collection.", "Let's try joining two tables, using the columns we have been discussing:"]}, {"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;\n\n                                         QUERY PLAN\n---------------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..49.36 rows=33 width=488)\n   Join Filter: (t1.hundred < t2.hundred)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Materialize  (cost=0.29..8.51 rows=10 width=244)\n         ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)\n               Index Cond: (unique2 < 10)", "source": {"url": "/docs/devel/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 20devel \u00b7 using-explain", "sha256": "99cfea3035876ea63f88b75ba8c964b51a32a70606544e09f769c0b60a234b31"}, "paragraphs": ["Example copied from the PostgreSQL 20devel manual; it was not executed for this collection.", "In this example the join's output row count is the same as the product of the two scans' row counts, but that's not true in all cases because there can be additional WHERE clauses that mention both tables and so can only be applied at the join point, not to either input scan. Here's an example:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["old comments Returns the tuple joined from inner and outer tuples which satisfies the qualification clause.", "It scans the inner relation to join with current outer tuple.", "If none is found, next tuple from the outer relation is retrieved and the inner relation is scanned from the beginning again to join with the outer tuple.", "NULL is returned if all the remaining outer tuples are tried and all fail to join with the inner tuples.", "NULL is also returned if there is no tuple from inner relation."]}, {"code": "case T_NestLoop:\n\t\t\tpname = sname = \"Nested Loop\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Rescans the inner plan for outer rows and evaluates the join qualifications."], "evidence_kind": "source and documentation", "explain_names": ["Nested Loop"], "partial_modes": [], "comparison_data": {"node_tag": "T_NestLoop", "strategies": [], "text_names": ["Nested Loop"], "initializer": "ExecInitNestLoop", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "852ca4868c795edb229c6a1a6741f21e4cec6ee99e843b68527b4563765adf7d", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeNestloop.c"}, "parallel_callbacks": []}}}, "snapshot": {"facts": [{"label": "Core node tag", "value": "T_NestLoop"}, {"label": "Structured EXPLAIN Node Type", "value": "Nested Loop"}, {"label": "Inputs", "value": "Outer and inner child plans"}, {"label": "Output", "value": "Joined tuples according to the selected join type"}, {"label": "Executor initializer", "value": "ExecInitNestLoop"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "2d8fbc2a3a31ce10400f0524107d2d083721071b6a384f5d635c11d1d2aeb49c", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}], "mechanism": "unclassified", "description": "This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider.", "source_notes": []}, "tables": [{"key": "explain-labels", "rows": [{"label": "Nested Loop", "identity": "Nested Loop"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=18", "label": "EXPLAIN"}, {"url": "/docs/18/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/18/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_nestloop/?v=18", "label": "enable_nestloop"}], "release": {"ref": "PostgreSQL 18.6 source archive", "label": "18.6", "major": "18", "channel": "stable", "revision": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f", "source_url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 1421, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1421", "sha256": "34c86d6070224a0e981efef51f79101d6d505e5874f1684ace183034bab14bb4", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 297, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:297", "sha256": "f8a06a3f539077249b20664b2812433db6d7bd12b2c0ca633525db43d06f112a", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeNestloop.c", "label": "src/backend/executor/nodeNestloop.c", "sha256": "2d8fbc2a3a31ce10400f0524107d2d083721071b6a384f5d635c11d1d2aeb49c", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "52422b327a8049fbbb20d8b96008a0fc0a6fafa60f7eff3c695d5b2e83830120", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "/docs/18/parallel-plans.html#PARALLEL-JOINS", "path": "parallel-plans.html", "label": "PostgreSQL 18.6 \u00b7 parallel-plans", "sha256": "62207d207bead82b01b59dc119c4f95856f08655cc11d4699a05a40867ed2070"}, {"url": "/docs/18/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}], "node_tag": "T_NestLoop", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Nested Loop.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This extraction does not assign a universal memory limit or spill policy to this node. Inspect the same-build implementation and its expressions or provider."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["Just as in a non-parallel plan, the driving table may be joined to one or more other tables using a nested loop, hash join, or merge join. The inner side of the join may be any kind of non-parallel plan that is otherwise supported by the planner provided that it is safe to run within a parallel worker. Depending on the join type, the inner side may also be a parallel plan.", "In a nested loop join , the inner side is always non-parallel. Although it is executed in full, this is efficient if the inner side is an index scan, because the outer tuples and thus the loops that look up values in the index are divided over the cooperating processes."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;\n\n                                      QUERY PLAN\n--------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..118.50 rows=10 width=488)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244)\n         Index Cond: (unique2 = t1.unique2)", "source": {"url": "/docs/18/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}, "paragraphs": ["Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.", "Let's try joining two tables, using the columns we have been discussing:"]}, {"code": "EXPLAIN SELECT *\nFROM tenk1 t1, tenk2 t2\nWHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;\n\n                                         QUERY PLAN\n---------------------------------------------------------------------------------------------\n Nested Loop  (cost=4.65..49.36 rows=33 width=488)\n   Join Filter: (t1.hundred < t2.hundred)\n   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)\n         Recheck Cond: (unique1 < 10)\n         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)\n               Index Cond: (unique1 < 10)\n   ->  Materialize  (cost=0.29..8.51 rows=10 width=244)\n         ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)\n               Index Cond: (unique2 < 10)", "source": {"url": "/docs/18/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}, "paragraphs": ["Example copied from the PostgreSQL 18.6 manual; it was not executed for this collection.", "In this example the join's output row count is the same as the product of the two scans' row counts, but that's not true in all cases because there can be additional WHERE clauses that mention both tables and so can only be applied at the join point, not to either input scan. Here's an example:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["old comments Returns the tuple joined from inner and outer tuples which satisfies the qualification clause.", "It scans the inner relation to join with current outer tuple.", "If none is found, next tuple from the outer relation is retrieved and the inner relation is scanned from the beginning again to join with the outer tuple.", "NULL is returned if all the remaining outer tuples are tried and all fail to join with the inner tuples.", "NULL is also returned if there is no tuple from inner relation."]}, {"code": "case T_NestLoop:\n\t\t\tpname = sname = \"Nested Loop\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Rescans the inner plan for outer rows and evaluates the join qualifications."], "evidence_kind": "source and documentation", "explain_names": ["Nested Loop"], "partial_modes": [], "comparison_data": {"node_tag": "T_NestLoop", "strategies": [], "text_names": ["Nested Loop"], "initializer": "ExecInitNestLoop", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": []}, "comparison_hash": "852ca4868c795edb229c6a1a6741f21e4cec6ee99e843b68527b4563765adf7d", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeNestloop.c"}, "parallel_callbacks": []}, "comparison": {"left": "17", "right": "18", "status": "unchanged", "diff": ""}}