主页 / dba / 问题 / 211735
Accepted
peterh
Asked: 2018-07-11 01:55:59 +0800 CST

有没有办法通过主键自动对 PostgreSQL 表进行排序？

  • 772

我想转储按主键排序的表的全部内容。直觉上，我想做一个

SELECT * FROM %table_name% ORDER BY %primary_key_of(table_name)%;

这将由外部工具运行，知道表的列表，但不知道它们的主键。

如果没有更好的选择，我们可以假设所有主键都是单列的。

我使用的是 PostgreSQL-9.5，但可以升级。

postgresql primary-key

2 个回答

  1. 默认情况下，simpleSELECT * FROM %table_name%不会按主键对结果进行排序。

    但是您可以获得表的主键列的列表：

    SELECT ind_column.attname AS columns_of_pk
FROM pg_class tbl
  INNER JOIN pg_index ind ON ind.indrelid = tbl.oid
  INNER JOIN pg_class ind_table ON ind_table.oid = ind.indexrelid
  INNER JOIN pg_attribute ind_column ON ind_column.attrelid = ind_table.oid
WHERE tbl.relname = 'my_table'
  AND ind.indisprimary;

    ORDER BY然后在子句中使用此列构建动态查询。

    如果您在不同的模式中有多个同名的表，请添加INNER JOIN pg_namespace sch ON sch.oid = tbl.relnamespace并WHERE设置条件。sch.nspname

    您可以在 1 个查询中获取所有表的 PK：

    SELECT sch.nspname AS "schema"
  , tbl.relname AS "table"
  , array_agg(ind_column.attname) AS columns_of_pk
FROM pg_class tbl
  INNER JOIN pg_namespace sch ON sch.oid = tbl.relnamespace
  INNER JOIN pg_index ind ON ind.indrelid = tbl.oid
  INNER JOIN pg_class ind_table ON ind_table.oid = ind.indexrelid
  INNER JOIN pg_attribute ind_column ON ind_column.attrelid = ind_table.oid
WHERE sch.nspname <> 'pg_toast'
  AND ind.indisprimary
GROUP BY "schema", "table";
    • 5
  2. Best Answer

    我相信如果你在你的主键上放置一个聚集索引，那么 PG 默认会使用它。未经检验的假设:)

    CLUSTER table_name USING primary_key_index;

    根据https://www.postgresql.org/docs/current/static/sql-cluster.html

    如果您在表中随机访问单行，则表中数据的实际顺序并不重要。但是，如果您倾向于访问某些数据而不是其他数据，并且有一个将它们组合在一起的索引，那么您将从使用 CLUSTER 中受益。如果您从表中请求一系列索引值，或者请求具有多个匹配行的单个索引值，CLUSTER 将有所帮助，因为一旦索引识别出第一行匹配的表页，所有其他匹配的行可能已经在同一个表页面上，因此您可以节省磁盘访问并加快查询速度。

    CLUSTER 可以使用指定索引上的索引扫描或（如果索引是 b 树）顺序扫描然后排序来重新排序表。它将尝试根据计划者成本参数和可用的统计信息选择更快的方法。

    • 1

相关问题