Início / dba / Perguntas / 328933
Accepted
sujeet
Asked: 2023-07-05 00:24:59 +0800 CST

Armazenamento e recuperação de tabela PostgreSQL com índice na coluna de chave não primária

  • 772

Entendo o armazenamento básico de tabela de chave primária no PostgreSQL, onde os dados são armazenados em páginas e uma estrutura de índice de árvore B é usada para recuperação eficiente.

No entanto, gostaria de entender o comportamento quando uma tabela não possui uma chave primária, mas possui um índice em uma coluna de chave não primária, como a namecoluna.

CREATE TABLE products (
  id SERIAL,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL
);

Supondo que a tabela "products" tenha 100 linhas, meu entendimento é que os dados serão armazenados em páginas e o índice da árvore B terá um nó raiz [1, 100].

Além disso, haverá nós intermediários com intervalos como [1, 49]e [50, 100], que terão nós filhos e assim por diante. No entanto, não estou certo sobre o que acontecerá com os nós folha do índice. Eles conterão dados de linha reais?

  1. No caso de um índice na namecoluna sem uma chave primária, os nós folha do índice conterão os dados reais da linha ou ponteiros para as linhas?

  2. Como o processo de recuperação funcionará se os nós folha contiverem os dados reais da linha? Ele executará uma varredura linear dos nós de folha para encontrar o valor de "nome" desejado?

  3. Além disso, ter uma chave primária, se a tabela já estiver indexada em name, pode otimizar a consulta?

Eu apreciaria uma explicação detalhada do mecanismo de armazenamento e recuperação deste cenário, juntamente com quaisquer exemplos de código relevantes ou considerações adicionais.

postgresql

2 respostas

  1. Acho que esta parte da documentação responde a maior parte da sua pergunta. Pelo que entendi, não, não acho que ter uma chave primária no nome otimizará a consulta. A restrição de chave primária não afeta o índice existente na coluna de nome. No entanto, ter uma chave primária pode trazer outros benefícios de desempenho, como integridade de dados aprimorada e operações de junção mais rápidas se você precisar referenciar a tabela de produtos em chaves estrangeiras.

    • 1
  2. Best Answer

    Acho que você foi vítima de um mal-entendido fundamental. Uma tabela PostgreSQL é apenas uma variedade não ordenada de linhas, não é organizada por uma chave primária. Um índice de chave primária não é diferente de outros índices de árvore B. Um índice de árvore B não faz referência às linhas da tabela por sua chave primária, mas por seu ctid(ID de tupla atual), que é uma combinação de número de bloco de 8kB e número de entrada dentro do bloco.

    Um índice de árvore B namearmazenará as strings reais, organizadas em uma lista ordenada em forma de árvore. Os nós folha contêm as ctidlinhas da tabela referenciada.

    • 1

relate perguntas