{"Entry":{"collection":"sql","key":"explain","name":"EXPLAIN","aliases":["explain"],"metadata":{"aliases":["explain"],"changed_in":["7.2","7.4","9.0","9.2","10","12","13","16","17","19"],"changes":[{"from":"6.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","usage"],"removed":[]},"status":"changed","synopsis":null,"to":"6.5"},{"from":"6.5","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":["description","usage"],"removed":[]},"status":"changed","synopsis":null,"to":"7.0"},{"from":"7.0","purpose_changed":false,"renamed":{"from_file":"sql-explain.htm","to_file":"sql-explain.html"},"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"7.1"},{"from":"7.1","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":["description","usage"],"removed":[]},"status":"changed","synopsis":{"added":["EXPLAIN [ ANALYZE ] [ VERBOSE ] query"],"removed":[]},"to":"7.2"},{"from":"7.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","usage"],"removed":[]},"status":"changed","synopsis":null,"to":"7.3"},{"from":"7.3","purpose_changed":false,"renamed":null,"sections":{"added":["parameters","notes","examples"],"changed":["description","compatibility"],"removed":["usage"]},"status":"changed","synopsis":{"added":["EXPLAIN [ ANALYZE ] [ VERBOSE ] statement"],"removed":["EXPLAIN [ ANALYZE ] [ VERBOSE ] query"]},"to":"7.4"},{"from":"7.4","purpose_changed":false,"renamed":null,"sections":{"added":["see_also"],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"8.0"},{"from":"8.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.1"},{"from":"8.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.2"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"8.3"},{"from":"8.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"8.4"},{"from":"8.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes","examples"],"removed":[]},"status":"changed","synopsis":{"added":["EXPLAIN [ ( option [, ...] ) ] statement","ANALYZE [ boolean ]","VERBOSE [ boolean ]","COSTS [ boolean ]","BUFFERS [ boolean ]","FORMAT { TEXT | XML | JSON | YAML }"],"removed":[]},"to":"9.0"},{"from":"9.1","purpose_changed":false,"renamed":null,"sections":{"added":["outputs"],"changed":["description","parameters","notes","examples"],"removed":[]},"status":"changed","synopsis":{"added":["TIMING [ boolean ]"],"removed":[]},"to":"9.2"},{"from":"9.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"9.3"},{"from":"9.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"9.4"},{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":{"added":["SUMMARY [ boolean ]"],"removed":[]},"to":"10"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":{"added":["SETTINGS [ boolean ]"],"removed":[]},"to":"12"},{"from":"12","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","examples"],"removed":[]},"status":"changed","synopsis":{"added":["WAL [ boolean ]"],"removed":[]},"to":"13"},{"from":"13","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"14"},{"from":"14","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"15"},{"from":"15","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","examples"],"removed":[]},"status":"changed","synopsis":{"added":["GENERIC_PLAN [ boolean ]"],"removed":[]},"to":"16"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["SERIALIZE [ { NONE | TEXT | BINARY } ]","SUMMARY [ boolean ]","MEMORY [ boolean ]"],"removed":["EXPLAIN [ ( option [, ...] ) ] statement","EXPLAIN [ ANALYZE ] [ VERBOSE ] statement"]},"to":"17"},{"from":"17","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"18"},{"from":"18","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":{"added":["IO [ boolean ]"],"removed":[]},"to":"19"}],"content_hash":"e0aa398ab6386aa09cf48a38cafbe7c8400cdbfcb06b30c53247fddfd8cbca95","editorial":{},"first_version":"6.4","group":"query","imported_at":"2026-09-30T17:43:39.043274+08:00","last_version":"20","name":"EXPLAIN","object":"","position":11002,"present_in":["6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"show the execution plan of a statement","purpose_zh":"","related":["analyze"],"slug":"explain","source_rev":"a709ab85","synopsis":"EXPLAIN [ ( option [, ...] ) ] statement\nwhere option can be one of:\n\nANALYZE [ boolean ]\nVERBOSE [ boolean ]\nCOSTS [ boolean ]\nSETTINGS [ boolean ]\nGENERIC_PLAN [ boolean ]\nBUFFERS [ boolean ]\nSERIALIZE [ { NONE | TEXT | BINARY } ]\nWAL [ boolean ]\nTIMING [ boolean ]\nSUMMARY [ boolean ]\nMEMORY [ boolean ]\nIO [ boolean ]\nFORMAT { TEXT | XML | JSON | YAML }","verb":"EXPLAIN"}},"Definition":{"Collection":"sql","Key":"explain","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"explain","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-EXPLAIN","file":"sql-explain.html","lang":"en","name":"EXPLAIN","purpose":"show the execution plan of a statement","purpose_zh":"","related":["analyze"],"sections":[{"html":"\u003cp\u003eThis command displays the execution plan that the \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e planner generates for the supplied statement. The execution plan shows how the table(s) referenced by the statement will be scanned — by plain sequential scan, index scan, etc. — and if multiple tables are referenced, what join algorithms will be used to bring together the required rows from each input table.\u003c/p\u003e\u003cp\u003eThe most critical part of the display is the estimated statement execution cost, which is the planner's guess at how long it will take to run the statement (measured in cost units that are arbitrary, but conventionally mean disk page fetches). Actually two numbers are shown: the start-up cost before the first row can be returned, and the total cost to return all the rows. For most queries the total cost is what matters, but in contexts such as a subquery in \u003ccode class=\"literal\"\u003eEXISTS\u003c/code\u003e, the planner will choose the smallest start-up cost instead of the smallest total cost (since the executor will stop after getting one row, anyway). Also, if you limit the number of rows to return with a \u003ccode class=\"literal\"\u003eLIMIT\u003c/code\u003e clause, the planner makes an appropriate interpolation between the endpoint costs to estimate which plan is really the cheapest.\u003c/p\u003e\u003cp\u003eThe \u003ccode class=\"literal\"\u003eANALYZE\u003c/code\u003e option causes the statement to be actually executed, not only planned. Then actual run time statistics are added to the display, including the total elapsed time expended within each plan node (in milliseconds) and the total number of rows it actually returned. This is useful for seeing whether the planner's estimates are close to reality.\u003c/p\u003e\u003cdiv class=\"important\"\u003e\u003ch3\u003eImportant\u003c/h3\u003e\u003cp\u003eKeep in mind that the statement is actually executed when the \u003ccode class=\"literal\"\u003eANALYZE\u003c/code\u003e option is used. Although \u003ccode class=\"command\"\u003eEXPLAIN\u003c/code\u003e will discard any output that a \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e would return, other side effects of the statement will happen as usual. If you wish to use \u003ccode class=\"command\"\u003eEXPLAIN ANALYZE\u003c/code\u003e on an \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e, \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e, \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e, \u003ccode class=\"command\"\u003eCREATE TABLE AS\u003c/code\u003e, or \u003ccode class=\"command\"\u003eEXECUTE\u003c/code\u003e statement without letting the command affect your data, use this approach:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eBEGIN;\nEXPLAIN ANALYZE ...;\nROLLBACK;\n\u003c/pre\u003e\u003c/div\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eANALYZE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eCarry out the command and show actual run times and other statistics. This parameter defaults to \u003ccode class=\"literal\"\u003eFALSE\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eVERBOSE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eDisplay additional information regarding the plan. Specifically, include the output column list for each node in the plan tree, schema-qualify table and function names, always label variables in expressions with their range table alias, and always print the name of each trigger for which statistics are displayed. The query identifier will also be displayed if one has been computed, see \u003ca href=\"/docs/18/runtime-config-statistics.html#GUC-COMPUTE-QUERY-ID\"\u003ecompute_query_id\u003c/a\u003e for more details. This parameter defaults to \u003ccode class=\"literal\"\u003eFALSE\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eCOSTS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eInclude information on the estimated startup and total cost of each plan node, as well as the estimated number of rows and the estimated width of each row. This parameter defaults to \u003ccode class=\"literal\"\u003eTRUE\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSETTINGS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eInclude information on configuration parameters. Specifically, include options affecting query planning with value different from the built-in default value. This parameter defaults to \u003ccode class=\"literal\"\u003eFALSE\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eGENERIC_PLAN\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAllow the statement to contain parameter placeholders like \u003ccode class=\"literal\"\u003e$1\u003c/code\u003e, and generate a generic plan that does not depend on the values of those parameters. See \u003ca href=\"/docs/18/sql-prepare.html\" title=\"PREPARE\"\u003e\u003ccode class=\"command\"\u003ePREPARE\u003c/code\u003e\u003c/a\u003e for details about generic plans and the types of statement that support parameters. This parameter cannot be used together with \u003ccode class=\"literal\"\u003eANALYZE\u003c/code\u003e. It defaults to \u003ccode class=\"literal\"\u003eFALSE\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eBUFFERS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eInclude information on buffer usage. Specifically, include the number of shared blocks hit, read, dirtied, and written, the number of local blocks hit, read, dirtied, and written, the number of temp blocks read and written, and the time spent reading and writing data file blocks, local blocks and temporary file blocks (in milliseconds) if \u003ca href=\"/docs/18/runtime-config-statistics.html#GUC-TRACK-IO-TIMING\"\u003etrack_io_timing\u003c/a\u003e is enabled. A \u003cspan class=\"emphasis\"\u003e\u003cem\u003ehit\u003c/em\u003e\u003c/span\u003e means that a read was avoided because the block was found already in cache when needed. Shared blocks contain data from regular tables and indexes; local blocks contain data from temporary tables and indexes; while temporary blocks contain short-term working data used in sorts, hashes, Materialize plan nodes, and similar cases. The number of blocks \u003cspan class=\"emphasis\"\u003e\u003cem\u003edirtied\u003c/em\u003e\u003c/span\u003e indicates the number of previously unmodified blocks that were changed by this query; while the number of blocks \u003cspan class=\"emphasis\"\u003e\u003cem\u003ewritten\u003c/em\u003e\u003c/span\u003e indicates the number of previously-dirtied blocks evicted from cache by this backend during query processing. The number of blocks shown for an upper-level node includes those used by all its child nodes. In text format, only non-zero values are printed. Buffers information is automatically included when \u003ccode class=\"literal\"\u003eANALYZE\u003c/code\u003e is used.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSERIALIZE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eInclude information on the cost of \u003cem class=\"firstterm\"\u003eserializing\u003c/em\u003e the query's output data, that is converting it to text or binary format to send to the client. This can be a significant part of the time required for regular execution of the query, if the datatype output functions are expensive or if \u003cacronym\u003eTOAST\u003c/acronym\u003eed values must be fetched from out-of-line storage. \u003ccode class=\"command\"\u003eEXPLAIN\u003c/code\u003e's default behavior, \u003ccode class=\"literal\"\u003eSERIALIZE NONE\u003c/code\u003e, does not perform these conversions. If \u003ccode class=\"literal\"\u003eSERIALIZE TEXT\u003c/code\u003e or \u003ccode class=\"literal\"\u003eSERIALIZE BINARY\u003c/code\u003e is specified, the appropriate conversions are performed, and the time spent doing so is measured (unless \u003ccode class=\"literal\"\u003eTIMING OFF\u003c/code\u003e is specified). If the \u003ccode class=\"literal\"\u003eBUFFERS\u003c/code\u003e option is also specified, then any buffer accesses involved in the conversions are counted too. In no case, however, will \u003ccode class=\"command\"\u003eEXPLAIN\u003c/code\u003e actually send the resulting data to the client; hence network transmission costs cannot be investigated this way. Serialization may only be enabled when \u003ccode class=\"literal\"\u003eANALYZE\u003c/code\u003e is also enabled. If \u003ccode class=\"literal\"\u003eSERIALIZE\u003c/code\u003e is written without an argument, \u003ccode class=\"literal\"\u003eTEXT\u003c/code\u003e is assumed.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWAL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eInclude information on WAL record generation. Specifically, include the number of records, number of full page images (fpi), the amount of WAL generated in bytes and the number of times the WAL buffers became full. In text format, only non-zero values are printed. This parameter may only be used when \u003ccode class=\"literal\"\u003eANALYZE\u003c/code\u003e is also enabled. It defaults to \u003ccode class=\"literal\"\u003eFALSE\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTIMING\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eInclude actual startup time and time spent in each node in the output. The overhead of repeatedly reading the system clock can slow down the query significantly on some systems, so it may be useful to set this parameter to \u003ccode class=\"literal\"\u003eFALSE\u003c/code\u003e when only actual row counts, and not exact times, are needed. Run time of the entire statement is always measured, even when node-level timing is turned off with this option. This parameter may only be used when \u003ccode class=\"literal\"\u003eANALYZE\u003c/code\u003e is also enabled. It defaults to \u003ccode class=\"literal\"\u003eTRUE\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSUMMARY\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eInclude summary information (e.g., totaled timing information) after the query plan. Summary information is included by default when \u003ccode class=\"literal\"\u003eANALYZE\u003c/code\u003e is used but otherwise is not included by default, but can be enabled using this option. Planning time in \u003ccode class=\"command\"\u003eEXPLAIN EXECUTE\u003c/code\u003e includes the time required to fetch the plan from the cache and the time required for re-planning, if necessary.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eMEMORY\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eInclude information on memory consumption by the query planning phase. Specifically, include the precise amount of storage used by planner in-memory structures, as well as total memory considering allocation overhead. This parameter defaults to \u003ccode class=\"literal\"\u003eFALSE\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eFORMAT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecify the output format, which can be TEXT, XML, JSON, or YAML. Non-text output contains the same information as the text output format, but is easier for programs to parse. This parameter defaults to \u003ccode class=\"literal\"\u003eTEXT\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies whether the selected option should be turned on or off. You can write \u003ccode class=\"literal\"\u003eTRUE\u003c/code\u003e, \u003ccode class=\"literal\"\u003eON\u003c/code\u003e, or \u003ccode class=\"literal\"\u003e1\u003c/code\u003e to enable the option, and \u003ccode class=\"literal\"\u003eFALSE\u003c/code\u003e, \u003ccode class=\"literal\"\u003eOFF\u003c/code\u003e, or \u003ccode class=\"literal\"\u003e0\u003c/code\u003e to disable it. The \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e value can also be omitted, in which case \u003ccode class=\"literal\"\u003eTRUE\u003c/code\u003e is assumed.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAny \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e, \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e, \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e, \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e, \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e, \u003ccode class=\"command\"\u003eEXECUTE\u003c/code\u003e, \u003ccode class=\"command\"\u003eDECLARE\u003c/code\u003e, \u003ccode class=\"command\"\u003eCREATE TABLE AS\u003c/code\u003e, or \u003ccode class=\"command\"\u003eCREATE MATERIALIZED VIEW AS\u003c/code\u003e statement, whose execution plan you wish to see.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eThe command's result is a textual description of the plan selected for the \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e, optionally annotated with execution statistics. \u003ca href=\"/docs/18/using-explain.html\" title=\"14.1. Using EXPLAIN\"\u003eSection 14.1\u003c/a\u003e describes the information provided.\u003c/p\u003e","key":"outputs","title":"Outputs"},{"html":"\u003cp\u003eIn order to allow the \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e query planner to make reasonably informed decisions when optimizing queries, the \u003ca href=\"/docs/18/catalog-pg-statistic.html\" title=\"52.51. pg_statistic\"\u003e\u003ccode class=\"structname\"\u003epg_statistic\u003c/code\u003e\u003c/a\u003e data should be up-to-date for all tables used in the query. Normally the \u003ca href=\"/docs/18/routine-vacuuming.html#AUTOVACUUM\" title=\"24.1.6. The Autovacuum Daemon\"\u003eautovacuum daemon\u003c/a\u003e will take care of that automatically. But if a table has recently had substantial changes in its contents, you might need to do a manual \u003ca href=\"/docs/18/sql-analyze.html\" title=\"ANALYZE\"\u003e\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e\u003c/a\u003e rather than wait for autovacuum to catch up with the changes.\u003c/p\u003e\u003cp\u003eIn order to measure the run-time cost of each node in the execution plan, the current implementation of \u003ccode class=\"command\"\u003eEXPLAIN ANALYZE\u003c/code\u003e adds profiling overhead to query execution. As a result, running \u003ccode class=\"command\"\u003eEXPLAIN ANALYZE\u003c/code\u003e on a query can sometimes take significantly longer than executing the query normally. The amount of overhead depends on the nature of the query, as well as the platform being used. The worst case occurs for plan nodes that in themselves require very little time per execution, and on machines that have relatively slow operating system calls for obtaining the time of day.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eTo show the plan for a simple query on a table with a single \u003ccode class=\"type\"\u003einteger\u003c/code\u003e column and 10000 rows:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eEXPLAIN SELECT * FROM foo;\n\n                       QUERY PLAN\n---------------------------------------------------------\n Seq Scan on foo  (cost=0.00..155.00 rows=10000 width=4)\n(1 row)\n\u003c/pre\u003e\u003cp\u003eHere is the same query, with JSON output formatting:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eEXPLAIN (FORMAT JSON) SELECT * FROM foo;\n           QUERY PLAN\n--------------------------------\n [                             +\n   {                           +\n     \"Plan\": {                 +\n       \"Node Type\": \"Seq Scan\",+\n       \"Relation Name\": \"foo\", +\n       \"Alias\": \"foo\",         +\n       \"Startup Cost\": 0.00,   +\n       \"Total Cost\": 155.00,   +\n       \"Plan Rows\": 10000,     +\n       \"Plan Width\": 4         +\n     }                         +\n   }                           +\n ]\n(1 row)\n\u003c/pre\u003e\u003cp\u003eIf there is an index and we use a query with an indexable \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e condition, \u003ccode class=\"command\"\u003eEXPLAIN\u003c/code\u003e might show a different plan:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eEXPLAIN SELECT * FROM foo WHERE i = 4;\n\n                         QUERY PLAN\n--------------------------------------------------------------\n Index Scan using fi on foo  (cost=0.00..5.98 rows=1 width=4)\n   Index Cond: (i = 4)\n(2 rows)\n\u003c/pre\u003e\u003cp\u003eHere is the same query, but in YAML format:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eEXPLAIN (FORMAT YAML) SELECT * FROM foo WHERE i='4';\n          QUERY PLAN\n-------------------------------\n - Plan:                      +\n     Node Type: \"Index Scan\"  +\n     Scan Direction: \"Forward\"+\n     Index Name: \"fi\"         +\n     Relation Name: \"foo\"     +\n     Alias: \"foo\"             +\n     Startup Cost: 0.00       +\n     Total Cost: 5.98         +\n     Plan Rows: 1             +\n     Plan Width: 4            +\n     Index Cond: \"(i = 4)\"\n(1 row)\n\u003c/pre\u003e\u003cp\u003eXML format is left as an exercise for the reader.\u003c/p\u003e\u003cp\u003eHere is the same plan with cost estimates suppressed:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eEXPLAIN (COSTS FALSE) SELECT * FROM foo WHERE i = 4;\n\n        QUERY PLAN\n----------------------------\n Index Scan using fi on foo\n   Index Cond: (i = 4)\n(2 rows)\n\u003c/pre\u003e\u003cp\u003eHere is an example of a query plan for a query using an aggregate function:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eEXPLAIN SELECT sum(i) FROM foo WHERE i \u0026lt; 10;\n\n                             QUERY PLAN\n-------------------------------------------------------------------​--\n Aggregate  (cost=23.93..23.93 rows=1 width=4)\n   -\u0026gt;  Index Scan using fi on foo  (cost=0.00..23.92 rows=6 width=4)\n         Index Cond: (i \u0026lt; 10)\n(3 rows)\n\u003c/pre\u003e\u003cp\u003eHere is an example of using \u003ccode class=\"command\"\u003eEXPLAIN EXECUTE\u003c/code\u003e to display the execution plan for a prepared query:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003ePREPARE query(int, int) AS SELECT sum(bar) FROM test\n    WHERE id \u0026gt; $1 AND id \u0026lt; $2\n    GROUP BY foo;\n\nEXPLAIN ANALYZE EXECUTE query(100, 200);\n\n                                                       QUERY PLAN\n-------------------------------------------------------------------​------------------------------------------------------\n HashAggregate  (cost=10.77..10.87 rows=10 width=12) (actual time=0.043..0.044 rows=10.00 loops=1)\n   Group Key: foo\n   Batches: 1  Memory Usage: 24kB\n   Buffers: shared hit=4\n   -\u0026gt;  Index Scan using test_pkey on test  (cost=0.29..10.27 rows=99 width=8) (actual time=0.009..0.025 rows=99.00 loops=1)\n         Index Cond: ((id \u0026gt; 100) AND (id \u0026lt; 200))\n         Index Searches: 1\n         Buffers: shared hit=4\n Planning Time: 0.244 ms\n Execution Time: 0.073 ms\n(10 rows)\n\u003c/pre\u003e\u003cp\u003eOf course, the specific numbers shown here depend on the actual contents of the tables involved. Also note that the numbers, and even the selected query strategy, might vary between \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e releases due to planner improvements. In addition, the \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e command uses random sampling to estimate data statistics; therefore, it is possible for cost estimates to change after a fresh run of \u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e, even if the actual distribution of data in the table has not changed.\u003c/p\u003e\u003cp\u003eNotice that the previous example showed a \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003ecustom\u003c/span\u003e”\u003c/span\u003e plan for the specific parameter values given in \u003ccode class=\"command\"\u003eEXECUTE\u003c/code\u003e. We might also wish to see the generic plan for a parameterized query, which can be done with \u003ccode class=\"literal\"\u003eGENERIC_PLAN\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eEXPLAIN (GENERIC_PLAN)\n  SELECT sum(bar) FROM test\n    WHERE id \u0026gt; $1 AND id \u0026lt; $2\n    GROUP BY foo;\n\n                                  QUERY PLAN\n-------------------------------------------------------------------​------------\n HashAggregate  (cost=26.79..26.89 rows=10 width=12)\n   Group Key: foo\n   -\u0026gt;  Index Scan using test_pkey on test  (cost=0.29..24.29 rows=500 width=8)\n         Index Cond: ((id \u0026gt; $1) AND (id \u0026lt; $2))\n(4 rows)\n\u003c/pre\u003e\u003cp\u003eIn this case the parser correctly inferred that \u003ccode class=\"literal\"\u003e$1\u003c/code\u003e and \u003ccode class=\"literal\"\u003e$2\u003c/code\u003e should have the same data type as \u003ccode class=\"literal\"\u003eid\u003c/code\u003e, so the lack of parameter type information from \u003ccode class=\"command\"\u003ePREPARE\u003c/code\u003e was not a problem. In other cases it might be necessary to explicitly specify types for the parameter symbols, which can be done by casting them, for example:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eEXPLAIN (GENERIC_PLAN)\n  SELECT sum(bar) FROM test\n    WHERE id \u0026gt; $1::integer AND id \u0026lt; $2::integer\n    GROUP BY foo;\n\u003c/pre\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003eThere is no \u003ccode class=\"command\"\u003eEXPLAIN\u003c/code\u003e statement defined in the SQL standard.\u003c/p\u003e\u003cp\u003eThe following syntax was used before \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e version 9.0 and is still supported:\u003c/p\u003e\u003cpre class=\"synopsis\"\u003eEXPLAIN [ ANALYZE ] [ VERBOSE ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e\u003cp\u003eNote that in this syntax, the options must be specified in exactly the order shown.\u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/analyze/?v=18\" title=\"ANALYZE\"\u003e\u003cspan class=\"refentrytitle\"\u003eANALYZE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"EXPLAIN [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e [, ...] ) ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e\n\n\u003cspan class=\"phrase\"\u003ewhere \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e can be one of:\u003c/span\u003e\n\n    ANALYZE [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    VERBOSE [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    COSTS [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    SETTINGS [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    GENERIC_PLAN [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    BUFFERS [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    SERIALIZE [ { NONE | TEXT | BINARY } ]\n    WAL [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    TIMING [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    SUMMARY [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    MEMORY [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    FORMAT { TEXT | XML | JSON | YAML }","synopsis_text":"EXPLAIN [ ( option [, ...] ) ] statement\nwhere option can be one of:\n\nANALYZE [ boolean ]\nVERBOSE [ boolean ]\nCOSTS [ boolean ]\nSETTINGS [ boolean ]\nGENERIC_PLAN [ boolean ]\nBUFFERS [ boolean ]\nSERIALIZE [ { NONE | TEXT | BINARY } ]\nWAL [ boolean ]\nTIMING [ boolean ]\nSUMMARY [ boolean ]\nMEMORY [ boolean ]\nFORMAT { TEXT | XML | JSON | YAML }"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"explain","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"EXPLAIN","Summary":"显示一个语句的执行计划","BodyHTML":"\u003cpre\u003eEXPLAIN [ ( option [, ...] ) ] statement\n其中 option 可以是以下之一：\n\nANALYZE [ boolean ]\nVERBOSE [ boolean ]\nCOSTS [ boolean ]\nSETTINGS [ boolean ]\nGENERIC_PLAN [ boolean ]\nBUFFERS [ boolean ]\nSERIALIZE [ { NONE | TEXT | BINARY } ]\nWAL [ boolean ]\nTIMING [ boolean ]\nSUMMARY [ boolean ]\nMEMORY [ boolean ]\nFORMAT { TEXT | XML | JSON | YAML }\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e这个命令显示\u003cspan\u003ePostgreSQL\u003c/span\u003e规划器为给定语句生成的执行计划。执行计划会显示将如何扫描该语句引用的表 — 例如普通顺序扫描、索引扫描等 —，如果引用了多个表，还会显示将使用哪些连接算法来汇集每个输入表中的所需行。\u003c/p\u003e\u003cp\u003e显示结果中最关键的部分是语句执行代价的估计值，它是规划器对运行该语句需要多长时间的猜测（以任意代价单位衡量，但按惯例表示磁盘页面抓取次数）。实际上会显示两个数字：返回第一行之前的启动代价，以及返回全部行的总代价。对大多数查询来说，总代价才是关键；但在某些场景中，例如\u003ccode\u003eEXISTS\u003c/code\u003e中的子查询，规划器会选择启动代价最小而不是总代价最小的计划（因为无论如何执行器都会在得到一行后停止）。此外，如果你用\u003ccode\u003eLIMIT\u003c/code\u003e子句限制返回的行数，规划器会在端点代价之间作适当插值，以估计哪个计划实际上最便宜。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eANALYZE\u003c/code\u003e选项会让该语句被实际执行，而不仅仅是生成计划。随后会把实际运行统计信息加入显示结果，包括每个计划节点中耗费的总时间（以毫秒计）以及它实际返回的总行数。这有助于判断规划器的估计是否接近实际情况。\u003c/p\u003e\u003cdiv\u003e\u003ch3\u003e重要\u003c/h3\u003e\u003cp\u003e请记住，使用\u003ccode\u003eANALYZE\u003c/code\u003e选项时，该语句会被实际执行。尽管\u003ccode\u003eEXPLAIN\u003c/code\u003e会丢弃\u003ccode\u003eSELECT\u003c/code\u003e本来会返回的任何输出，语句的其他副作用仍会照常发生。如果你希望对\u003ccode\u003eINSERT\u003c/code\u003e、\u003ccode\u003eUPDATE\u003c/code\u003e、\u003ccode\u003eDELETE\u003c/code\u003e、\u003ccode\u003eMERGE\u003c/code\u003e、\u003ccode\u003eCREATE TABLE AS\u003c/code\u003e或\u003ccode\u003eEXECUTE\u003c/code\u003e语句使用\u003ccode\u003eEXPLAIN ANALYZE\u003c/code\u003e，而又不让该命令影响你的数据，请采用这种方法：\u003c/p\u003e\u003cpre\u003eBEGIN;\nEXPLAIN ANALYZE ...;\nROLLBACK;\n\u003c/pre\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eANALYZE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e执行该命令并显示实际运行时间及其他统计信息。此参数默认为\u003ccode\u003eFALSE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eVERBOSE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e显示有关计划的附加信息。具体包括：计划树中每个节点的输出列列表、模式限定的表名和函数名、总是用其范围表别名标注表达式中的变量，以及总是打印显示统计信息的每个触发器名称。如果已经计算出查询标识符，也会将其显示出来，详见\u003ca href=\"/docs/18/runtime-config-statistics.html#GUC-COMPUTE-QUERY-ID\" rel=\"nofollow\"\u003ecompute_query_id\u003c/a\u003e。此参数默认为\u003ccode\u003eFALSE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eCOSTS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e包含每个计划节点的估计启动代价和总代价，以及估计行数和每行的估计宽度。此参数默认为\u003ccode\u003eTRUE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eSETTINGS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e包含配置参数信息。具体来说，会列出影响查询规划且其值不同于内置默认值的选项。此参数默认为\u003ccode\u003eFALSE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eGENERIC_PLAN\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e允许语句包含形如\u003ccode\u003e$1\u003c/code\u003e的参数占位符，并生成一个不依赖这些参数值的通用计划。关于通用计划以及哪些语句类型支持参数的细节，详见\u003ca href=\"/docs/18/sql-prepare.html\" title=\"PREPARE\" rel=\"nofollow\"\u003e\u003ccode\u003ePREPARE\u003c/code\u003e\u003c/a\u003e。此参数不能与\u003ccode\u003eANALYZE\u003c/code\u003e一起使用。此参数默认为\u003ccode\u003eFALSE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eBUFFERS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e包含缓冲区使用信息。具体来说，会包括共享块命中、读取、写脏和写出的数量，本地块命中、读取、写脏和写出的数量，临时块读取和写出的数量，以及在启用\u003ca href=\"/docs/18/runtime-config-statistics.html#GUC-TRACK-IO-TIMING\" rel=\"nofollow\"\u003etrack_io_timing\u003c/a\u003e时读取和写出数据文件块、本地块和临时文件块所花费的时间（以毫秒计）。\u003cspan\u003e\u003cem\u003ehit\u003c/em\u003e\u003c/span\u003e表示该块在需要时已经在缓存中找到，因此避免了一次读取。共享块包含普通表和索引的数据；本地块包含临时表和索引的数据；临时块则包含排序、hash、Materialize 计划节点等场景使用的短期工作数据。\u003cspan\u003e\u003cem\u003edirtied\u003c/em\u003e\u003c/span\u003e块数表示此查询修改的、先前未修改过的块数；\u003cspan\u003e\u003cem\u003ewritten\u003c/em\u003e\u003c/span\u003e块数表示在查询处理期间该后端从缓存中逐出的、先前已被写脏的块数。某个上层节点显示的块数包含其所有子节点使用的块数。在文本格式中，只打印非零值。使用\u003ccode\u003eANALYZE\u003c/code\u003e时会自动包含缓冲区信息。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eSERIALIZE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e包含对查询输出数据进行\u003cem\u003e序列化\u003c/em\u003e的代价信息，也就是把它转换成要发送给客户端的文本或二进制格式。如果数据类型的输出函数开销很大，或者必须从行外存储中取回经过\u003cacronym\u003eTOAST\u003c/acronym\u003e处理的值，这部分时间可能会占常规查询执行时间的很大一部分。\u003ccode\u003eEXPLAIN\u003c/code\u003e的默认行为\u003ccode\u003eSERIALIZE NONE\u003c/code\u003e不会执行这些转换。如果指定\u003ccode\u003eSERIALIZE TEXT\u003c/code\u003e或\u003ccode\u003eSERIALIZE BINARY\u003c/code\u003e，则会执行相应的转换，并测量所耗时间（除非指定了\u003ccode\u003eTIMING OFF\u003c/code\u003e）。如果还指定了\u003ccode\u003eBUFFERS\u003c/code\u003e选项，则转换过程中涉及的缓冲区访问也会被计入。不过，无论如何\u003ccode\u003eEXPLAIN\u003c/code\u003e都不会真的把结果数据发送给客户端，因此无法用这种方式研究网络传输代价。只有在同时启用\u003ccode\u003eANALYZE\u003c/code\u003e时才能启用序列化。如果写出\u003ccode\u003eSERIALIZE\u003c/code\u003e而不带参数，则假定为\u003ccode\u003eTEXT\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eWAL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e包含 WAL 记录生成的信息。具体来说，会包括记录数、整页镜像（fpi）数量、以字节计的 WAL 生成量，以及 WAL 缓冲区变满的次数。在文本格式中，只打印非零值。此参数只能在同时启用\u003ccode\u003eANALYZE\u003c/code\u003e时使用。此参数默认为\u003ccode\u003eFALSE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eTIMING\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在输出中包含实际启动时间以及每个节点中耗费的时间。反复读取系统时钟的开销在某些系统上可能会显著拖慢查询，因此当只需要实际行数而不需要精确时间时，把此参数设置为\u003ccode\u003eFALSE\u003c/code\u003e可能会有用。即使通过这个选项关闭了节点级计时，整个语句的运行时间也总会被测量。此参数只能在同时启用\u003ccode\u003eANALYZE\u003c/code\u003e时使用。此参数默认为\u003ccode\u003eTRUE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eSUMMARY\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在查询计划之后包含摘要信息（例如汇总后的时间信息）。使用\u003ccode\u003eANALYZE\u003c/code\u003e时默认包含摘要信息；否则默认不包含，但可以使用此选项启用。在\u003ccode\u003eEXPLAIN EXECUTE\u003c/code\u003e中，规划时间包括从缓存中取出计划所需的时间，以及在必要时重新规划所需的时间。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eMEMORY\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e包含查询规划阶段的内存消耗信息。具体来说，会包括规划器内存结构实际使用的精确存储量，以及把分配开销计算在内后的总内存量。此参数默认为\u003ccode\u003eFALSE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eFORMAT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定输出格式，可以是 TEXT、XML、JSON 或 YAML。非文本输出包含与文本输出相同的信息，但更容易被程序解析。此参数默认为\u003ccode\u003eTEXT\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定所选选项是否开启。可以写\u003ccode\u003eTRUE\u003c/code\u003e、\u003ccode\u003eON\u003c/code\u003e或\u003ccode\u003e1\u003c/code\u003e来启用选项，写\u003ccode\u003eFALSE\u003c/code\u003e、\u003ccode\u003eOFF\u003c/code\u003e或\u003ccode\u003e0\u003c/code\u003e来禁用它。也可以省略\u003cem\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e值，此时假定为\u003ccode\u003eTRUE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e任何你希望查看其执行计划的\u003ccode\u003eSELECT\u003c/code\u003e、\u003ccode\u003eINSERT\u003c/code\u003e、\u003ccode\u003eUPDATE\u003c/code\u003e、\u003ccode\u003eDELETE\u003c/code\u003e、\u003ccode\u003eMERGE\u003c/code\u003e、\u003ccode\u003eVALUES\u003c/code\u003e、\u003ccode\u003eEXECUTE\u003c/code\u003e、\u003ccode\u003eDECLARE\u003c/code\u003e、\u003ccode\u003eCREATE TABLE AS\u003c/code\u003e或\u003ccode\u003eCREATE MATERIALIZED VIEW AS\u003c/code\u003e语句。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e输出\u003c/h2\u003e\u003cp\u003e该命令的结果是为\u003cem\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e选择的计划的文本描述，并可选择附带执行统计信息。\u003ca href=\"/docs/18/using-explain.html\" rel=\"nofollow\"\u003e第 14.1 节\u003c/a\u003e描述了所提供的信息。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e为了让\u003cspan\u003ePostgreSQL\u003c/span\u003e查询规划器在优化查询时能够做出相当有根据的决策，查询中用到的所有表的\u003ca href=\"/docs/18/catalog-pg-statistic.html\" rel=\"nofollow\"\u003e\u003ccode\u003epg_statistic\u003c/code\u003e\u003c/a\u003e数据都应保持最新。通常\u003ca href=\"/docs/18/routine-vacuuming.html#AUTOVACUUM\" rel=\"nofollow\"\u003e自动清理守护进程\u003c/a\u003e会自动处理这一点。但如果某个表最近内容发生了大量变化，你可能需要手工执行一次\u003ca href=\"/docs/18/sql-analyze.html\" title=\"ANALYZE\" rel=\"nofollow\"\u003e\u003ccode\u003eANALYZE\u003c/code\u003e\u003c/a\u003e，而不是等待自动清理跟上这些变化。\u003c/p\u003e\u003cp\u003e为了度量执行计划中每个节点的运行时代价，当前的\u003ccode\u003eEXPLAIN ANALYZE\u003c/code\u003e实现会给查询执行增加性能分析开销。因此，对一个查询运行\u003ccode\u003eEXPLAIN ANALYZE\u003c/code\u003e有时会比正常执行该查询慢得多。开销大小取决于查询的性质以及所用平台。最坏情况出现在那些自身每次执行耗时极少的计划节点上，以及获取当前时间的操作系统调用相对较慢的机器上。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e要显示一个只有单个\u003ccode\u003einteger\u003c/code\u003e列且包含 10000 行的表上的简单查询计划：\u003c/p\u003e\u003cpre\u003eEXPLAIN SELECT * FROM foo;\n\n                       QUERY PLAN\n---------------------------------------------------------\n Seq Scan on foo  (cost=0.00..155.00 rows=10000 width=4)\n(1 row)\n\u003c/pre\u003e\u003cp\u003e下面是同一查询，但使用 JSON 输出格式：\u003c/p\u003e\u003cpre\u003eEXPLAIN (FORMAT JSON) SELECT * FROM foo;\n           QUERY PLAN\n--------------------------------\n [                             +\n   {                           +\n     \u0026#34;Plan\u0026#34;: {                 +\n       \u0026#34;Node Type\u0026#34;: \u0026#34;Seq Scan\u0026#34;,+\n       \u0026#34;Relation Name\u0026#34;: \u0026#34;foo\u0026#34;, +\n       \u0026#34;Alias\u0026#34;: \u0026#34;foo\u0026#34;,         +\n       \u0026#34;Startup Cost\u0026#34;: 0.00,   +\n       \u0026#34;Total Cost\u0026#34;: 155.00,   +\n       \u0026#34;Plan Rows\u0026#34;: 10000,     +\n       \u0026#34;Plan Width\u0026#34;: 4         +\n     }                         +\n   }                           +\n ]\n(1 row)\n\u003c/pre\u003e\u003cp\u003e如果存在索引，并且我们使用了带有可索引\u003ccode\u003eWHERE\u003c/code\u003e条件的查询，\u003ccode\u003eEXPLAIN\u003c/code\u003e可能会显示不同的计划：\u003c/p\u003e\u003cpre\u003eEXPLAIN SELECT * FROM foo WHERE i = 4;\n\n                         QUERY PLAN\n--------------------------------------------------------------\n Index Scan using fi on foo  (cost=0.00..5.98 rows=1 width=4)\n   Index Cond: (i = 4)\n(2 rows)\n\u003c/pre\u003e\u003cp\u003e下面是同一查询，但采用 YAML 格式：\u003c/p\u003e\u003cpre\u003eEXPLAIN (FORMAT YAML) SELECT * FROM foo WHERE i=\u0026#39;4\u0026#39;;\n          QUERY PLAN\n-------------------------------\n - Plan:                      +\n     Node Type: \u0026#34;Index Scan\u0026#34;  +\n     Scan Direction: \u0026#34;Forward\u0026#34;+\n     Index Name: \u0026#34;fi\u0026#34;         +\n     Relation Name: \u0026#34;foo\u0026#34;     +\n     Alias: \u0026#34;foo\u0026#34;             +\n     Startup Cost: 0.00       +\n     Total Cost: 5.98         +\n     Plan Rows: 1             +\n     Plan Width: 4            +\n     Index Cond: \u0026#34;(i = 4)\u0026#34;\n(1 row)\n\u003c/pre\u003e\u003cp\u003eXML 格式留给读者自行练习。\u003c/p\u003e\u003cp\u003e下面是同一计划，但隐藏了代价估计：\u003c/p\u003e\u003cpre\u003eEXPLAIN (COSTS FALSE) SELECT * FROM foo WHERE i = 4;\n\n        QUERY PLAN\n----------------------------\n Index Scan using fi on foo\n   Index Cond: (i = 4)\n(2 rows)\n\u003c/pre\u003e\u003cp\u003e下面是使用聚合函数的查询计划示例：\u003c/p\u003e\u003cpre\u003eEXPLAIN SELECT sum(i) FROM foo WHERE i \u0026lt; 10;\n\n                             QUERY PLAN\n-------------------------------------------------------------------​--\n Aggregate  (cost=23.93..23.93 rows=1 width=4)\n   -\u0026gt;  Index Scan using fi on foo  (cost=0.00..23.92 rows=6 width=4)\n         Index Cond: (i \u0026lt; 10)\n(3 rows)\n\u003c/pre\u003e\u003cp\u003e下面是使用\u003ccode\u003eEXPLAIN EXECUTE\u003c/code\u003e显示预备查询执行计划的示例：\u003c/p\u003e\u003cpre\u003ePREPARE query(int, int) AS SELECT sum(bar) FROM test\n    WHERE id \u0026gt; $1 AND id \u0026lt; $2\n    GROUP BY foo;\n\nEXPLAIN ANALYZE EXECUTE query(100, 200);\n\n                                                       QUERY PLAN\n-------------------------------------------------------------------​------------------------------------------------------\n HashAggregate  (cost=10.77..10.87 rows=10 width=12) (actual time=0.043..0.044 rows=10.00 loops=1)\n   Group Key: foo\n   Batches: 1  Memory Usage: 24kB\n   Buffers: shared hit=4\n   -\u0026gt;  Index Scan using test_pkey on test  (cost=0.29..10.27 rows=99 width=8) (actual time=0.009..0.025 rows=99.00 loops=1)\n         Index Cond: ((id \u0026gt; 100) AND (id \u0026lt; 200))\n         Index Searches: 1\n         Buffers: shared hit=4\n Planning Time: 0.244 ms\n Execution Time: 0.073 ms\n(10 rows)\n\u003c/pre\u003e\u003cp\u003e当然，这里显示的具体数字取决于所涉及表的实际内容。还要注意，由于规划器的改进，这些数字甚至所选查询策略在\u003cspan\u003ePostgreSQL\u003c/span\u003e的不同版本之间都可能发生变化。此外，\u003ccode\u003eANALYZE\u003c/code\u003e命令使用随机采样来估计数据统计信息；因此，即使表中数据的实际分布没有变化，在重新运行一次\u003ccode\u003eANALYZE\u003c/code\u003e之后，代价估计也可能发生变化。\u003c/p\u003e\u003cp\u003e请注意，前一个示例展示了一个\u003cspan\u003e“\u003cspan\u003e自定义\u003c/span\u003e”\u003c/span\u003e计划，它针对的是\u003ccode\u003eEXECUTE\u003c/code\u003e中给定的特定参数值。我们也可能想查看参数化查询的通用计划，这可以用\u003ccode\u003eGENERIC_PLAN\u003c/code\u003e来完成：\u003c/p\u003e\u003cpre\u003eEXPLAIN (GENERIC_PLAN)\n  SELECT sum(bar) FROM test\n    WHERE id \u0026gt; $1 AND id \u0026lt; $2\n    GROUP BY foo;\n\n                                  QUERY PLAN\n-------------------------------------------------------------------​------------\n HashAggregate  (cost=26.79..26.89 rows=10 width=12)\n   Group Key: foo\n   -\u0026gt;  Index Scan using test_pkey on test  (cost=0.29..24.29 rows=500 width=8)\n         Index Cond: ((id \u0026gt; $1) AND (id \u0026lt; $2))\n(4 rows)\n\u003c/pre\u003e\u003cp\u003e在这个例子中，解析器正确地推断出\u003ccode\u003e$1\u003c/code\u003e和\u003ccode\u003e$2\u003c/code\u003e应与\u003ccode\u003eid\u003c/code\u003e具有相同的数据类型，因此缺少来自\u003ccode\u003ePREPARE\u003c/code\u003e的参数类型信息并不是问题。在其他情况下，可能需要显式指定参数符号的类型，这可以通过对它们进行类型转换来做到，例如：\u003c/p\u003e\u003cpre\u003eEXPLAIN (GENERIC_PLAN)\n  SELECT sum(bar) FROM test\n    WHERE id \u0026gt; $1::integer AND id \u0026lt; $2::integer\n    GROUP BY foo;\n\u003c/pre\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003eSQL 标准中没有定义\u003ccode\u003eEXPLAIN\u003c/code\u003e语句。\u003c/p\u003e\u003cp\u003e以下语法在\u003cspan\u003ePostgreSQL\u003c/span\u003e 9.0 之前使用，并且仍然受支持：\u003c/p\u003e\u003cpre\u003eEXPLAIN [ ANALYZE ] [ VERBOSE ] \u003cem\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e\u003cp\u003e注意，在这种语法中，选项必须严格按显示的顺序指定。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/analyze/?v=18\" title=\"ANALYZE\" rel=\"nofollow\"\u003e\u003cspan\u003eANALYZE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"39a6165ba486e52f0634b4f2f36bafa0b6f66b0678de6b327e9f1d97e4aef115","Payload":{"purpose_zh":"显示一个语句的执行计划","sections":[{"html":"\u003cp\u003e这个命令显示\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e规划器为给定语句生成的执行计划。执行计划会显示将如何扫描该语句引用的表 — 例如普通顺序扫描、索引扫描等 —，如果引用了多个表，还会显示将使用哪些连接算法来汇集每个输入表中的所需行。\u003c/p\u003e\u003cp\u003e显示结果中最关键的部分是语句执行代价的估计值，它是规划器对运行该语句需要多长时间的猜测（以任意代价单位衡量，但按惯例表示磁盘页面抓取次数）。实际上会显示两个数字：返回第一行之前的启动代价，以及返回全部行的总代价。对大多数查询来说，总代价才是关键；但在某些场景中，例如\u003ccode class=\"literal\"\u003eEXISTS\u003c/code\u003e中的子查询，规划器会选择启动代价最小而不是总代价最小的计划（因为无论如何执行器都会在得到一行后停止）。此外，如果你用\u003ccode class=\"literal\"\u003eLIMIT\u003c/code\u003e子句限制返回的行数，规划器会在端点代价之间作适当插值，以估计哪个计划实际上最便宜。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eANALYZE\u003c/code\u003e选项会让该语句被实际执行，而不仅仅是生成计划。随后会把实际运行统计信息加入显示结果，包括每个计划节点中耗费的总时间（以毫秒计）以及它实际返回的总行数。这有助于判断规划器的估计是否接近实际情况。\u003c/p\u003e\u003cdiv class=\"important\"\u003e\u003ch3\u003e重要\u003c/h3\u003e\u003cp\u003e请记住，使用\u003ccode class=\"literal\"\u003eANALYZE\u003c/code\u003e选项时，该语句会被实际执行。尽管\u003ccode class=\"command\"\u003eEXPLAIN\u003c/code\u003e会丢弃\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e本来会返回的任何输出，语句的其他副作用仍会照常发生。如果你希望对\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e、\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e、\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e、\u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e、\u003ccode class=\"command\"\u003eCREATE TABLE AS\u003c/code\u003e或\u003ccode class=\"command\"\u003eEXECUTE\u003c/code\u003e语句使用\u003ccode class=\"command\"\u003eEXPLAIN ANALYZE\u003c/code\u003e，而又不让该命令影响你的数据，请采用这种方法：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eBEGIN;\nEXPLAIN ANALYZE ...;\nROLLBACK;\n\u003c/pre\u003e\u003c/div\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eANALYZE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e执行该命令并显示实际运行时间及其他统计信息。此参数默认为\u003ccode class=\"literal\"\u003eFALSE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eVERBOSE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e显示有关计划的附加信息。具体包括：计划树中每个节点的输出列列表、模式限定的表名和函数名、总是用其范围表别名标注表达式中的变量，以及总是打印显示统计信息的每个触发器名称。如果已经计算出查询标识符，也会将其显示出来，详见\u003ca href=\"/docs/18/runtime-config-statistics.html#GUC-COMPUTE-QUERY-ID\"\u003ecompute_query_id\u003c/a\u003e。此参数默认为\u003ccode class=\"literal\"\u003eFALSE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eCOSTS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e包含每个计划节点的估计启动代价和总代价，以及估计行数和每行的估计宽度。此参数默认为\u003ccode class=\"literal\"\u003eTRUE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSETTINGS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e包含配置参数信息。具体来说，会列出影响查询规划且其值不同于内置默认值的选项。此参数默认为\u003ccode class=\"literal\"\u003eFALSE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eGENERIC_PLAN\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e允许语句包含形如\u003ccode class=\"literal\"\u003e$1\u003c/code\u003e的参数占位符，并生成一个不依赖这些参数值的通用计划。关于通用计划以及哪些语句类型支持参数的细节，详见\u003ca href=\"/docs/18/sql-prepare.html\" title=\"PREPARE\"\u003e\u003ccode class=\"command\"\u003ePREPARE\u003c/code\u003e\u003c/a\u003e。此参数不能与\u003ccode class=\"literal\"\u003eANALYZE\u003c/code\u003e一起使用。此参数默认为\u003ccode class=\"literal\"\u003eFALSE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eBUFFERS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e包含缓冲区使用信息。具体来说，会包括共享块命中、读取、写脏和写出的数量，本地块命中、读取、写脏和写出的数量，临时块读取和写出的数量，以及在启用\u003ca href=\"/docs/18/runtime-config-statistics.html#GUC-TRACK-IO-TIMING\"\u003etrack_io_timing\u003c/a\u003e时读取和写出数据文件块、本地块和临时文件块所花费的时间（以毫秒计）。\u003cspan class=\"emphasis\"\u003e\u003cem\u003ehit\u003c/em\u003e\u003c/span\u003e表示该块在需要时已经在缓存中找到，因此避免了一次读取。共享块包含普通表和索引的数据；本地块包含临时表和索引的数据；临时块则包含排序、hash、Materialize 计划节点等场景使用的短期工作数据。\u003cspan class=\"emphasis\"\u003e\u003cem\u003edirtied\u003c/em\u003e\u003c/span\u003e块数表示此查询修改的、先前未修改过的块数；\u003cspan class=\"emphasis\"\u003e\u003cem\u003ewritten\u003c/em\u003e\u003c/span\u003e块数表示在查询处理期间该后端从缓存中逐出的、先前已被写脏的块数。某个上层节点显示的块数包含其所有子节点使用的块数。在文本格式中，只打印非零值。使用\u003ccode class=\"literal\"\u003eANALYZE\u003c/code\u003e时会自动包含缓冲区信息。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSERIALIZE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e包含对查询输出数据进行\u003cem class=\"firstterm\"\u003e序列化\u003c/em\u003e的代价信息，也就是把它转换成要发送给客户端的文本或二进制格式。如果数据类型的输出函数开销很大，或者必须从行外存储中取回经过\u003cacronym\u003eTOAST\u003c/acronym\u003e处理的值，这部分时间可能会占常规查询执行时间的很大一部分。\u003ccode class=\"command\"\u003eEXPLAIN\u003c/code\u003e的默认行为\u003ccode class=\"literal\"\u003eSERIALIZE NONE\u003c/code\u003e不会执行这些转换。如果指定\u003ccode class=\"literal\"\u003eSERIALIZE TEXT\u003c/code\u003e或\u003ccode class=\"literal\"\u003eSERIALIZE BINARY\u003c/code\u003e，则会执行相应的转换，并测量所耗时间（除非指定了\u003ccode class=\"literal\"\u003eTIMING OFF\u003c/code\u003e）。如果还指定了\u003ccode class=\"literal\"\u003eBUFFERS\u003c/code\u003e选项，则转换过程中涉及的缓冲区访问也会被计入。不过，无论如何\u003ccode class=\"command\"\u003eEXPLAIN\u003c/code\u003e都不会真的把结果数据发送给客户端，因此无法用这种方式研究网络传输代价。只有在同时启用\u003ccode class=\"literal\"\u003eANALYZE\u003c/code\u003e时才能启用序列化。如果写出\u003ccode class=\"literal\"\u003eSERIALIZE\u003c/code\u003e而不带参数，则假定为\u003ccode class=\"literal\"\u003eTEXT\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWAL\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e包含 WAL 记录生成的信息。具体来说，会包括记录数、整页镜像（fpi）数量、以字节计的 WAL 生成量，以及 WAL 缓冲区变满的次数。在文本格式中，只打印非零值。此参数只能在同时启用\u003ccode class=\"literal\"\u003eANALYZE\u003c/code\u003e时使用。此参数默认为\u003ccode class=\"literal\"\u003eFALSE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTIMING\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在输出中包含实际启动时间以及每个节点中耗费的时间。反复读取系统时钟的开销在某些系统上可能会显著拖慢查询，因此当只需要实际行数而不需要精确时间时，把此参数设置为\u003ccode class=\"literal\"\u003eFALSE\u003c/code\u003e可能会有用。即使通过这个选项关闭了节点级计时，整个语句的运行时间也总会被测量。此参数只能在同时启用\u003ccode class=\"literal\"\u003eANALYZE\u003c/code\u003e时使用。此参数默认为\u003ccode class=\"literal\"\u003eTRUE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSUMMARY\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在查询计划之后包含摘要信息（例如汇总后的时间信息）。使用\u003ccode class=\"literal\"\u003eANALYZE\u003c/code\u003e时默认包含摘要信息；否则默认不包含，但可以使用此选项启用。在\u003ccode class=\"command\"\u003eEXPLAIN EXECUTE\u003c/code\u003e中，规划时间包括从缓存中取出计划所需的时间，以及在必要时重新规划所需的时间。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eMEMORY\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e包含查询规划阶段的内存消耗信息。具体来说，会包括规划器内存结构实际使用的精确存储量，以及把分配开销计算在内后的总内存量。此参数默认为\u003ccode class=\"literal\"\u003eFALSE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eFORMAT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定输出格式，可以是 TEXT、XML、JSON 或 YAML。非文本输出包含与文本输出相同的信息，但更容易被程序解析。此参数默认为\u003ccode class=\"literal\"\u003eTEXT\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定所选选项是否开启。可以写\u003ccode class=\"literal\"\u003eTRUE\u003c/code\u003e、\u003ccode class=\"literal\"\u003eON\u003c/code\u003e或\u003ccode class=\"literal\"\u003e1\u003c/code\u003e来启用选项，写\u003ccode class=\"literal\"\u003eFALSE\u003c/code\u003e、\u003ccode class=\"literal\"\u003eOFF\u003c/code\u003e或\u003ccode class=\"literal\"\u003e0\u003c/code\u003e来禁用它。也可以省略\u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e值，此时假定为\u003ccode class=\"literal\"\u003eTRUE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e任何你希望查看其执行计划的\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e、\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e、\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e、\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e、\u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e、\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e、\u003ccode class=\"command\"\u003eEXECUTE\u003c/code\u003e、\u003ccode class=\"command\"\u003eDECLARE\u003c/code\u003e、\u003ccode class=\"command\"\u003eCREATE TABLE AS\u003c/code\u003e或\u003ccode class=\"command\"\u003eCREATE MATERIALIZED VIEW AS\u003c/code\u003e语句。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e该命令的结果是为\u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e选择的计划的文本描述，并可选择附带执行统计信息。\u003ca href=\"/docs/18/using-explain.html\" title=\"14.1. 使用EXPLAIN\"\u003e第 14.1 节\u003c/a\u003e描述了所提供的信息。\u003c/p\u003e","key":"outputs","title":"输出"},{"html":"\u003cp\u003e为了让\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e查询规划器在优化查询时能够做出相当有根据的决策，查询中用到的所有表的\u003ca href=\"/docs/18/catalog-pg-statistic.html\" title=\"52.51. pg_statistic\"\u003e\u003ccode class=\"structname\"\u003epg_statistic\u003c/code\u003e\u003c/a\u003e数据都应保持最新。通常\u003ca href=\"/docs/18/routine-vacuuming.html#AUTOVACUUM\" title=\"24.1.6. 自动清理守护进程\"\u003e自动清理守护进程\u003c/a\u003e会自动处理这一点。但如果某个表最近内容发生了大量变化，你可能需要手工执行一次\u003ca href=\"/docs/18/sql-analyze.html\" title=\"ANALYZE\"\u003e\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e\u003c/a\u003e，而不是等待自动清理跟上这些变化。\u003c/p\u003e\u003cp\u003e为了度量执行计划中每个节点的运行时代价，当前的\u003ccode class=\"command\"\u003eEXPLAIN ANALYZE\u003c/code\u003e实现会给查询执行增加性能分析开销。因此，对一个查询运行\u003ccode class=\"command\"\u003eEXPLAIN ANALYZE\u003c/code\u003e有时会比正常执行该查询慢得多。开销大小取决于查询的性质以及所用平台。最坏情况出现在那些自身每次执行耗时极少的计划节点上，以及获取当前时间的操作系统调用相对较慢的机器上。\u003c/p\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e要显示一个只有单个\u003ccode class=\"type\"\u003einteger\u003c/code\u003e列且包含 10000 行的表上的简单查询计划：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eEXPLAIN SELECT * FROM foo;\n\n                       QUERY PLAN\n---------------------------------------------------------\n Seq Scan on foo  (cost=0.00..155.00 rows=10000 width=4)\n(1 row)\n\u003c/pre\u003e\u003cp\u003e下面是同一查询，但使用 JSON 输出格式：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eEXPLAIN (FORMAT JSON) SELECT * FROM foo;\n           QUERY PLAN\n--------------------------------\n [                             +\n   {                           +\n     \"Plan\": {                 +\n       \"Node Type\": \"Seq Scan\",+\n       \"Relation Name\": \"foo\", +\n       \"Alias\": \"foo\",         +\n       \"Startup Cost\": 0.00,   +\n       \"Total Cost\": 155.00,   +\n       \"Plan Rows\": 10000,     +\n       \"Plan Width\": 4         +\n     }                         +\n   }                           +\n ]\n(1 row)\n\u003c/pre\u003e\u003cp\u003e如果存在索引，并且我们使用了带有可索引\u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e条件的查询，\u003ccode class=\"command\"\u003eEXPLAIN\u003c/code\u003e可能会显示不同的计划：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eEXPLAIN SELECT * FROM foo WHERE i = 4;\n\n                         QUERY PLAN\n--------------------------------------------------------------\n Index Scan using fi on foo  (cost=0.00..5.98 rows=1 width=4)\n   Index Cond: (i = 4)\n(2 rows)\n\u003c/pre\u003e\u003cp\u003e下面是同一查询，但采用 YAML 格式：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eEXPLAIN (FORMAT YAML) SELECT * FROM foo WHERE i='4';\n          QUERY PLAN\n-------------------------------\n - Plan:                      +\n     Node Type: \"Index Scan\"  +\n     Scan Direction: \"Forward\"+\n     Index Name: \"fi\"         +\n     Relation Name: \"foo\"     +\n     Alias: \"foo\"             +\n     Startup Cost: 0.00       +\n     Total Cost: 5.98         +\n     Plan Rows: 1             +\n     Plan Width: 4            +\n     Index Cond: \"(i = 4)\"\n(1 row)\n\u003c/pre\u003e\u003cp\u003eXML 格式留给读者自行练习。\u003c/p\u003e\u003cp\u003e下面是同一计划，但隐藏了代价估计：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eEXPLAIN (COSTS FALSE) SELECT * FROM foo WHERE i = 4;\n\n        QUERY PLAN\n----------------------------\n Index Scan using fi on foo\n   Index Cond: (i = 4)\n(2 rows)\n\u003c/pre\u003e\u003cp\u003e下面是使用聚合函数的查询计划示例：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eEXPLAIN SELECT sum(i) FROM foo WHERE i \u0026lt; 10;\n\n                             QUERY PLAN\n-------------------------------------------------------------------​--\n Aggregate  (cost=23.93..23.93 rows=1 width=4)\n   -\u0026gt;  Index Scan using fi on foo  (cost=0.00..23.92 rows=6 width=4)\n         Index Cond: (i \u0026lt; 10)\n(3 rows)\n\u003c/pre\u003e\u003cp\u003e下面是使用\u003ccode class=\"command\"\u003eEXPLAIN EXECUTE\u003c/code\u003e显示预备查询执行计划的示例：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003ePREPARE query(int, int) AS SELECT sum(bar) FROM test\n    WHERE id \u0026gt; $1 AND id \u0026lt; $2\n    GROUP BY foo;\n\nEXPLAIN ANALYZE EXECUTE query(100, 200);\n\n                                                       QUERY PLAN\n-------------------------------------------------------------------​------------------------------------------------------\n HashAggregate  (cost=10.77..10.87 rows=10 width=12) (actual time=0.043..0.044 rows=10.00 loops=1)\n   Group Key: foo\n   Batches: 1  Memory Usage: 24kB\n   Buffers: shared hit=4\n   -\u0026gt;  Index Scan using test_pkey on test  (cost=0.29..10.27 rows=99 width=8) (actual time=0.009..0.025 rows=99.00 loops=1)\n         Index Cond: ((id \u0026gt; 100) AND (id \u0026lt; 200))\n         Index Searches: 1\n         Buffers: shared hit=4\n Planning Time: 0.244 ms\n Execution Time: 0.073 ms\n(10 rows)\n\u003c/pre\u003e\u003cp\u003e当然，这里显示的具体数字取决于所涉及表的实际内容。还要注意，由于规划器的改进，这些数字甚至所选查询策略在\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e的不同版本之间都可能发生变化。此外，\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e命令使用随机采样来估计数据统计信息；因此，即使表中数据的实际分布没有变化，在重新运行一次\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e之后，代价估计也可能发生变化。\u003c/p\u003e\u003cp\u003e请注意，前一个示例展示了一个\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e自定义\u003c/span\u003e”\u003c/span\u003e计划，它针对的是\u003ccode class=\"command\"\u003eEXECUTE\u003c/code\u003e中给定的特定参数值。我们也可能想查看参数化查询的通用计划，这可以用\u003ccode class=\"literal\"\u003eGENERIC_PLAN\u003c/code\u003e来完成：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eEXPLAIN (GENERIC_PLAN)\n  SELECT sum(bar) FROM test\n    WHERE id \u0026gt; $1 AND id \u0026lt; $2\n    GROUP BY foo;\n\n                                  QUERY PLAN\n-------------------------------------------------------------------​------------\n HashAggregate  (cost=26.79..26.89 rows=10 width=12)\n   Group Key: foo\n   -\u0026gt;  Index Scan using test_pkey on test  (cost=0.29..24.29 rows=500 width=8)\n         Index Cond: ((id \u0026gt; $1) AND (id \u0026lt; $2))\n(4 rows)\n\u003c/pre\u003e\u003cp\u003e在这个例子中，解析器正确地推断出\u003ccode class=\"literal\"\u003e$1\u003c/code\u003e和\u003ccode class=\"literal\"\u003e$2\u003c/code\u003e应与\u003ccode class=\"literal\"\u003eid\u003c/code\u003e具有相同的数据类型，因此缺少来自\u003ccode class=\"command\"\u003ePREPARE\u003c/code\u003e的参数类型信息并不是问题。在其他情况下，可能需要显式指定参数符号的类型，这可以通过对它们进行类型转换来做到，例如：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eEXPLAIN (GENERIC_PLAN)\n  SELECT sum(bar) FROM test\n    WHERE id \u0026gt; $1::integer AND id \u0026lt; $2::integer\n    GROUP BY foo;\n\u003c/pre\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003eSQL 标准中没有定义\u003ccode class=\"command\"\u003eEXPLAIN\u003c/code\u003e语句。\u003c/p\u003e\u003cp\u003e以下语法在\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 9.0 之前使用，并且仍然受支持：\u003c/p\u003e\u003cpre class=\"synopsis\"\u003eEXPLAIN [ ANALYZE ] [ VERBOSE ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e\u003cp\u003e注意，在这种语法中，选项必须严格按显示的顺序指定。\u003c/p\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/analyze/?v=18\" title=\"ANALYZE\"\u003e\u003cspan class=\"refentrytitle\"\u003eANALYZE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"EXPLAIN [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e [, ...] ) ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e\n\n\u003cspan class=\"phrase\"\u003e其中 \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e 可以是以下之一：\u003c/span\u003e\n\n    ANALYZE [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    VERBOSE [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    COSTS [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    SETTINGS [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    GENERIC_PLAN [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    BUFFERS [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    SERIALIZE [ { NONE | TEXT | BINARY } ]\n    WAL [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    TIMING [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    SUMMARY [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    MEMORY [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]\n    FORMAT { TEXT | XML | JSON | YAML }","synopsis_text":"EXPLAIN [ ( option [, ...] ) ] statement\n其中 option 可以是以下之一：\n\nANALYZE [ boolean ]\nVERBOSE [ boolean ]\nCOSTS [ boolean ]\nSETTINGS [ boolean ]\nGENERIC_PLAN [ boolean ]\nBUFFERS [ boolean ]\nSERIALIZE [ { NONE | TEXT | BINARY } ]\nWAL [ boolean ]\nTIMING [ boolean ]\nSUMMARY [ boolean ]\nMEMORY [ boolean ]\nFORMAT { TEXT | XML | JSON | YAML }"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}