{"kind": "plan", "major": "18", "item": {"slug": "index-scan", "name": "Index Scan", "name_zh": "IndexScan", "category": "Scan", "summary": "Uses an index to identify table tuples and fetches them from the relation.", "aliases": ["Index Scan", "IndexScan", "T_IndexScan"], "content_hash": "f53ebc4a90679a398a8018bbd906d3fd30a00f8375f009e7068d9db7115abadf", "versions": {"10": {"facts": [{"label": "Core node tag", "value": "T_IndexScan"}, {"label": "Structured EXPLAIN Node Type", "value": "Index Scan"}, {"label": "Inputs", "value": "An index and its relation"}, {"label": "Output", "value": "Qualified table tuples"}, {"label": "Executor initializer", "value": "ExecInitIndexScan"}, {"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/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "f7fea3c437c537d9d8d89bbf7b034a5ed262e2230275aec63179a3e66b807187", "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": ["Store the scanned tuple in the scan tuple slot of the scan state. Note: we pass 'false' because tuples returned by amgetnext are pointers onto disk pages and must not be pfree()'d.", "If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Index Scan", "identity": "Index Scan"}], "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_indexscan/?v=10", "label": "enable_indexscan"}, {"url": "/wiki/guc/random_page_cost/?v=10", "label": "random_page_cost"}, {"url": "/wiki/indexam/?v=10", "label": "Index AM"}], "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": 944, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:944", "sha256": "a785298532047cfeda969e78c3597a343dc1c56d61ba85830b0f16a02a14b5a1", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "line": 217, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:217", "sha256": "cea76648bb38ae55f18f989768bee1a4ee025691ceea0f86bccb29dcdc166acc", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "path": "src/backend/executor/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "f7fea3c437c537d9d8d89bbf7b034a5ed262e2230275aec63179a3e66b807187", "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/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 10.23 \u00b7 using-explain", "sha256": "a4b4304aedb0a2da0145cc7c35b03b319a5c7ee3df35a8ef84ab6d3a610f98f3"}, {"url": "/docs/10/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 10.23 \u00b7 using-explain", "sha256": "a4b4304aedb0a2da0145cc7c35b03b319a5c7ee3df35a8ef84ab6d3a610f98f3"}, {"url": "/docs/10/using-explain.html#USING-EXPLAIN-CAVEATS", "path": "using-explain.html", "label": "PostgreSQL 10.23 \u00b7 using-explain", "sha256": "a4b4304aedb0a2da0145cc7c35b03b319a5c7ee3df35a8ef84ab6d3a610f98f3"}], "node_tag": "T_IndexScan", "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: Index Scan.", "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.", "Store the scanned tuple in the scan tuple slot of the scan state. Note: we pass 'false' because tuples returned by amgetnext are pointers onto disk pages and must not be pfree()'d.", "If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, {"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: ExecIndexScanEstimate, ExecIndexScanInitializeDSM, ExecIndexScanInitializeWorker, ExecIndexScanReInitializeDSM."]}, {"title": "Same-version manual discussion", "paragraphs": ["This is the same query as above, but we added a LIMIT so that not all the rows need be retrieved, and the planner changed its mind about what to do. Notice that the total cost and row count of the Index Scan node are shown as if it were run to completion. However, the Limit node is expected to stop after retrieving only a fifth of those rows, so its total cost is only a fifth as much, and that's the actual estimated cost of the query. This plan is preferred over adding a Limit node to the previous plan because the Limit could not avoid paying the startup cost of the bitmap scan, so the total cost would be something over 25 units with that approach.", "Notice that here the planner has chosen to \u201c materialize \u201d the inner relation of the join, by putting a Materialize plan node atop it. This means that the t2 index scan will be done just once, even though the nested-loop join node needs to read that data ten times, once for each row from the outer relation. The Materialize node saves the data in memory as it's read, and then returns the data from memory on each subsequent pass.", "Merge join requires its input data to be sorted on the join keys. In this plan the tenk1 data is sorted by using an index scan to visit the rows in the correct order, but a sequential scan and sort is preferred for onek , because there are many more rows to be visited in that table. (Sequential-scan-and-sort frequently beats an index scan for sorting many rows, because of the nonsequential disk access required by the index scan.)", "In some query plans, it is possible for a subplan node to be executed more than once. For example, the inner index scan will be executed once per outer row in the above nested-loop plan. In such cases, the loops value reports the total number of executions of the node, and the actual time and rows values shown are averages per-execution. This is done to make the numbers comparable with the way that the cost estimates are shown. Multiply by the loops value to get the total time actually spent in the node. In the above example, we spent a total of 0.220 milliseconds executing the index scans on tenk2 .", "The planner thinks (quite correctly) that this sample table is too small to bother with an index scan, so we have a plain sequential scan in which all the rows got rejected by the filter condition. But if we force an index scan to be used, we see:", "the estimated cost and row count for the Index Scan node are shown as though it were run to completion. But in reality the Limit node stopped requesting rows after it got two, so the actual row count is only 2 and the run time is less than the cost estimate would suggest. This is not an estimation error, only a discrepancy in the way the estimates and true values are displayed."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.07..229.20 rows=101 width=244)\n   Recheck Cond: (unique1 < 100)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now, let's make the condition more restrictive:"]}, {"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.04..229.43 rows=1 width=244)\n   Recheck Cond: (unique1 < 100)\n   Filter: (stringu1 = 'xxx'::name)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now let's add another condition to the WHERE clause:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["nodeIndexscan.c Routines to support indexed scans of relations", "When an ordering operator is used, tuples fetched from the index that need to be reordered are queued in a pairing heap, as ReorderTuples.", "Retrieve a tuple from the IndexScan node's currentRelation using the index specified in the IndexScanState information.", "We reach here if the index scan is not parallel, or if we're serially executing an index scan that was planned to be parallel.", "If no run-time keys to calculate or they are ready, go ahead and pass the scankeys to the index AM."]}, {"code": "case T_IndexScan:\n\t\t\tpname = sname = \"Index Scan\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Uses an index to identify table tuples and fetches them from the relation."], "evidence_kind": "source and documentation", "explain_names": ["Index Scan"], "partial_modes": [], "comparison_data": {"node_tag": "T_IndexScan", "strategies": [], "text_names": ["Index Scan"], "initializer": "ExecInitIndexScan", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanReInitializeDSM"]}, "comparison_hash": "fb7921673ff1820c8a2d077e3523844901f32a51d84d2f6f01020dd044f11db7", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeIndexscan.c"}, "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanReInitializeDSM"]}, "11": {"facts": [{"label": "Core node tag", "value": "T_IndexScan"}, {"label": "Structured EXPLAIN Node Type", "value": "Index Scan"}, {"label": "Inputs", "value": "An index and its relation"}, {"label": "Output", "value": "Qualified table tuples"}, {"label": "Executor initializer", "value": "ExecInitIndexScan"}, {"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/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "53494a0d11a28a1aebb1906661868a5bc6743b7ba87eb5cd6e30929cbc65e583", "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": ["Store the scanned tuple in the scan tuple slot of the scan state. Note: we pass 'false' because tuples returned by amgetnext are pointers onto disk pages and must not be pfree()'d.", "If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Index Scan", "identity": "Index Scan"}], "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_indexscan/?v=11", "label": "enable_indexscan"}, {"url": "/wiki/guc/random_page_cost/?v=11", "label": "random_page_cost"}, {"url": "/wiki/indexam/?v=11", "label": "Index AM"}], "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": 1069, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1069", "sha256": "9df8400c1a4377179572ceb916d6020fca4e2760f74bf416d77ed97476523bbd", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "line": 217, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:217", "sha256": "95ef4d4a5df4c29f14af9763fae2c530449bdacdf3853d9ff297adf1fed6153b", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "path": "src/backend/executor/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "53494a0d11a28a1aebb1906661868a5bc6743b7ba87eb5cd6e30929cbc65e583", "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/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 11.22 \u00b7 using-explain", "sha256": "8411bc78085d4737e32d7cca103d859da6c6539e33ec5e2fba46e74c6f199d8a"}, {"url": "/docs/11/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 11.22 \u00b7 using-explain", "sha256": "8411bc78085d4737e32d7cca103d859da6c6539e33ec5e2fba46e74c6f199d8a"}, {"url": "/docs/11/using-explain.html#USING-EXPLAIN-CAVEATS", "path": "using-explain.html", "label": "PostgreSQL 11.22 \u00b7 using-explain", "sha256": "8411bc78085d4737e32d7cca103d859da6c6539e33ec5e2fba46e74c6f199d8a"}], "node_tag": "T_IndexScan", "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: Index Scan.", "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.", "Store the scanned tuple in the scan tuple slot of the scan state. Note: we pass 'false' because tuples returned by amgetnext are pointers onto disk pages and must not be pfree()'d.", "If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, {"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: ExecIndexScanEstimate, ExecIndexScanInitializeDSM, ExecIndexScanInitializeWorker, ExecIndexScanReInitializeDSM."]}, {"title": "Same-version manual discussion", "paragraphs": ["This is the same query as above, but we added a LIMIT so that not all the rows need be retrieved, and the planner changed its mind about what to do. Notice that the total cost and row count of the Index Scan node are shown as if it were run to completion. However, the Limit node is expected to stop after retrieving only a fifth of those rows, so its total cost is only a fifth as much, and that's the actual estimated cost of the query. This plan is preferred over adding a Limit node to the previous plan because the Limit could not avoid paying the startup cost of the bitmap scan, so the total cost would be something over 25 units with that approach.", "Notice that here the planner has chosen to \u201c materialize \u201d the inner relation of the join, by putting a Materialize plan node atop it. This means that the t2 index scan will be done just once, even though the nested-loop join node needs to read that data ten times, once for each row from the outer relation. The Materialize node saves the data in memory as it's read, and then returns the data from memory on each subsequent pass.", "Merge join requires its input data to be sorted on the join keys. In this plan the tenk1 data is sorted by using an index scan to visit the rows in the correct order, but a sequential scan and sort is preferred for onek , because there are many more rows to be visited in that table. (Sequential-scan-and-sort frequently beats an index scan for sorting many rows, because of the nonsequential disk access required by the index scan.)", "In some query plans, it is possible for a subplan node to be executed more than once. For example, the inner index scan will be executed once per outer row in the above nested-loop plan. In such cases, the loops value reports the total number of executions of the node, and the actual time and rows values shown are averages per-execution. This is done to make the numbers comparable with the way that the cost estimates are shown. Multiply by the loops value to get the total time actually spent in the node. In the above example, we spent a total of 0.220 milliseconds executing the index scans on tenk2 .", "The planner thinks (quite correctly) that this sample table is too small to bother with an index scan, so we have a plain sequential scan in which all the rows got rejected by the filter condition. But if we force an index scan to be used, we see:", "the estimated cost and row count for the Index Scan node are shown as though it were run to completion. But in reality the Limit node stopped requesting rows after it got two, so the actual row count is only 2 and the run time is less than the cost estimate would suggest. This is not an estimation error, only a discrepancy in the way the estimates and true values are displayed."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.07..229.20 rows=101 width=244)\n   Recheck Cond: (unique1 < 100)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now, let's make the condition more restrictive:"]}, {"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.04..229.43 rows=1 width=244)\n   Recheck Cond: (unique1 < 100)\n   Filter: (stringu1 = 'xxx'::name)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now let's add another condition to the WHERE clause:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["nodeIndexscan.c Routines to support indexed scans of relations", "When an ordering operator is used, tuples fetched from the index that need to be reordered are queued in a pairing heap, as ReorderTuples.", "Retrieve a tuple from the IndexScan node's currentRelation using the index specified in the IndexScanState information.", "We reach here if the index scan is not parallel, or if we're serially executing an index scan that was planned to be parallel.", "If no run-time keys to calculate or they are ready, go ahead and pass the scankeys to the index AM."]}, {"code": "case T_IndexScan:\n\t\t\tpname = sname = \"Index Scan\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Uses an index to identify table tuples and fetches them from the relation."], "evidence_kind": "source and documentation", "explain_names": ["Index Scan"], "partial_modes": [], "comparison_data": {"node_tag": "T_IndexScan", "strategies": [], "text_names": ["Index Scan"], "initializer": "ExecInitIndexScan", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanReInitializeDSM"]}, "comparison_hash": "fb7921673ff1820c8a2d077e3523844901f32a51d84d2f6f01020dd044f11db7", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeIndexscan.c"}, "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanReInitializeDSM"]}, "12": {"facts": [{"label": "Core node tag", "value": "T_IndexScan"}, {"label": "Structured EXPLAIN Node Type", "value": "Index Scan"}, {"label": "Inputs", "value": "An index and its relation"}, {"label": "Output", "value": "Qualified table tuples"}, {"label": "Executor initializer", "value": "ExecInitIndexScan"}, {"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/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "3e536bdf59b743fd6c159839885d4faf3d8e46143c8bb7e7f1249bb7bdb57293", "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": ["If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Index Scan", "identity": "Index Scan"}], "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_indexscan/?v=12", "label": "enable_indexscan"}, {"url": "/wiki/guc/random_page_cost/?v=12", "label": "random_page_cost"}, {"url": "/wiki/indexam/?v=12", "label": "Index AM"}], "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": 1140, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1140", "sha256": "d02ea84fdaa201de5d9360645a9f24bfbd2c31f7d45a639e09560ac0e6b6471d", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "line": 217, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:217", "sha256": "311b17379fe54e3f342fe5ad41c43afbdfa1b844978db2bb2eb22b82520d3256", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "path": "src/backend/executor/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "3e536bdf59b743fd6c159839885d4faf3d8e46143c8bb7e7f1249bb7bdb57293", "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/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 12.22 \u00b7 using-explain", "sha256": "06297525f2180b07e752837a3351be9c871b56559f535bcd0a67dec9baac10c0"}, {"url": "/docs/12/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 12.22 \u00b7 using-explain", "sha256": "06297525f2180b07e752837a3351be9c871b56559f535bcd0a67dec9baac10c0"}, {"url": "/docs/12/using-explain.html#USING-EXPLAIN-CAVEATS", "path": "using-explain.html", "label": "PostgreSQL 12.22 \u00b7 using-explain", "sha256": "06297525f2180b07e752837a3351be9c871b56559f535bcd0a67dec9baac10c0"}], "node_tag": "T_IndexScan", "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: Index Scan.", "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.", "If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, {"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: ExecIndexScanEstimate, ExecIndexScanInitializeDSM, ExecIndexScanInitializeWorker, ExecIndexScanReInitializeDSM."]}, {"title": "Same-version manual discussion", "paragraphs": ["This is the same query as above, but we added a LIMIT so that not all the rows need be retrieved, and the planner changed its mind about what to do. Notice that the total cost and row count of the Index Scan node are shown as if it were run to completion. However, the Limit node is expected to stop after retrieving only a fifth of those rows, so its total cost is only a fifth as much, and that's the actual estimated cost of the query. This plan is preferred over adding a Limit node to the previous plan because the Limit could not avoid paying the startup cost of the bitmap scan, so the total cost would be something over 25 units with that approach.", "Notice that here the planner has chosen to \u201c materialize \u201d the inner relation of the join, by putting a Materialize plan node atop it. This means that the t2 index scan will be done just once, even though the nested-loop join node needs to read that data ten times, once for each row from the outer relation. The Materialize node saves the data in memory as it's read, and then returns the data from memory on each subsequent pass.", "Merge join requires its input data to be sorted on the join keys. In this plan the tenk1 data is sorted by using an index scan to visit the rows in the correct order, but a sequential scan and sort is preferred for onek , because there are many more rows to be visited in that table. (Sequential-scan-and-sort frequently beats an index scan for sorting many rows, because of the nonsequential disk access required by the index scan.)", "In some query plans, it is possible for a subplan node to be executed more than once. For example, the inner index scan will be executed once per outer row in the above nested-loop plan. In such cases, the loops value reports the total number of executions of the node, and the actual time and rows values shown are averages per-execution. This is done to make the numbers comparable with the way that the cost estimates are shown. Multiply by the loops value to get the total time actually spent in the node. In the above example, we spent a total of 0.220 milliseconds executing the index scans on tenk2 .", "The planner thinks (quite correctly) that this sample table is too small to bother with an index scan, so we have a plain sequential scan in which all the rows got rejected by the filter condition. But if we force an index scan to be used, we see:", "the estimated cost and row count for the Index Scan node are shown as though it were run to completion. But in reality the Limit node stopped requesting rows after it got two, so the actual row count is only 2 and the run time is less than the cost estimate would suggest. This is not an estimation error, only a discrepancy in the way the estimates and true values are displayed."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.07..229.20 rows=101 width=244)\n   Recheck Cond: (unique1 < 100)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now, let's make the condition more restrictive:"]}, {"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.04..229.43 rows=1 width=244)\n   Recheck Cond: (unique1 < 100)\n   Filter: (stringu1 = 'xxx'::name)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now let's add another condition to the WHERE clause:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["nodeIndexscan.c Routines to support indexed scans of relations", "When an ordering operator is used, tuples fetched from the index that need to be reordered are queued in a pairing heap, as ReorderTuples.", "Retrieve a tuple from the IndexScan node's currentRelation using the index specified in the IndexScanState information.", "We reach here if the index scan is not parallel, or if we're serially executing an index scan that was planned to be parallel.", "If no run-time keys to calculate or they are ready, go ahead and pass the scankeys to the index AM."]}, {"code": "case T_IndexScan:\n\t\t\tpname = sname = \"Index Scan\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Uses an index to identify table tuples and fetches them from the relation."], "evidence_kind": "source and documentation", "explain_names": ["Index Scan"], "partial_modes": [], "comparison_data": {"node_tag": "T_IndexScan", "strategies": [], "text_names": ["Index Scan"], "initializer": "ExecInitIndexScan", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanReInitializeDSM"]}, "comparison_hash": "fb7921673ff1820c8a2d077e3523844901f32a51d84d2f6f01020dd044f11db7", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeIndexscan.c"}, "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanReInitializeDSM"]}, "13": {"facts": [{"label": "Core node tag", "value": "T_IndexScan"}, {"label": "Structured EXPLAIN Node Type", "value": "Index Scan"}, {"label": "Inputs", "value": "An index and its relation"}, {"label": "Output", "value": "Qualified table tuples"}, {"label": "Executor initializer", "value": "ExecInitIndexScan"}, {"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/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "32927dcbab5f8770ab81c2381283b44055ef26282bc4e8ae2eb30898a6712819", "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": ["If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Index Scan", "identity": "Index Scan"}], "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_indexscan/?v=13", "label": "enable_indexscan"}, {"url": "/wiki/guc/random_page_cost/?v=13", "label": "random_page_cost"}, {"url": "/wiki/indexam/?v=13", "label": "Index AM"}], "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": 1198, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1198", "sha256": "541713e0e7f1c9cc352c2b6028964d440c19d2678a4463000094c24a88c1e730", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "line": 217, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:217", "sha256": "d085ee99acfa00587e6ade3a1d9f8108a0566beedbbee3f54a50c9fc0cc2e875", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "path": "src/backend/executor/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "32927dcbab5f8770ab81c2381283b44055ef26282bc4e8ae2eb30898a6712819", "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/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 13.23 \u00b7 using-explain", "sha256": "650fd8629382d5dc8f9f8412c50ec5f32442a9ad2f98ca88e5348a7c2bd0ac7a"}, {"url": "/docs/13/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 13.23 \u00b7 using-explain", "sha256": "650fd8629382d5dc8f9f8412c50ec5f32442a9ad2f98ca88e5348a7c2bd0ac7a"}, {"url": "/docs/13/using-explain.html#USING-EXPLAIN-CAVEATS", "path": "using-explain.html", "label": "PostgreSQL 13.23 \u00b7 using-explain", "sha256": "650fd8629382d5dc8f9f8412c50ec5f32442a9ad2f98ca88e5348a7c2bd0ac7a"}], "node_tag": "T_IndexScan", "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: Index Scan.", "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.", "If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, {"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: ExecIndexScanEstimate, ExecIndexScanInitializeDSM, ExecIndexScanInitializeWorker, ExecIndexScanReInitializeDSM."]}, {"title": "Same-version manual discussion", "paragraphs": ["This is the same query as above, but we added a LIMIT so that not all the rows need be retrieved, and the planner changed its mind about what to do. Notice that the total cost and row count of the Index Scan node are shown as if it were run to completion. However, the Limit node is expected to stop after retrieving only a fifth of those rows, so its total cost is only a fifth as much, and that's the actual estimated cost of the query. This plan is preferred over adding a Limit node to the previous plan because the Limit could not avoid paying the startup cost of the bitmap scan, so the total cost would be something over 25 units with that approach.", "Notice that here the planner has chosen to \u201c materialize \u201d the inner relation of the join, by putting a Materialize plan node atop it. This means that the t2 index scan will be done just once, even though the nested-loop join node needs to read that data ten times, once for each row from the outer relation. The Materialize node saves the data in memory as it's read, and then returns the data from memory on each subsequent pass.", "Merge join requires its input data to be sorted on the join keys. In this plan the tenk1 data is sorted by using an index scan to visit the rows in the correct order, but a sequential scan and sort is preferred for onek , because there are many more rows to be visited in that table. (Sequential-scan-and-sort frequently beats an index scan for sorting many rows, because of the nonsequential disk access required by the index scan.)", "In some query plans, it is possible for a subplan node to be executed more than once. For example, the inner index scan will be executed once per outer row in the above nested-loop plan. In such cases, the loops value reports the total number of executions of the node, and the actual time and rows values shown are averages per-execution. This is done to make the numbers comparable with the way that the cost estimates are shown. Multiply by the loops value to get the total time actually spent in the node. In the above example, we spent a total of 0.220 milliseconds executing the index scans on tenk2 .", "The planner thinks (quite correctly) that this sample table is too small to bother with an index scan, so we have a plain sequential scan in which all the rows got rejected by the filter condition. But if we force an index scan to be used, we see:", "the estimated cost and row count for the Index Scan node are shown as though it were run to completion. But in reality the Limit node stopped requesting rows after it got two, so the actual row count is only 2 and the run time is less than the cost estimate would suggest. This is not an estimation error, only a discrepancy in the way the estimates and true values are displayed."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.07..229.20 rows=101 width=244)\n   Recheck Cond: (unique1 < 100)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now, let's make the condition more restrictive:"]}, {"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.04..229.43 rows=1 width=244)\n   Recheck Cond: (unique1 < 100)\n   Filter: (stringu1 = 'xxx'::name)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now let's add another condition to the WHERE clause:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["nodeIndexscan.c Routines to support indexed scans of relations", "When an ordering operator is used, tuples fetched from the index that need to be reordered are queued in a pairing heap, as ReorderTuples.", "Retrieve a tuple from the IndexScan node's currentRelation using the index specified in the IndexScanState information.", "We reach here if the index scan is not parallel, or if we're serially executing an index scan that was planned to be parallel.", "If no run-time keys to calculate or they are ready, go ahead and pass the scankeys to the index AM."]}, {"code": "case T_IndexScan:\n\t\t\tpname = sname = \"Index Scan\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Uses an index to identify table tuples and fetches them from the relation."], "evidence_kind": "source and documentation", "explain_names": ["Index Scan"], "partial_modes": [], "comparison_data": {"node_tag": "T_IndexScan", "strategies": [], "text_names": ["Index Scan"], "initializer": "ExecInitIndexScan", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanReInitializeDSM"]}, "comparison_hash": "fb7921673ff1820c8a2d077e3523844901f32a51d84d2f6f01020dd044f11db7", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeIndexscan.c"}, "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanReInitializeDSM"]}, "14": {"facts": [{"label": "Core node tag", "value": "T_IndexScan"}, {"label": "Structured EXPLAIN Node Type", "value": "Index Scan"}, {"label": "Inputs", "value": "An index and its relation"}, {"label": "Output", "value": "Qualified table tuples"}, {"label": "Executor initializer", "value": "ExecInitIndexScan"}, {"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/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "0a2884f286242223668b792de2f0c757c83a31a1ad8719d5231fb02a55b0d083", "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": ["If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Index Scan", "identity": "Index Scan"}], "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_indexscan/?v=14", "label": "enable_indexscan"}, {"url": "/wiki/guc/random_page_cost/?v=14", "label": "random_page_cost"}, {"url": "/wiki/indexam/?v=14", "label": "Index AM"}], "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": 1234, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1234", "sha256": "e091be4e2a083b8dea39ccd09beedede22c1716ef974da66c214a44f48be8c41", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "line": 219, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:219", "sha256": "72da1c5ad457f1d92a39ab73531701794df858419e3b89d6e6cb7079634e68fa", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "path": "src/backend/executor/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "0a2884f286242223668b792de2f0c757c83a31a1ad8719d5231fb02a55b0d083", "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/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 14.24 \u00b7 using-explain", "sha256": "7f5ab59cb21a035ada45ea3426c5d1cca3f781273483677f73fdd76753555206"}, {"url": "/docs/14/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 14.24 \u00b7 using-explain", "sha256": "7f5ab59cb21a035ada45ea3426c5d1cca3f781273483677f73fdd76753555206"}, {"url": "/docs/14/using-explain.html#USING-EXPLAIN-CAVEATS", "path": "using-explain.html", "label": "PostgreSQL 14.24 \u00b7 using-explain", "sha256": "7f5ab59cb21a035ada45ea3426c5d1cca3f781273483677f73fdd76753555206"}], "node_tag": "T_IndexScan", "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: Index Scan.", "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.", "If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, {"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: ExecIndexScanEstimate, ExecIndexScanInitializeDSM, ExecIndexScanInitializeWorker, ExecIndexScanReInitializeDSM."]}, {"title": "Same-version manual discussion", "paragraphs": ["This is the same query as above, but we added a LIMIT so that not all the rows need be retrieved, and the planner changed its mind about what to do. Notice that the total cost and row count of the Index Scan node are shown as if it were run to completion. However, the Limit node is expected to stop after retrieving only a fifth of those rows, so its total cost is only a fifth as much, and that's the actual estimated cost of the query. This plan is preferred over adding a Limit node to the previous plan because the Limit could not avoid paying the startup cost of the bitmap scan, so the total cost would be something over 25 units with that approach.", "Notice that here the planner has chosen to \u201c materialize \u201d the inner relation of the join, by putting a Materialize plan node atop it. This means that the t2 index scan will be done just once, even though the nested-loop join node needs to read that data ten times, once for each row from the outer relation. The Materialize node saves the data in memory as it's read, and then returns the data from memory on each subsequent pass.", "Merge join requires its input data to be sorted on the join keys. In this plan the tenk1 data is sorted by using an index scan to visit the rows in the correct order, but a sequential scan and sort is preferred for onek , because there are many more rows to be visited in that table. (Sequential-scan-and-sort frequently beats an index scan for sorting many rows, because of the nonsequential disk access required by the index scan.)", "In some query plans, it is possible for a subplan node to be executed more than once. For example, the inner index scan will be executed once per outer row in the above nested-loop plan. In such cases, the loops value reports the total number of executions of the node, and the actual time and rows values shown are averages per-execution. This is done to make the numbers comparable with the way that the cost estimates are shown. Multiply by the loops value to get the total time actually spent in the node. In the above example, we spent a total of 0.220 milliseconds executing the index scans on tenk2 .", "The planner thinks (quite correctly) that this sample table is too small to bother with an index scan, so we have a plain sequential scan in which all the rows got rejected by the filter condition. But if we force an index scan to be used, we see:", "the estimated cost and row count for the Index Scan node are shown as though it were run to completion. But in reality the Limit node stopped requesting rows after it got two, so the actual row count is only 2 and the run time is less than the cost estimate would suggest. This is not an estimation error, only a discrepancy in the way the estimates and true values are displayed."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.07..229.20 rows=101 width=244)\n   Recheck Cond: (unique1 < 100)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now, let's make the condition more restrictive:"]}, {"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.04..229.43 rows=1 width=244)\n   Recheck Cond: (unique1 < 100)\n   Filter: (stringu1 = 'xxx'::name)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now let's add another condition to the WHERE clause:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["nodeIndexscan.c Routines to support indexed scans of relations", "When an ordering operator is used, tuples fetched from the index that need to be reordered are queued in a pairing heap, as ReorderTuples.", "Retrieve a tuple from the IndexScan node's currentRelation using the index specified in the IndexScanState information.", "We reach here if the index scan is not parallel, or if we're serially executing an index scan that was planned to be parallel.", "If no run-time keys to calculate or they are ready, go ahead and pass the scankeys to the index AM."]}, {"code": "case T_IndexScan:\n\t\t\tpname = sname = \"Index Scan\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Uses an index to identify table tuples and fetches them from the relation."], "evidence_kind": "source and documentation", "explain_names": ["Index Scan"], "partial_modes": [], "comparison_data": {"node_tag": "T_IndexScan", "strategies": [], "text_names": ["Index Scan"], "initializer": "ExecInitIndexScan", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanReInitializeDSM"]}, "comparison_hash": "fb7921673ff1820c8a2d077e3523844901f32a51d84d2f6f01020dd044f11db7", "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/nodeIndexscan.c"}, "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanReInitializeDSM"]}, "15": {"facts": [{"label": "Core node tag", "value": "T_IndexScan"}, {"label": "Structured EXPLAIN Node Type", "value": "Index Scan"}, {"label": "Inputs", "value": "An index and its relation"}, {"label": "Output", "value": "Qualified table tuples"}, {"label": "Executor initializer", "value": "ExecInitIndexScan"}, {"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/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "3ac43e5f33c0ab35294481f76e40e9fbcfa04f0827e4d0be4dffbb1dc2f10bf9", "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": ["If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Index Scan", "identity": "Index Scan"}], "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_indexscan/?v=15", "label": "enable_indexscan"}, {"url": "/wiki/guc/random_page_cost/?v=15", "label": "random_page_cost"}, {"url": "/wiki/indexam/?v=15", "label": "Index AM"}], "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": 1237, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1237", "sha256": "bb3b442d0f1b098aa8707335250102f027a596cd94117308bd16d1d36b258f5c", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "line": 219, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:219", "sha256": "19836c50a272741a4eac653541e655437c2e00710a541e5348d6a277d0669d7c", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "path": "src/backend/executor/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "3ac43e5f33c0ab35294481f76e40e9fbcfa04f0827e4d0be4dffbb1dc2f10bf9", "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/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 15.19 \u00b7 using-explain", "sha256": "d1f509457c647da453d2575c772d91022a0f115dd84f9a3b20f5ff25a243648f"}, {"url": "/docs/15/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 15.19 \u00b7 using-explain", "sha256": "d1f509457c647da453d2575c772d91022a0f115dd84f9a3b20f5ff25a243648f"}, {"url": "/docs/15/using-explain.html#USING-EXPLAIN-CAVEATS", "path": "using-explain.html", "label": "PostgreSQL 15.19 \u00b7 using-explain", "sha256": "d1f509457c647da453d2575c772d91022a0f115dd84f9a3b20f5ff25a243648f"}], "node_tag": "T_IndexScan", "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: Index Scan.", "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.", "If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, {"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: ExecIndexScanEstimate, ExecIndexScanInitializeDSM, ExecIndexScanInitializeWorker, ExecIndexScanReInitializeDSM."]}, {"title": "Same-version manual discussion", "paragraphs": ["This is the same query as above, but we added a LIMIT so that not all the rows need be retrieved, and the planner changed its mind about what to do. Notice that the total cost and row count of the Index Scan node are shown as if it were run to completion. However, the Limit node is expected to stop after retrieving only a fifth of those rows, so its total cost is only a fifth as much, and that's the actual estimated cost of the query. This plan is preferred over adding a Limit node to the previous plan because the Limit could not avoid paying the startup cost of the bitmap scan, so the total cost would be something over 25 units with that approach.", "Notice that here the planner has chosen to \u201c materialize \u201d the inner relation of the join, by putting a Materialize plan node atop it. This means that the t2 index scan will be done just once, even though the nested-loop join node needs to read that data ten times, once for each row from the outer relation. The Materialize node saves the data in memory as it's read, and then returns the data from memory on each subsequent pass.", "Merge join requires its input data to be sorted on the join keys. In this plan the tenk1 data is sorted by using an index scan to visit the rows in the correct order, but a sequential scan and sort is preferred for onek , because there are many more rows to be visited in that table. (Sequential-scan-and-sort frequently beats an index scan for sorting many rows, because of the nonsequential disk access required by the index scan.)", "In some query plans, it is possible for a subplan node to be executed more than once. For example, the inner index scan will be executed once per outer row in the above nested-loop plan. In such cases, the loops value reports the total number of executions of the node, and the actual time and rows values shown are averages per-execution. This is done to make the numbers comparable with the way that the cost estimates are shown. Multiply by the loops value to get the total time actually spent in the node. In the above example, we spent a total of 0.220 milliseconds executing the index scans on tenk2 .", "The planner thinks (quite correctly) that this sample table is too small to bother with an index scan, so we have a plain sequential scan in which all the rows got rejected by the filter condition. But if we force an index scan to be used, we see:", "the estimated cost and row count for the Index Scan node are shown as though it were run to completion. But in reality the Limit node stopped requesting rows after it got two, so the actual row count is only 2 and the run time is less than the cost estimate would suggest. This is not an estimation error, only a discrepancy in the way the estimates and true values are displayed."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.07..229.20 rows=101 width=244)\n   Recheck Cond: (unique1 < 100)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now, let's make the condition more restrictive:"]}, {"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.04..229.43 rows=1 width=244)\n   Recheck Cond: (unique1 < 100)\n   Filter: (stringu1 = 'xxx'::name)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now let's add another condition to the WHERE clause:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["nodeIndexscan.c Routines to support indexed scans of relations", "When an ordering operator is used, tuples fetched from the index that need to be reordered are queued in a pairing heap, as ReorderTuples.", "Retrieve a tuple from the IndexScan node's currentRelation using the index specified in the IndexScanState information.", "We reach here if the index scan is not parallel, or if we're serially executing an index scan that was planned to be parallel.", "If no run-time keys to calculate or they are ready, go ahead and pass the scankeys to the index AM."]}, {"code": "case T_IndexScan:\n\t\t\tpname = sname = \"Index Scan\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Uses an index to identify table tuples and fetches them from the relation."], "evidence_kind": "source and documentation", "explain_names": ["Index Scan"], "partial_modes": [], "comparison_data": {"node_tag": "T_IndexScan", "strategies": [], "text_names": ["Index Scan"], "initializer": "ExecInitIndexScan", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanReInitializeDSM"]}, "comparison_hash": "fb7921673ff1820c8a2d077e3523844901f32a51d84d2f6f01020dd044f11db7", "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/nodeIndexscan.c"}, "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanReInitializeDSM"]}, "16": {"facts": [{"label": "Core node tag", "value": "T_IndexScan"}, {"label": "Structured EXPLAIN Node Type", "value": "Index Scan"}, {"label": "Inputs", "value": "An index and its relation"}, {"label": "Output", "value": "Qualified table tuples"}, {"label": "Executor initializer", "value": "ExecInitIndexScan"}, {"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/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "dd2d5791ecf1790539a41222ee396ce6c69761a2c71c491a627cb5d9454e9b50", "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": ["If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Index Scan", "identity": "Index Scan"}], "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_indexscan/?v=16", "label": "enable_indexscan"}, {"url": "/wiki/guc/random_page_cost/?v=16", "label": "random_page_cost"}, {"url": "/wiki/indexam/?v=16", "label": "Index AM"}], "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": 1270, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1270", "sha256": "8e017f0116dbea471339b40c37a667cc9f95039e7e0329c783e5e8ce194de7e1", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "line": 219, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:219", "sha256": "e48c08e555f8cb4e4bb43df516c4b8906ce9bc374b2a745d98a1fc8c22cc5099", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "path": "src/backend/executor/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "dd2d5791ecf1790539a41222ee396ce6c69761a2c71c491a627cb5d9454e9b50", "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/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 16.15 \u00b7 using-explain", "sha256": "bd8b86e5281cf0e52ad6e0e4bb8b6ff6510dac61982b44f0c1dd07d54012db3c"}, {"url": "/docs/16/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 16.15 \u00b7 using-explain", "sha256": "bd8b86e5281cf0e52ad6e0e4bb8b6ff6510dac61982b44f0c1dd07d54012db3c"}, {"url": "/docs/16/using-explain.html#USING-EXPLAIN-CAVEATS", "path": "using-explain.html", "label": "PostgreSQL 16.15 \u00b7 using-explain", "sha256": "bd8b86e5281cf0e52ad6e0e4bb8b6ff6510dac61982b44f0c1dd07d54012db3c"}], "node_tag": "T_IndexScan", "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: Index Scan.", "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.", "If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, {"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: ExecIndexScanEstimate, ExecIndexScanInitializeDSM, ExecIndexScanInitializeWorker, ExecIndexScanReInitializeDSM."]}, {"title": "Same-version manual discussion", "paragraphs": ["This is the same query as above, but we added a LIMIT so that not all the rows need be retrieved, and the planner changed its mind about what to do. Notice that the total cost and row count of the Index Scan node are shown as if it were run to completion. However, the Limit node is expected to stop after retrieving only a fifth of those rows, so its total cost is only a fifth as much, and that's the actual estimated cost of the query. This plan is preferred over adding a Limit node to the previous plan because the Limit could not avoid paying the startup cost of the bitmap scan, so the total cost would be something over 25 units with that approach.", "Notice that here the planner has chosen to \u201c materialize \u201d the inner relation of the join, by putting a Materialize plan node atop it. This means that the t2 index scan will be done just once, even though the nested-loop join node needs to read that data ten times, once for each row from the outer relation. The Materialize node saves the data in memory as it's read, and then returns the data from memory on each subsequent pass.", "Merge join requires its input data to be sorted on the join keys. In this plan the tenk1 data is sorted by using an index scan to visit the rows in the correct order, but a sequential scan and sort is preferred for onek , because there are many more rows to be visited in that table. (Sequential-scan-and-sort frequently beats an index scan for sorting many rows, because of the nonsequential disk access required by the index scan.)", "In some query plans, it is possible for a subplan node to be executed more than once. For example, the inner index scan will be executed once per outer row in the above nested-loop plan. In such cases, the loops value reports the total number of executions of the node, and the actual time and rows values shown are averages per-execution. This is done to make the numbers comparable with the way that the cost estimates are shown. Multiply by the loops value to get the total time actually spent in the node. In the above example, we spent a total of 0.220 milliseconds executing the index scans on tenk2 .", "The planner thinks (quite correctly) that this sample table is too small to bother with an index scan, so we have a plain sequential scan in which all the rows got rejected by the filter condition. But if we force an index scan to be used, we see:", "the estimated cost and row count for the Index Scan node are shown as though it were run to completion. But in reality the Limit node stopped requesting rows after it got two, so the actual row count is only 2 and the run time is less than the cost estimate would suggest. This is not an estimation error, only a discrepancy in the way the estimates and true values are displayed."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.07..229.20 rows=101 width=244)\n   Recheck Cond: (unique1 < 100)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now, let's make the condition more restrictive:"]}, {"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.04..229.43 rows=1 width=244)\n   Recheck Cond: (unique1 < 100)\n   Filter: (stringu1 = 'xxx'::name)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now let's add another condition to the WHERE clause:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["nodeIndexscan.c Routines to support indexed scans of relations", "When an ordering operator is used, tuples fetched from the index that need to be reordered are queued in a pairing heap, as ReorderTuples.", "Retrieve a tuple from the IndexScan node's currentRelation using the index specified in the IndexScanState information.", "Determine which direction to scan the index in based on the plan's scan direction and the current direction of execution.", "We reach here if the index scan is not parallel, or if we're serially executing an index scan that was planned to be parallel."]}, {"code": "case T_IndexScan:\n\t\t\tpname = sname = \"Index Scan\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Uses an index to identify table tuples and fetches them from the relation."], "evidence_kind": "source and documentation", "explain_names": ["Index Scan"], "partial_modes": [], "comparison_data": {"node_tag": "T_IndexScan", "strategies": [], "text_names": ["Index Scan"], "initializer": "ExecInitIndexScan", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanReInitializeDSM"]}, "comparison_hash": "fb7921673ff1820c8a2d077e3523844901f32a51d84d2f6f01020dd044f11db7", "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/nodeIndexscan.c"}, "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanReInitializeDSM"]}, "17": {"facts": [{"label": "Core node tag", "value": "T_IndexScan"}, {"label": "Structured EXPLAIN Node Type", "value": "Index Scan"}, {"label": "Inputs", "value": "An index and its relation"}, {"label": "Output", "value": "Qualified table tuples"}, {"label": "Executor initializer", "value": "ExecInitIndexScan"}, {"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/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "c712125cf83a298087149211eb394e20ffed062327a3b1cec3b22b24bf981274", "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": ["If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Index Scan", "identity": "Index Scan"}], "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_indexscan/?v=17", "label": "enable_indexscan"}, {"url": "/wiki/guc/random_page_cost/?v=17", "label": "random_page_cost"}, {"url": "/wiki/indexam/?v=17", "label": "Index AM"}], "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": 1459, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1459", "sha256": "741251b1a3b6d269a52a673d42eb63b02e13a5872db7b359b137086ab21b63c8", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "line": 219, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:219", "sha256": "a77576e158b94cb01fa8c5174ba133004eabdd727660323f8afc66c8d2e757b8", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "path": "src/backend/executor/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "c712125cf83a298087149211eb394e20ffed062327a3b1cec3b22b24bf981274", "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/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 17.11 \u00b7 using-explain", "sha256": "8e3422c77496cc53bfc225ccadda3e82eb23c8c362b8eb95e7fb02cdb315ea78"}, {"url": "/docs/17/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 17.11 \u00b7 using-explain", "sha256": "8e3422c77496cc53bfc225ccadda3e82eb23c8c362b8eb95e7fb02cdb315ea78"}, {"url": "/docs/17/using-explain.html#USING-EXPLAIN-CAVEATS", "path": "using-explain.html", "label": "PostgreSQL 17.11 \u00b7 using-explain", "sha256": "8e3422c77496cc53bfc225ccadda3e82eb23c8c362b8eb95e7fb02cdb315ea78"}], "node_tag": "T_IndexScan", "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: Index Scan.", "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.", "If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, {"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: ExecIndexScanEstimate, ExecIndexScanInitializeDSM, ExecIndexScanInitializeWorker, ExecIndexScanReInitializeDSM."]}, {"title": "Same-version manual discussion", "paragraphs": ["This is the same query as above, but we added a LIMIT so that not all the rows need be retrieved, and the planner changed its mind about what to do. Notice that the total cost and row count of the Index Scan node are shown as if it were run to completion. However, the Limit node is expected to stop after retrieving only a fifth of those rows, so its total cost is only a fifth as much, and that's the actual estimated cost of the query. This plan is preferred over adding a Limit node to the previous plan because the Limit could not avoid paying the startup cost of the bitmap scan, so the total cost would be something over 25 units with that approach.", "Notice that here the planner has chosen to \u201c materialize \u201d the inner relation of the join, by putting a Materialize plan node atop it. This means that the t2 index scan will be done just once, even though the nested-loop join node needs to read that data ten times, once for each row from the outer relation. The Materialize node saves the data in memory as it's read, and then returns the data from memory on each subsequent pass.", "Merge join requires its input data to be sorted on the join keys. In this example each input is sorted by using an index scan to visit the rows in the correct order; but a sequential scan and sort could also be used. (Sequential-scan-and-sort frequently beats an index scan for sorting many rows, because of the nonsequential disk access required by the index scan.)", "In some query plans, it is possible for a subplan node to be executed more than once. For example, the inner index scan will be executed once per outer row in the above nested-loop plan. In such cases, the loops value reports the total number of executions of the node, and the actual time and rows values shown are averages per-execution. This is done to make the numbers comparable with the way that the cost estimates are shown. Multiply by the loops value to get the total time actually spent in the node. In the above example, we spent a total of 0.030 milliseconds executing the index scans on tenk2 .", "The planner thinks (quite correctly) that this sample table is too small to bother with an index scan, so we have a plain sequential scan in which all the rows got rejected by the filter condition. But if we force an index scan to be used, we see:", "the estimated cost and row count for the Index Scan node are shown as though it were run to completion. But in reality the Limit node stopped requesting rows after it got two, so the actual row count is only 2 and the run time is less than the cost estimate would suggest. This is not an estimation error, only a discrepancy in the way the estimates and true values are displayed."]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.06..224.98 rows=100 width=244)\n   Recheck Cond: (unique1 < 100)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now, let's make the condition more restrictive:"]}, {"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.04..225.20 rows=1 width=244)\n   Recheck Cond: (unique1 < 100)\n   Filter: (stringu1 = 'xxx'::name)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now let's add another condition to the WHERE clause:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["nodeIndexscan.c Routines to support indexed scans of relations", "When an ordering operator is used, tuples fetched from the index that need to be reordered are queued in a pairing heap, as ReorderTuples.", "Retrieve a tuple from the IndexScan node's currentRelation using the index specified in the IndexScanState information.", "Determine which direction to scan the index in based on the plan's scan direction and the current direction of execution.", "We reach here if the index scan is not parallel, or if we're serially executing an index scan that was planned to be parallel."]}, {"code": "case T_IndexScan:\n\t\t\tpname = sname = \"Index Scan\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Uses an index to identify table tuples and fetches them from the relation."], "evidence_kind": "source and documentation", "explain_names": ["Index Scan"], "partial_modes": [], "comparison_data": {"node_tag": "T_IndexScan", "strategies": [], "text_names": ["Index Scan"], "initializer": "ExecInitIndexScan", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanReInitializeDSM"]}, "comparison_hash": "fb7921673ff1820c8a2d077e3523844901f32a51d84d2f6f01020dd044f11db7", "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/nodeIndexscan.c"}, "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanReInitializeDSM"]}, "18": {"facts": [{"label": "Core node tag", "value": "T_IndexScan"}, {"label": "Structured EXPLAIN Node Type", "value": "Index Scan"}, {"label": "Inputs", "value": "An index and its relation"}, {"label": "Output", "value": "Qualified table tuples"}, {"label": "Executor initializer", "value": "ExecInitIndexScan"}, {"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/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "7bd7ad26dc403544b67a5b151cb28157f135e87733e78204c8b279f2ceb8f4dc", "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": ["If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Index Scan", "identity": "Index Scan"}], "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_indexscan/?v=18", "label": "enable_indexscan"}, {"url": "/wiki/guc/random_page_cost/?v=18", "label": "random_page_cost"}, {"url": "/wiki/indexam/?v=18", "label": "Index AM"}], "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": 1444, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1444", "sha256": "34c86d6070224a0e981efef51f79101d6d505e5874f1684ace183034bab14bb4", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 219, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:219", "sha256": "f8a06a3f539077249b20664b2812433db6d7bd12b2c0ca633525db43d06f112a", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "7bd7ad26dc403544b67a5b151cb28157f135e87733e78204c8b279f2ceb8f4dc", "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/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}, {"url": "/docs/18/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}], "node_tag": "T_IndexScan", "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: Index Scan.", "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.", "If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, {"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: ExecIndexScanEstimate, ExecIndexScanInitializeDSM, ExecIndexScanInitializeWorker, ExecIndexScanReInitializeDSM, ExecIndexScanRetrieveInstrumentation."]}, {"title": "Same-version manual discussion", "paragraphs": ["This is the same query as above, but we added a LIMIT so that not all the rows need be retrieved, and the planner changed its mind about what to do. Notice that the total cost and row count of the Index Scan node are shown as if it were run to completion. However, the Limit node is expected to stop after retrieving only a fifth of those rows, so its total cost is only a fifth as much, and that's the actual estimated cost of the query. This plan is preferred over adding a Limit node to the previous plan because the Limit could not avoid paying the startup cost of the bitmap scan, so the total cost would be something over 25 units with that approach.", "Notice that here the planner has chosen to \u201c materialize \u201d the inner relation of the join, by putting a Materialize plan node atop it. This means that the t2 index scan will be done just once, even though the nested-loop join node needs to read that data ten times, once for each row from the outer relation. The Materialize node saves the data in memory as it's read, and then returns the data from memory on each subsequent pass.", "Merge join requires its input data to be sorted on the join keys. In this example each input is sorted by using an index scan to visit the rows in the correct order; but a sequential scan and sort could also be used. (Sequential-scan-and-sort frequently beats an index scan for sorting many rows, because of the nonsequential disk access required by the index scan.)", "In some query plans, it is possible for a subplan node to be executed more than once. For example, the inner index scan will be executed once per outer row in the above nested-loop plan. In such cases, the loops value reports the total number of executions of the node, and the actual time and rows values shown are averages per-execution. This is done to make the numbers comparable with the way that the cost estimates are shown. Multiply by the loops value to get the total time actually spent in the node. In the above example, we spent a total of 0.030 milliseconds executing the index scans on tenk2 .", "Index Scan nodes (as well as Bitmap Index Scan and Index-Only Scan nodes) show an \u201c Index Searches \u201d line that reports the total number of searches across all node executions/ loops :", "Here we see a Bitmap Index Scan node that needed 4 separate index searches. The scan had to search the index from the tenk1_thous_tenthous index root page once per integer value from the predicate's IN construct. However, the number of index searches often won't have such a simple correspondence to the query predicate:"]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.06..224.98 rows=100 width=244)\n   Recheck Cond: (unique1 < 100)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now, let's make the condition more restrictive:"]}, {"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.04..225.20 rows=1 width=244)\n   Recheck Cond: (unique1 < 100)\n   Filter: (stringu1 = 'xxx'::name)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now let's add another condition to the WHERE clause:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["nodeIndexscan.c Routines to support indexed scans of relations", "When an ordering operator is used, tuples fetched from the index that need to be reordered are queued in a pairing heap, as ReorderTuples.", "Retrieve a tuple from the IndexScan node's currentRelation using the index specified in the IndexScanState information.", "Determine which direction to scan the index in based on the plan's scan direction and the current direction of execution.", "We reach here if the index scan is not parallel, or if we're serially executing an index scan that was planned to be parallel."]}, {"code": "case T_IndexScan:\n\t\t\tpname = sname = \"Index Scan\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Uses an index to identify table tuples and fetches them from the relation."], "evidence_kind": "source and documentation", "explain_names": ["Index Scan"], "partial_modes": [], "comparison_data": {"node_tag": "T_IndexScan", "strategies": [], "text_names": ["Index Scan"], "initializer": "ExecInitIndexScan", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanReInitializeDSM", "ExecIndexScanRetrieveInstrumentation"]}, "comparison_hash": "bff6d57534571534f7bc51a46aeea96e7625a417c3e00e43facf5de0097b2963", "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/nodeIndexscan.c"}, "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanReInitializeDSM", "ExecIndexScanRetrieveInstrumentation"]}, "19": {"facts": [{"label": "Core node tag", "value": "T_IndexScan"}, {"label": "Structured EXPLAIN Node Type", "value": "Index Scan"}, {"label": "Inputs", "value": "An index and its relation"}, {"label": "Output", "value": "Qualified table tuples"}, {"label": "Executor initializer", "value": "ExecInitIndexScan"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "path": "src/backend/executor/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "e581eab35154bbffb0262523d7e1cab6a1689abab0e88c7acafd43c6be54e11a", "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": ["If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Index Scan", "identity": "Index Scan"}], "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_indexscan/?v=19", "label": "enable_indexscan"}, {"url": "/wiki/guc/random_page_cost/?v=19", "label": "random_page_cost"}, {"url": "/wiki/indexam/?v=19", "label": "Index AM"}], "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": 1456, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1456", "sha256": "8b115b1c194a4b54ae630209a741e293b1df49a9052f10b2de9ca092a48998e3", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "line": 219, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:219", "sha256": "5e39b2037bed672da55104229ecc32da5abde44c26bcad01479edcfa044d09ed", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "path": "src/backend/executor/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "e581eab35154bbffb0262523d7e1cab6a1689abab0e88c7acafd43c6be54e11a", "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/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 19beta4 \u00b7 using-explain", "sha256": "52f111fbd213e2200146319d01a9dcc2ea90001617d2a28edc52f7954c2dd70b"}, {"url": "/docs/19/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 19beta4 \u00b7 using-explain", "sha256": "52f111fbd213e2200146319d01a9dcc2ea90001617d2a28edc52f7954c2dd70b"}], "node_tag": "T_IndexScan", "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: Index Scan.", "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.", "If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, {"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: ExecIndexScanEstimate, ExecIndexScanInitializeDSM, ExecIndexScanInitializeWorker, ExecIndexScanInstrumentEstimate, ExecIndexScanReInitializeDSM, ExecIndexScanRetrieveInstrumentation."]}, {"title": "Same-version manual discussion", "paragraphs": ["This is the same query as above, but we added a LIMIT so that not all the rows need be retrieved, and the planner changed its mind about what to do. Notice that the total cost and row count of the Index Scan node are shown as if it were run to completion. However, the Limit node is expected to stop after retrieving only a fifth of those rows, so its total cost is only a fifth as much, and that's the actual estimated cost of the query. This plan is preferred over adding a Limit node to the previous plan because the Limit could not avoid paying the startup cost of the bitmap scan, so the total cost would be something over 25 units with that approach.", "Notice that here the planner has chosen to \u201c materialize \u201d the inner relation of the join, by putting a Materialize plan node atop it. This means that the t2 index scan will be done just once, even though the nested-loop join node needs to read that data ten times, once for each row from the outer relation. The Materialize node saves the data in memory as it's read, and then returns the data from memory on each subsequent pass.", "Merge join requires its input data to be sorted on the join keys. In this example each input is sorted by using an index scan to visit the rows in the correct order; but a sequential scan and sort could also be used. (Sequential-scan-and-sort frequently beats an index scan for sorting many rows, because of the nonsequential disk access required by the index scan.)", "In some query plans, it is possible for a subplan node to be executed more than once. For example, the inner index scan will be executed once per outer row in the above nested-loop plan. In such cases, the loops value reports the total number of executions of the node, and the actual time and rows values shown are averages per-execution. This is done to make the numbers comparable with the way that the cost estimates are shown. Multiply by the loops value to get the total time actually spent in the node and the total number of rows processed by the node across all executions. In the above example, we spent a total of 0.030 milliseconds executing the index scans on tenk2 , and they handled a total of 10 rows.", "Index Scan nodes (as well as Bitmap Index Scan and Index-Only Scan nodes) show an \u201c Index Searches \u201d line that reports the total number of searches across all node executions/ loops :", "Here we see a Bitmap Index Scan node that needed 4 separate index searches. The scan had to search the index from the tenk1_thous_tenthous index root page once per integer value from the predicate's IN construct. However, the number of index searches often won't have such a simple correspondence to the query predicate:"]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.06..224.98 rows=100 width=244)\n   Recheck Cond: (unique1 < 100)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now, let's make the condition more restrictive:"]}, {"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.04..225.20 rows=1 width=244)\n   Recheck Cond: (unique1 < 100)\n   Filter: (stringu1 = 'xxx'::name)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now let's add another condition to the WHERE clause:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["nodeIndexscan.c Routines to support indexed scans of relations", "When an ordering operator is used, tuples fetched from the index that need to be reordered are queued in a pairing heap, as ReorderTuples.", "Retrieve a tuple from the IndexScan node's currentRelation using the index specified in the IndexScanState information.", "Determine which direction to scan the index in based on the plan's scan direction and the current direction of execution.", "We reach here if the index scan is not parallel, or if we're serially executing an index scan that was planned to be parallel."]}, {"code": "case T_IndexScan:\n\t\t\tpname = sname = \"Index Scan\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Uses an index to identify table tuples and fetches them from the relation."], "evidence_kind": "source and documentation", "explain_names": ["Index Scan"], "partial_modes": [], "comparison_data": {"node_tag": "T_IndexScan", "strategies": [], "text_names": ["Index Scan"], "initializer": "ExecInitIndexScan", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanInstrumentEstimate", "ExecIndexScanReInitializeDSM", "ExecIndexScanRetrieveInstrumentation"]}, "comparison_hash": "358cca44ccdd44b7e726c069322da3878096f0631d5d3134722c1697b59dc486", "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/nodeIndexscan.c"}, "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanInstrumentEstimate", "ExecIndexScanReInitializeDSM", "ExecIndexScanRetrieveInstrumentation"]}, "20": {"facts": [{"label": "Core node tag", "value": "T_IndexScan"}, {"label": "Structured EXPLAIN Node Type", "value": "Index Scan"}, {"label": "Inputs", "value": "An index and its relation"}, {"label": "Output", "value": "Qualified table tuples"}, {"label": "Executor initializer", "value": "ExecInitIndexScan"}, {"label": "Memory mechanism", "value": "unclassified"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "path": "src/backend/executor/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "787e2c0d351730d3bee6d74032b15db8ae89a8c0ea63e876cd01b03d04a8319c", "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": ["If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Index Scan", "identity": "Index Scan"}], "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_indexscan/?v=20", "label": "enable_indexscan"}, {"url": "/wiki/guc/random_page_cost/?v=20", "label": "random_page_cost"}, {"url": "/wiki/indexam/?v=20", "label": "Index AM"}], "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": 1456, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1456", "sha256": "13402758013520451539427b5993db06d463ca11c4e2d4cc5444e82367688077", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "line": 219, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:219", "sha256": "5e39b2037bed672da55104229ecc32da5abde44c26bcad01479edcfa044d09ed", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "path": "src/backend/executor/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "787e2c0d351730d3bee6d74032b15db8ae89a8c0ea63e876cd01b03d04a8319c", "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/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 20devel \u00b7 using-explain", "sha256": "99cfea3035876ea63f88b75ba8c964b51a32a70606544e09f769c0b60a234b31"}, {"url": "/docs/devel/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 20devel \u00b7 using-explain", "sha256": "99cfea3035876ea63f88b75ba8c964b51a32a70606544e09f769c0b60a234b31"}], "node_tag": "T_IndexScan", "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: Index Scan.", "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.", "If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, {"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: ExecIndexScanEstimate, ExecIndexScanInitializeDSM, ExecIndexScanInitializeWorker, ExecIndexScanInstrumentEstimate, ExecIndexScanReInitializeDSM, ExecIndexScanRetrieveInstrumentation."]}, {"title": "Same-version manual discussion", "paragraphs": ["This is the same query as above, but we added a LIMIT so that not all the rows need be retrieved, and the planner changed its mind about what to do. Notice that the total cost and row count of the Index Scan node are shown as if it were run to completion. However, the Limit node is expected to stop after retrieving only a fifth of those rows, so its total cost is only a fifth as much, and that's the actual estimated cost of the query. This plan is preferred over adding a Limit node to the previous plan because the Limit could not avoid paying the startup cost of the bitmap scan, so the total cost would be something over 25 units with that approach.", "Notice that here the planner has chosen to \u201c materialize \u201d the inner relation of the join, by putting a Materialize plan node atop it. This means that the t2 index scan will be done just once, even though the nested-loop join node needs to read that data ten times, once for each row from the outer relation. The Materialize node saves the data in memory as it's read, and then returns the data from memory on each subsequent pass.", "Merge join requires its input data to be sorted on the join keys. In this example each input is sorted by using an index scan to visit the rows in the correct order; but a sequential scan and sort could also be used. (Sequential-scan-and-sort frequently beats an index scan for sorting many rows, because of the nonsequential disk access required by the index scan.)", "In some query plans, it is possible for a subplan node to be executed more than once. For example, the inner index scan will be executed once per outer row in the above nested-loop plan. In such cases, the loops value reports the total number of executions of the node, and the actual time and rows values shown are averages per-execution. This is done to make the numbers comparable with the way that the cost estimates are shown. Multiply by the loops value to get the total time actually spent in the node and the total number of rows processed by the node across all executions. In the above example, we spent a total of 0.030 milliseconds executing the index scans on tenk2 , and they handled a total of 10 rows.", "Index Scan nodes (as well as Bitmap Index Scan and Index-Only Scan nodes) show an \u201c Index Searches \u201d line that reports the total number of searches across all node executions/ loops :", "Here we see a Bitmap Index Scan node that needed 4 separate index searches. The scan had to search the index from the tenk1_thous_tenthous index root page once per integer value from the predicate's IN construct. However, the number of index searches often won't have such a simple correspondence to the query predicate:"]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.06..224.98 rows=100 width=244)\n   Recheck Cond: (unique1 < 100)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now, let's make the condition more restrictive:"]}, {"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.04..225.20 rows=1 width=244)\n   Recheck Cond: (unique1 < 100)\n   Filter: (stringu1 = 'xxx'::name)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now let's add another condition to the WHERE clause:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["nodeIndexscan.c Routines to support indexed scans of relations", "When an ordering operator is used, tuples fetched from the index that need to be reordered are queued in a pairing heap, as ReorderTuples.", "Retrieve a tuple from the IndexScan node's currentRelation using the index specified in the IndexScanState information.", "Determine which direction to scan the index in based on the plan's scan direction and the current direction of execution.", "We reach here if the index scan is not parallel, or if we're serially executing an index scan that was planned to be parallel."]}, {"code": "case T_IndexScan:\n\t\t\tpname = sname = \"Index Scan\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Uses an index to identify table tuples and fetches them from the relation."], "evidence_kind": "source and documentation", "explain_names": ["Index Scan"], "partial_modes": [], "comparison_data": {"node_tag": "T_IndexScan", "strategies": [], "text_names": ["Index Scan"], "initializer": "ExecInitIndexScan", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanInstrumentEstimate", "ExecIndexScanReInitializeDSM", "ExecIndexScanRetrieveInstrumentation"]}, "comparison_hash": "358cca44ccdd44b7e726c069322da3878096f0631d5d3134722c1697b59dc486", "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/nodeIndexscan.c"}, "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanInstrumentEstimate", "ExecIndexScanReInitializeDSM", "ExecIndexScanRetrieveInstrumentation"]}}}, "snapshot": {"facts": [{"label": "Core node tag", "value": "T_IndexScan"}, {"label": "Structured EXPLAIN Node Type", "value": "Index Scan"}, {"label": "Inputs", "value": "An index and its relation"}, {"label": "Output", "value": "Qualified table tuples"}, {"label": "Executor initializer", "value": "ExecInitIndexScan"}, {"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/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "7bd7ad26dc403544b67a5b151cb28157f135e87733e78204c8b279f2ceb8f4dc", "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": ["If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Index Scan", "identity": "Index Scan"}], "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_indexscan/?v=18", "label": "enable_indexscan"}, {"url": "/wiki/guc/random_page_cost/?v=18", "label": "random_page_cost"}, {"url": "/wiki/indexam/?v=18", "label": "Index AM"}], "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": 1444, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1444", "sha256": "34c86d6070224a0e981efef51f79101d6d505e5874f1684ace183034bab14bb4", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 219, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:219", "sha256": "f8a06a3f539077249b20664b2812433db6d7bd12b2c0ca633525db43d06f112a", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeIndexscan.c", "label": "src/backend/executor/nodeIndexscan.c", "sha256": "7bd7ad26dc403544b67a5b151cb28157f135e87733e78204c8b279f2ceb8f4dc", "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/using-explain.html#USING-EXPLAIN-BASICS", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}, {"url": "/docs/18/using-explain.html#USING-EXPLAIN-ANALYZE", "path": "using-explain.html", "label": "PostgreSQL 18.6 \u00b7 using-explain", "sha256": "60040c30180093418a0affe56dd27dff9df2504b705b38039589e458bf5c31ed"}], "node_tag": "T_IndexScan", "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: Index Scan.", "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.", "If the index was lossy, we have to recheck the index quals using the fetched tuple.", "If the index was lossy, we have to recheck the index quals and ORDER BY expressions using the fetched tuple."]}, {"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: ExecIndexScanEstimate, ExecIndexScanInitializeDSM, ExecIndexScanInitializeWorker, ExecIndexScanReInitializeDSM, ExecIndexScanRetrieveInstrumentation."]}, {"title": "Same-version manual discussion", "paragraphs": ["This is the same query as above, but we added a LIMIT so that not all the rows need be retrieved, and the planner changed its mind about what to do. Notice that the total cost and row count of the Index Scan node are shown as if it were run to completion. However, the Limit node is expected to stop after retrieving only a fifth of those rows, so its total cost is only a fifth as much, and that's the actual estimated cost of the query. This plan is preferred over adding a Limit node to the previous plan because the Limit could not avoid paying the startup cost of the bitmap scan, so the total cost would be something over 25 units with that approach.", "Notice that here the planner has chosen to \u201c materialize \u201d the inner relation of the join, by putting a Materialize plan node atop it. This means that the t2 index scan will be done just once, even though the nested-loop join node needs to read that data ten times, once for each row from the outer relation. The Materialize node saves the data in memory as it's read, and then returns the data from memory on each subsequent pass.", "Merge join requires its input data to be sorted on the join keys. In this example each input is sorted by using an index scan to visit the rows in the correct order; but a sequential scan and sort could also be used. (Sequential-scan-and-sort frequently beats an index scan for sorting many rows, because of the nonsequential disk access required by the index scan.)", "In some query plans, it is possible for a subplan node to be executed more than once. For example, the inner index scan will be executed once per outer row in the above nested-loop plan. In such cases, the loops value reports the total number of executions of the node, and the actual time and rows values shown are averages per-execution. This is done to make the numbers comparable with the way that the cost estimates are shown. Multiply by the loops value to get the total time actually spent in the node. In the above example, we spent a total of 0.030 milliseconds executing the index scans on tenk2 .", "Index Scan nodes (as well as Bitmap Index Scan and Index-Only Scan nodes) show an \u201c Index Searches \u201d line that reports the total number of searches across all node executions/ loops :", "Here we see a Bitmap Index Scan node that needed 4 separate index searches. The scan had to search the index from the tenk1_thous_tenthous index root page once per integer value from the predicate's IN construct. However, the number of index searches often won't have such a simple correspondence to the query predicate:"]}, {"title": "Examples from this manual build", "blocks": [{"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.06..224.98 rows=100 width=244)\n   Recheck Cond: (unique1 < 100)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now, let's make the condition more restrictive:"]}, {"code": "EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';\n\n                                  QUERY PLAN\n------------------------------------------------------------------------------\n Bitmap Heap Scan on tenk1  (cost=5.04..225.20 rows=1 width=244)\n   Recheck Cond: (unique1 < 100)\n   Filter: (stringu1 = 'xxx'::name)\n   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)\n         Index Cond: (unique1 < 100)", "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.", "Now let's add another condition to the WHERE clause:"]}]}, {"title": "Executor implementation notes", "paragraphs": ["nodeIndexscan.c Routines to support indexed scans of relations", "When an ordering operator is used, tuples fetched from the index that need to be reordered are queued in a pairing heap, as ReorderTuples.", "Retrieve a tuple from the IndexScan node's currentRelation using the index specified in the IndexScanState information.", "Determine which direction to scan the index in based on the plan's scan direction and the current direction of execution.", "We reach here if the index scan is not parallel, or if we're serially executing an index scan that was planned to be parallel."]}, {"code": "case T_IndexScan:\n\t\t\tpname = sname = \"Index Scan\";\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": [], "description": ["Uses an index to identify table tuples and fetches them from the relation."], "evidence_kind": "source and documentation", "explain_names": ["Index Scan"], "partial_modes": [], "comparison_data": {"node_tag": "T_IndexScan", "strategies": [], "text_names": ["Index Scan"], "initializer": "ExecInitIndexScan", "partial_modes": [], "memory_mechanism": "unclassified", "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanReInitializeDSM", "ExecIndexScanRetrieveInstrumentation"]}, "comparison_hash": "bff6d57534571534f7bc51a46aeea96e7625a417c3e00e43facf5de0097b2963", "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/nodeIndexscan.c"}, "parallel_callbacks": ["ExecIndexScanEstimate", "ExecIndexScanInitializeDSM", "ExecIndexScanInitializeWorker", "ExecIndexScanReInitializeDSM", "ExecIndexScanRetrieveInstrumentation"]}, "comparison": {"left": "17", "right": "18", "status": "changed", "diff": "--- PostgreSQL 17\n+++ PostgreSQL 18\n@@ -6,7 +6,8 @@\n     \"ExecIndexScanEstimate\",\n     \"ExecIndexScanInitializeDSM\",\n     \"ExecIndexScanInitializeWorker\",\n-    \"ExecIndexScanReInitializeDSM\"\n+    \"ExecIndexScanReInitializeDSM\",\n+    \"ExecIndexScanRetrieveInstrumentation\"\n   ],\n   \"partial_modes\": [],\n   \"strategies\": [],"}}