{"Entry":{"collection":"sql","key":"cluster","name":"CLUSTER","aliases":["cluster"],"metadata":{"aliases":["cluster"],"changed_in":["7.1","7.4","8.3","8.4","9.0","14","17"],"changes":[{"from":"6.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["usage"],"removed":[]},"status":"changed","synopsis":null,"to":"6.5"},{"from":"6.5","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"7.0"},{"from":"7.0","purpose_changed":false,"renamed":{"from_file":"sql-cluster.htm","to_file":"sql-cluster.html"},"sections":{"added":[],"changed":["description","usage"],"removed":[]},"status":"changed","synopsis":{"added":["CLUSTER indexname ON tablename"],"removed":["CLUSTER indexname ON table"]},"to":"7.1"},{"from":"7.1","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"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","see_also"],"changed":["description","compatibility"],"removed":["usage"]},"status":"changed","synopsis":{"added":["CLUSTER tablename","CLUSTER"],"removed":[]},"to":"7.4"},{"from":"7.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"8.0"},{"from":"8.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.2"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes","examples","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["CLUSTER tablename [ USING indexname ]"],"removed":["CLUSTER indexname ON tablename"]},"to":"8.3"},{"from":"8.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":{"added":["CLUSTER [VERBOSE] tablename [ USING indexname ]","CLUSTER [VERBOSE]"],"removed":[]},"to":"8.4"},{"from":"8.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["CLUSTER [VERBOSE] table_name [ USING index_name ]"],"removed":["CLUSTER [VERBOSE] tablename [ USING indexname ]"]},"to":"9.0"},{"from":"9.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.1"},{"from":"9.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"9.5"},{"from":"13","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes","see_also"],"removed":[]},"status":"changed","synopsis":{"added":["CLUSTER ( option [, ...] ) table_name [ USING index_name ]","CLUSTER [VERBOSE]","VERBOSE [ boolean ]"],"removed":[]},"to":"14"},{"from":"14","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"15"},{"from":"15","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes","see_also"],"removed":[]},"status":"changed","synopsis":null,"to":"16"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes","compatibility","see_also"],"removed":[]},"status":"changed","synopsis":{"added":["CLUSTER [ ( option [, ...] ) ] [ table_name [ USING index_name ] ]"],"removed":["CLUSTER [VERBOSE] table_name [ USING index_name ]","CLUSTER ( option [, ...] ) table_name [ USING index_name ]","CLUSTER [VERBOSE]"]},"to":"17"},{"from":"17","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"18"},{"from":"18","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes","see_also"],"removed":[]},"status":"changed","synopsis":null,"to":"19"}],"content_hash":"809e01f749df0d91db39812f9aab9cc0f38729c5c02357b1a8ec23500bd54c91","editorial":{},"first_version":"6.4","group":"maintenance","imported_at":"2026-09-30T17:43:39.623938+08:00","last_version":"20","name":"CLUSTER","object":"","position":15002,"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":"cluster a table according to an index","purpose_zh":"","related":["repack"],"slug":"cluster","source_rev":"a709ab85","synopsis":"CLUSTER [ ( option [, ...] ) ] [ table_name [ USING index_name ] ]\n\nwhere option can be one of:\n\nVERBOSE [ boolean ]","verb":"CLUSTER"}},"Definition":{"Collection":"sql","Key":"cluster","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"cluster","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-CLUSTER","file":"sql-cluster.html","lang":"en","name":"CLUSTER","purpose":"cluster a table according to an index","purpose_zh":"","related":[],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e instructs \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e to cluster the table specified by \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e based on the index specified by \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_name\u003c/code\u003e\u003c/em\u003e. The index must already have been defined on \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e\u003cp\u003eWhen a table is clustered, it is physically reordered based on the index information. Clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered. That is, no attempt is made to store new or updated rows according to their index order. (If one wishes, one can periodically recluster by issuing the command again. Also, setting the table's \u003ccode class=\"literal\"\u003efillfactor\u003c/code\u003e storage parameter to less than 100% can aid in preserving cluster ordering during updates, since updated rows are kept on the same page if enough space is available there.)\u003c/p\u003e\u003cp\u003eWhen a table is clustered, \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e remembers which index it was clustered by. The form \u003ccode class=\"command\"\u003eCLUSTER \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e reclusters the table using the same index as before. You can also use the \u003ccode class=\"literal\"\u003eCLUSTER\u003c/code\u003e or \u003ccode class=\"literal\"\u003eSET WITHOUT CLUSTER\u003c/code\u003e forms of \u003ca href=\"/docs/18/sql-altertable.html\" title=\"ALTER TABLE\"\u003e\u003ccode class=\"command\"\u003eALTER TABLE\u003c/code\u003e\u003c/a\u003e to set the index to be used for future cluster operations, or to clear any previous setting.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e without a \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e reclusters all the previously-clustered tables in the current database that the calling user has privileges for. This form of \u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e cannot be executed inside a transaction block.\u003c/p\u003e\u003cp\u003eWhen a table is being clustered, an \u003ccode class=\"literal\"\u003eACCESS EXCLUSIVE\u003c/code\u003e lock is acquired on it. This prevents any other database operations (both reads and writes) from operating on the table until the \u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e is finished.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name (possibly schema-qualified) of a table.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of an index.\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\u003ePrints a progress report as each table is clustered at \u003ccode class=\"literal\"\u003eINFO\u003c/code\u003e level.\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\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eTo cluster a table, one must have the \u003ccode class=\"literal\"\u003eMAINTAIN\u003c/code\u003e privilege on the table.\u003c/p\u003e\u003cp\u003eIn cases where you are accessing single rows randomly within a table, the actual order of the data in the table is unimportant. However, if you tend to access some data more than others, and there is an index that groups them together, you will benefit from using \u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e. If you are requesting a range of indexed values from a table, or a single indexed value that has multiple rows that match, \u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e will help because once the index identifies the table page for the first row that matches, all other rows that match are probably already on the same table page, and so you save disk accesses and speed up the query.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e can re-sort the table using either an index scan on the specified index, or (if the index is a b-tree) a sequential scan followed by sorting. It will attempt to choose the method that will be faster, based on planner cost parameters and available statistical information.\u003c/p\u003e\u003cp\u003eWhile \u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e is running, the \u003ca href=\"/docs/18/runtime-config-client.html#GUC-SEARCH-PATH\"\u003esearch_path\u003c/a\u003e is temporarily changed to \u003ccode class=\"literal\"\u003epg_catalog, pg_temp\u003c/code\u003e.\u003c/p\u003e\u003cp\u003eWhen an index scan is used, a temporary copy of the table is created that contains the table data in the index order. Temporary copies of each index on the table are created as well. Therefore, you need free space on disk at least equal to the sum of the table size and the index sizes.\u003c/p\u003e\u003cp\u003eWhen a sequential scan and sort is used, a temporary sort file is also created, so that the peak temporary space requirement is as much as double the table size, plus the index sizes. This method is often faster than the index scan method, but if the disk space requirement is intolerable, you can disable this choice by temporarily setting \u003ca href=\"/docs/18/runtime-config-query.html#GUC-ENABLE-SORT\"\u003eenable_sort\u003c/a\u003e to \u003ccode class=\"literal\"\u003eoff\u003c/code\u003e.\u003c/p\u003e\u003cp\u003eIt is advisable to set \u003ca href=\"/docs/18/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM\"\u003emaintenance_work_mem\u003c/a\u003e to a reasonably large value (but not more than the amount of RAM you can dedicate to the \u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e operation) before clustering.\u003c/p\u003e\u003cp\u003eBecause the planner records statistics about the ordering of tables, it is advisable to run \u003ca href=\"/docs/18/sql-analyze.html\" title=\"ANALYZE\"\u003e\u003ccode class=\"command\"\u003eANALYZE\u003c/code\u003e\u003c/a\u003e on the newly clustered table. Otherwise, the planner might make poor choices of query plans.\u003c/p\u003e\u003cp\u003eBecause \u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e remembers which indexes are clustered, one can cluster the tables one wants clustered manually the first time, then set up a periodic maintenance script that executes \u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e without any parameters, so that the desired tables are periodically reclustered.\u003c/p\u003e\u003cp\u003eEach backend running \u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e will report its progress in the \u003ccode class=\"structname\"\u003epg_stat_progress_cluster\u003c/code\u003e view. See \u003ca href=\"/docs/18/progress-reporting.html#CLUSTER-PROGRESS-REPORTING\" title=\"27.4.2. CLUSTER Progress Reporting\"\u003eSection 27.4.2\u003c/a\u003e for details.\u003c/p\u003e\u003cp\u003eClustering a partitioned table clusters each of its partitions using the partition of the specified partitioned index. When clustering a partitioned table, the index may not be omitted. \u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e on a partitioned table cannot be executed inside a transaction block.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eCluster the table \u003ccode class=\"literal\"\u003eemployees\u003c/code\u003e on the basis of its index \u003ccode class=\"literal\"\u003eemployees_ind\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCLUSTER employees USING employees_ind;\n\u003c/pre\u003e\u003cp\u003eCluster the \u003ccode class=\"literal\"\u003eemployees\u003c/code\u003e table using the same index that was used before:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCLUSTER employees;\n\u003c/pre\u003e\u003cp\u003eCluster all tables in the database that have previously been clustered:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCLUSTER;\n\u003c/pre\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003eThere is no \u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e statement in the SQL standard.\u003c/p\u003e\u003cp\u003eThe following syntax was used before \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 17 and is still supported:\u003c/p\u003e\u003cpre class=\"synopsis\"\u003eCLUSTER [ VERBOSE ] [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e [ USING \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_name\u003c/code\u003e\u003c/em\u003e ] ]\n\u003c/pre\u003e\u003cp\u003eThe following syntax was used before \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 8.3 and is still supported:\u003c/p\u003e\u003cpre class=\"synopsis\"\u003eCLUSTER \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_name\u003c/code\u003e\u003c/em\u003e ON \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/docs/18/app-clusterdb.html\" title=\"clusterdb\"\u003e\u003cspan class=\"refentrytitle\"\u003e\u003cspan class=\"application\"\u003eclusterdb\u003c/span\u003e\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/progress-reporting.html#CLUSTER-PROGRESS-REPORTING\" title=\"27.4.2. CLUSTER Progress Reporting\"\u003eSection 27.4.2\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"CLUSTER [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e [, ...] ) ] [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e [ USING \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_name\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    VERBOSE [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]","synopsis_text":"CLUSTER [ ( option [, ...] ) ] [ table_name [ USING index_name ] ]\n\nwhere option can be one of:\n\nVERBOSE [ boolean ]"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"cluster","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"CLUSTER","Summary":"按照一个索引对表进行聚簇","BodyHTML":"\u003cpre\u003eCLUSTER [ ( option [, ...] ) ] [ table_name [ USING index_name ] ]\n\n其中 option 可以是以下之一：\n\nVERBOSE [ boolean ]\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCLUSTER\u003c/code\u003e 指示 \u003cspan\u003ePostgreSQL\u003c/span\u003e 按照 \u003cem\u003e\u003ccode\u003eindex_name\u003c/code\u003e\u003c/em\u003e 指定的索引，对 \u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e 指定的表进行聚簇。该索引必须已经定义在 \u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e 上。\u003c/p\u003e\u003cp\u003e当一个表被聚簇时，它会根据索引信息在物理上重新排序。聚簇是一次性操作：之后如果表再被更新，这些更改不会再次被聚簇。也就是说，系统不会试图按照索引顺序存储新行或更新后的行。（如果需要，可以定期再次执行该命令来重新聚簇。此外，将表的 \u003ccode\u003efillfactor\u003c/code\u003e 存储参数设置为小于 100%，有助于在更新期间保持聚簇顺序，因为如果页面上有足够空间，更新后的行会保留在同一页面中。）\u003c/p\u003e\u003cp\u003e当一个表被聚簇时，\u003cspan\u003ePostgreSQL\u003c/span\u003e 会记住该表是按哪个索引聚簇的。形式 \u003ccode\u003eCLUSTER \u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e 会使用与之前相同的索引对表重新聚簇。你也可以使用 \u003ca href=\"/docs/18/sql-altertable.html\" title=\"ALTER TABLE\" rel=\"nofollow\"\u003e\u003ccode\u003eALTER TABLE\u003c/code\u003e\u003c/a\u003e 的 \u003ccode\u003eCLUSTER\u003c/code\u003e 或 \u003ccode\u003eSET WITHOUT CLUSTER\u003c/code\u003e 形式，设置未来聚簇操作要使用的索引，或者清除任何先前的设置。\u003c/p\u003e\u003cp\u003e不带 \u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e 的 \u003ccode\u003eCLUSTER\u003c/code\u003e 会对当前数据库中所有此前已聚簇且调用用户具有相应权限的表重新执行聚簇。这种形式的 \u003ccode\u003eCLUSTER\u003c/code\u003e 不能在事务块内执行。\u003c/p\u003e\u003cp\u003e当对一个表执行聚簇时，会在该表上获取 \u003ccode\u003eACCESS EXCLUSIVE\u003c/code\u003e 锁。这会阻止任何其他数据库操作（包括读和写）在 \u003ccode\u003eCLUSTER\u003c/code\u003e 完成前访问该表。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e表的名称（可能是模式限定的）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eindex_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\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在每个表被聚簇时，以 \u003ccode\u003eINFO\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\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e要对表执行聚簇，必须在该表上具有 \u003ccode\u003eMAINTAIN\u003c/code\u003e 权限。\u003c/p\u003e\u003cp\u003e当你在表中随机访问单行时，数据在表中的实际顺序并不重要。但是，如果访问某些数据比其他数据更频繁，并且存在将这些数据放在一起的索引，使用\u003ccode\u003eCLUSTER\u003c/code\u003e就会有益。如果要从表中获取一段索引值范围，或者获取有多行匹配的单个索引值，\u003ccode\u003eCLUSTER\u003c/code\u003e会有帮助，因为一旦索引确定第一个匹配行所在的表页，其他匹配行很可能已在同一个表页中，从而减少磁盘访问并加快查询。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eCLUSTER\u003c/code\u003e 可以通过在指定索引上进行索引扫描，或者（如果该索引是 B-树）先执行顺序扫描再排序，来对表重新排序。它会根据规划器代价参数和可用的统计信息，尝试选择速度更快的方法。\u003c/p\u003e\u003cp\u003e在 \u003ccode\u003eCLUSTER\u003c/code\u003e 执行期间，\u003ca href=\"/docs/18/runtime-config-client.html#GUC-SEARCH-PATH\" rel=\"nofollow\"\u003esearch_path\u003c/a\u003e会被临时设置为 \u003ccode\u003epg_catalog, pg_temp\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e使用索引扫描时，会创建一个表的临时副本，其中表数据按索引顺序排列。还会为表上的每个索引创建临时副本。因此，所需的磁盘空闲空间至少应等于表大小与索引大小之和。\u003c/p\u003e\u003cp\u003e使用顺序扫描加排序时，还会创建临时排序文件，因此临时空间需求峰值可达表大小的两倍再加上索引大小。这种方法通常比索引扫描更快，但如果无法接受其磁盘空间需求，可以临时将\u003ca href=\"/docs/18/runtime-config-query.html#GUC-ENABLE-SORT\" rel=\"nofollow\"\u003eenable_sort\u003c/a\u003e设置为\u003ccode\u003eoff\u003c/code\u003e来禁用这种选择。\u003c/p\u003e\u003cp\u003e建议在聚簇之前将\u003ca href=\"/docs/18/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM\" rel=\"nofollow\"\u003emaintenance_work_mem\u003c/a\u003e设置为一个合理的大值（但不超过可专门用于\u003ccode\u003eCLUSTER\u003c/code\u003e操作的内存量）。\u003c/p\u003e\u003cp\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\u003eCLUSTER\u003c/code\u003e 会记住哪些索引已被设为聚簇索引，你可以第一次先手工聚簇需要聚簇的表，然后设置一个定期运行的维护脚本，执行不带任何参数的 \u003ccode\u003eCLUSTER\u003c/code\u003e，这样这些表就会被周期性地重新聚簇。\u003c/p\u003e\u003cp\u003e每个运行 \u003ccode\u003eCLUSTER\u003c/code\u003e 的后端都会在 \u003ccode\u003epg_stat_progress_cluster\u003c/code\u003e 视图中报告其进度。有关详细信息，请参见\u003ca href=\"/docs/18/progress-reporting.html#CLUSTER-PROGRESS-REPORTING\" rel=\"nofollow\"\u003e第 27.4.2 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e对分区表进行聚簇时，会使用指定分区索引在各分区上的对应分区索引来聚簇每个分区。对分区表执行聚簇时，索引不可省略。对分区表执行 \u003ccode\u003eCLUSTER\u003c/code\u003e 不能在事务块内进行。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e按照索引 \u003ccode\u003eemployees_ind\u003c/code\u003e 对表 \u003ccode\u003eemployees\u003c/code\u003e 进行聚簇：\u003c/p\u003e\u003cpre\u003eCLUSTER employees USING employees_ind;\n\u003c/pre\u003e\u003cp\u003e使用之前用过的同一个索引对 \u003ccode\u003eemployees\u003c/code\u003e 表进行聚簇：\u003c/p\u003e\u003cpre\u003eCLUSTER employees;\n\u003c/pre\u003e\u003cp\u003e对数据库中此前已聚簇过的所有表执行聚簇：\u003c/p\u003e\u003cpre\u003eCLUSTER;\n\u003c/pre\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003eSQL 标准中没有 \u003ccode\u003eCLUSTER\u003c/code\u003e 语句。\u003c/p\u003e\u003cp\u003e以下语法在 \u003cspan\u003ePostgreSQL\u003c/span\u003e 17 之前使用，当前仍受支持：\u003c/p\u003e\u003cpre\u003eCLUSTER [ VERBOSE ] [ \u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e [ USING \u003cem\u003e\u003ccode\u003eindex_name\u003c/code\u003e\u003c/em\u003e ] ]\n\u003c/pre\u003e\u003cp\u003e以下语法在 \u003cspan\u003ePostgreSQL\u003c/span\u003e 8.3 之前使用，当前仍受支持：\u003c/p\u003e\u003cpre\u003eCLUSTER \u003cem\u003e\u003ccode\u003eindex_name\u003c/code\u003e\u003c/em\u003e ON \u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/docs/18/app-clusterdb.html\" title=\"clusterdb\" rel=\"nofollow\"\u003e\u003cspan\u003e\u003cspan\u003eclusterdb\u003c/span\u003e\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/progress-reporting.html#CLUSTER-PROGRESS-REPORTING\" rel=\"nofollow\"\u003e第 27.4.2 节\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"b31da3136e8fe8f2982f6e354bde443ee199c078904a6573a3eb57962b36c9eb","Payload":{"purpose_zh":"按照一个索引对表进行聚簇","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e 指示 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 按照 \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_name\u003c/code\u003e\u003c/em\u003e 指定的索引，对 \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e 指定的表进行聚簇。该索引必须已经定义在 \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e 上。\u003c/p\u003e\u003cp\u003e当一个表被聚簇时，它会根据索引信息在物理上重新排序。聚簇是一次性操作：之后如果表再被更新，这些更改不会再次被聚簇。也就是说，系统不会试图按照索引顺序存储新行或更新后的行。（如果需要，可以定期再次执行该命令来重新聚簇。此外，将表的 \u003ccode class=\"literal\"\u003efillfactor\u003c/code\u003e 存储参数设置为小于 100%，有助于在更新期间保持聚簇顺序，因为如果页面上有足够空间，更新后的行会保留在同一页面中。）\u003c/p\u003e\u003cp\u003e当一个表被聚簇时，\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 会记住该表是按哪个索引聚簇的。形式 \u003ccode class=\"command\"\u003eCLUSTER \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e 会使用与之前相同的索引对表重新聚簇。你也可以使用 \u003ca href=\"/docs/18/sql-altertable.html\" title=\"ALTER TABLE\"\u003e\u003ccode class=\"command\"\u003eALTER TABLE\u003c/code\u003e\u003c/a\u003e 的 \u003ccode class=\"literal\"\u003eCLUSTER\u003c/code\u003e 或 \u003ccode class=\"literal\"\u003eSET WITHOUT CLUSTER\u003c/code\u003e 形式，设置未来聚簇操作要使用的索引，或者清除任何先前的设置。\u003c/p\u003e\u003cp\u003e不带 \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e 的 \u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e 会对当前数据库中所有此前已聚簇且调用用户具有相应权限的表重新执行聚簇。这种形式的 \u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e 不能在事务块内执行。\u003c/p\u003e\u003cp\u003e当对一个表执行聚簇时，会在该表上获取 \u003ccode class=\"literal\"\u003eACCESS EXCLUSIVE\u003c/code\u003e 锁。这会阻止任何其他数据库操作（包括读和写）在 \u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e 完成前访问该表。\u003c/p\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e表的名称（可能是模式限定的）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\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在每个表被聚簇时，以 \u003ccode class=\"literal\"\u003eINFO\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\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e要对表执行聚簇，必须在该表上具有 \u003ccode class=\"literal\"\u003eMAINTAIN\u003c/code\u003e 权限。\u003c/p\u003e\u003cp\u003e当你在表中随机访问单行时，数据在表中的实际顺序并不重要。但是，如果访问某些数据比其他数据更频繁，并且存在将这些数据放在一起的索引，使用\u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e就会有益。如果要从表中获取一段索引值范围，或者获取有多行匹配的单个索引值，\u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e会有帮助，因为一旦索引确定第一个匹配行所在的表页，其他匹配行很可能已在同一个表页中，从而减少磁盘访问并加快查询。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e 可以通过在指定索引上进行索引扫描，或者（如果该索引是 B-树）先执行顺序扫描再排序，来对表重新排序。它会根据规划器代价参数和可用的统计信息，尝试选择速度更快的方法。\u003c/p\u003e\u003cp\u003e在 \u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e 执行期间，\u003ca href=\"/docs/18/runtime-config-client.html#GUC-SEARCH-PATH\"\u003esearch_path\u003c/a\u003e会被临时设置为 \u003ccode class=\"literal\"\u003epg_catalog, pg_temp\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e使用索引扫描时，会创建一个表的临时副本，其中表数据按索引顺序排列。还会为表上的每个索引创建临时副本。因此，所需的磁盘空闲空间至少应等于表大小与索引大小之和。\u003c/p\u003e\u003cp\u003e使用顺序扫描加排序时，还会创建临时排序文件，因此临时空间需求峰值可达表大小的两倍再加上索引大小。这种方法通常比索引扫描更快，但如果无法接受其磁盘空间需求，可以临时将\u003ca href=\"/docs/18/runtime-config-query.html#GUC-ENABLE-SORT\"\u003eenable_sort\u003c/a\u003e设置为\u003ccode class=\"literal\"\u003eoff\u003c/code\u003e来禁用这种选择。\u003c/p\u003e\u003cp\u003e建议在聚簇之前将\u003ca href=\"/docs/18/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM\"\u003emaintenance_work_mem\u003c/a\u003e设置为一个合理的大值（但不超过可专门用于\u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e操作的内存量）。\u003c/p\u003e\u003cp\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\"\u003eCLUSTER\u003c/code\u003e 会记住哪些索引已被设为聚簇索引，你可以第一次先手工聚簇需要聚簇的表，然后设置一个定期运行的维护脚本，执行不带任何参数的 \u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e，这样这些表就会被周期性地重新聚簇。\u003c/p\u003e\u003cp\u003e每个运行 \u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e 的后端都会在 \u003ccode class=\"structname\"\u003epg_stat_progress_cluster\u003c/code\u003e 视图中报告其进度。有关详细信息，请参见\u003ca href=\"/docs/18/progress-reporting.html#CLUSTER-PROGRESS-REPORTING\" title=\"27.4.2. CLUSTER 进度报告\"\u003e第 27.4.2 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e对分区表进行聚簇时，会使用指定分区索引在各分区上的对应分区索引来聚簇每个分区。对分区表执行聚簇时，索引不可省略。对分区表执行 \u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e 不能在事务块内进行。\u003c/p\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e按照索引 \u003ccode class=\"literal\"\u003eemployees_ind\u003c/code\u003e 对表 \u003ccode class=\"literal\"\u003eemployees\u003c/code\u003e 进行聚簇：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCLUSTER employees USING employees_ind;\n\u003c/pre\u003e\u003cp\u003e使用之前用过的同一个索引对 \u003ccode class=\"literal\"\u003eemployees\u003c/code\u003e 表进行聚簇：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCLUSTER employees;\n\u003c/pre\u003e\u003cp\u003e对数据库中此前已聚簇过的所有表执行聚簇：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCLUSTER;\n\u003c/pre\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003eSQL 标准中没有 \u003ccode class=\"command\"\u003eCLUSTER\u003c/code\u003e 语句。\u003c/p\u003e\u003cp\u003e以下语法在 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 17 之前使用，当前仍受支持：\u003c/p\u003e\u003cpre class=\"synopsis\"\u003eCLUSTER [ VERBOSE ] [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e [ USING \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_name\u003c/code\u003e\u003c/em\u003e ] ]\n\u003c/pre\u003e\u003cp\u003e以下语法在 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 8.3 之前使用，当前仍受支持：\u003c/p\u003e\u003cpre class=\"synopsis\"\u003eCLUSTER \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_name\u003c/code\u003e\u003c/em\u003e ON \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/docs/18/app-clusterdb.html\" title=\"clusterdb\"\u003e\u003cspan class=\"refentrytitle\"\u003e\u003cspan class=\"application\"\u003eclusterdb\u003c/span\u003e\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/progress-reporting.html#CLUSTER-PROGRESS-REPORTING\" title=\"27.4.2. CLUSTER 进度报告\"\u003e第 27.4.2 节\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"CLUSTER [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoption\u003c/code\u003e\u003c/em\u003e [, ...] ) ] [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e [ USING \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_name\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    VERBOSE [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e ]","synopsis_text":"CLUSTER [ ( option [, ...] ) ] [ table_name [ USING index_name ] ]\n\n其中 option 可以是以下之一：\n\nVERBOSE [ boolean ]"}},"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}