Início / dba / Perguntas / 211735
Accepted
peterh
Asked: 2018-07-11 01:55:59 +0800 CST

Existe uma maneira de ordenar uma tabela do PostgreSQL por sua chave primária automaticamente?

  • 772

Eu quero despejar todo o conteúdo de uma tabela, ordenado por sua chave primária . Intuitivamente, gostaria de fazer uma

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

Isso seria executado por uma ferramenta externa, conhecendo a lista das tabelas, mas não conhecendo suas chaves primárias.

Se não houver uma opção melhor, podemos assumir que todas as chaves primárias são de coluna única.

Estou usando o PostgreSQL-9.5, mas uma atualização é possível.

postgresql primary-key

2 respostas

  1. Por padrão, o simple SELECT * FROM %table_name%não ordenará os resultados por chave primária.

    Mas você pode obter a lista de colunas da chave primária da tabela:

    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;

    E, em seguida, crie uma consulta dinâmica com essas colunas na ORDER BYcláusula.

    Adicione INNER JOIN pg_namespace sch ON sch.oid = tbl.relnamespacee WHEREcondicione sch.nspnamese você tiver várias tabelas com o mesmo nome em esquemas diferentes.

    Você pode obter PKs para todas as tabelas em 1 consulta:

    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

    Acredito que, se você colocar um índice clusterizado em sua chave primária, o PG o usará por padrão. Hipótese não testada :)

    CLUSTER table_name USING primary_key_index;

    De acordo com https://www.postgresql.org/docs/current/static/sql-cluster.html

    Nos casos em que você está acessando linhas únicas aleatoriamente em uma tabela, a ordem real dos dados na tabela não é importante. No entanto, se você tende a acessar alguns dados mais do que outros, e há um índice que os agrupa, você se beneficiará do uso de CLUSTER. Se você estiver solicitando um intervalo de valores indexados de uma tabela ou um único valor indexado que tenha várias linhas correspondentes, o CLUSTER ajudará porque, uma vez que o índice identifica a página da tabela para a primeira linha correspondente, todas as outras linhas correspondentes provavelmente serão já na mesma página da tabela, e assim você economiza acessos ao disco e agiliza a consulta.

    O CLUSTER pode reordenar a tabela usando uma varredura de índice no índice especificado ou (se o índice for uma b-tree) uma varredura sequencial seguida de classificação. Ele tentará escolher o método que será mais rápido, com base nos parâmetros de custo do planejador e nas informações estatísticas disponíveis.

    • 1

relate perguntas