ldrg's questions

A pg_statstabela revela que o PostgreSQL armazena um histograma ao coletar estatísticas. Isso significa que o PostgreSQL pode chegar a uma estimativa de quantas linhas um filtro retornará contando o número de buckets de histograma que contêm o valor do filtro. Você pode aumentar o número de buckets de histograma com o ALTER TABLE ... SET STATISTICSque significa que o PostgreSQL pode ser ainda mais preciso (estimar menos linhas) sobre a seletividade de um filtro.

O planejador de consulta estima a quantidade total de trabalho que precisa ser feito para executar a consulta e compara isso com um limite para ver se a compilação da consulta JIT deve ser feita. Essa estimativa inclui as estimativas de contagem de linhas que o PostgreSQL mantém para fins de seletividade de filtro? Aumentar o tamanho da amostra de estatísticas permitiria que o planejador de consultas tomasse melhores decisões sobre quando o benefício de desempenho da compilação JIT supera o tempo de inicialização da compilação JIT?

Há um post de blog popular e aparentemente oficial chamado On Rocks and Sand sobre como otimizar o tamanho das tabelas PostgreSQL para eliminar o preenchimento interno reordenando o comprimento da coluna. Eles explicam como os tipos de comprimento variável incorrem em algum preenchimento extra se não estiverem no final da tabela:

Isso significa que podemos encadear colunas de comprimento variável o dia todo sem introduzir preenchimento, exceto no limite direito. Conseqüentemente, podemos deduzir que as colunas de comprimento variável não apresentam inchaço, desde que estejam no final de uma listagem de colunas.

E no final do post, resumindo:

Classifique as colunas pelo comprimento do tipo, conforme definido em pg_type.

Existe uma biblioteca que se integra ao ActiveRecord do Ruby para reordenar automaticamente as colunas para reduzir o preenchimento chamado pg_column_byte_packer . Você pode ver o README em que o repositório cita a postagem do blog acima e, em geral, faz a mesma coisa que a postagem do blog descreve.

No entanto, o pg_column_byte_packernão retorna resultados consistentes com a postagem do blog que cita. A postagem do blog é extraída do interno do PostgreSQL, pg_type.typelenque coloca colunas de comprimento variável sempre no final por meio de um alinhamento de -1. pg_column_byte_packerdá-lhes um alinhamento de 3.

pg_column_byte_packertem um comentário explicativo :

    # These types generally have an alignment of 4 (as designated by pg_type
    # having a typalign value of 'i', but they're special in that small values
    # have an optimized storage layout. Beyond the optimized storage layout, though,
    # these small values also are not required to respect the alignment the type
    # would otherwise have. Specifically, values with a size of at most 127 bytes
    # aren't aligned. That 127 byte cap, however, includes an overhead byte to store
    # the length, and so in reality the max is 126 bytes. Interestingly TOASTable
    # values are also treated that way, but we don't have a good way of knowing which
    # values those will be.
    #
    # See: `fill_val()` in src/backend/access/common/heaptuple.c (in the conditional
    # `else if (att->attlen == -1)` branch.
    #
    # When no limit modifier has been applied we don't have a good heuristic for
    # determining which columns are likely to be long or short, so we currently
    # just slot them all after the columns we believe will always be long.

O comentário parece não estar errado, pois as colunas de texto têm um pg_type.typalignde 4, mas também têm um pg_type.typlende -1, que a postagem do blog argumenta que obtém a embalagem mais ideal quando no final da tabela.

Portanto, no caso de uma tabela que tenha uma integercoluna, uma textcoluna e uma smallintcoluna, pg_column_byte_packercolocará as colunas de texto entre as duas. Eles até têm um teste de unidade para afirmar que isso sempre acontece.

Minha pergunta aqui é: qual ordem de colunas realmente é compactada para espaço mínimo? O comentário de pg_column_byte_packerparece não estar errado, pois as colunas de texto têm um pg_type.typalignde 4, mas também têm um pg_type.typlende -1.

A ferramenta de linha de comando do MySQL tem um sinalizador --i-am-a-dummy que define os limites de execução:

Com --safe-updates, você pode modificar linhas apenas especificando os valores de chave que as identificam, uma cláusula LIMIT ou ambas. Isso ajuda a evitar acidentes. O modo de atualizações seguras também restringe as instruções SELECT que produzem (ou estima-se que produzam) conjuntos de resultados muito grandes.

Existe uma maneira de ativar recursos semelhantes no psql?

Link do DB Fiddle. Eu tenho uma tabela de muitos para muitos assim:

CREATE TABLE house_to_cats (
  id SERIAL PRIMARY KEY,
  house_id INTEGER,
  cat_id INTEGER
);

-- house with cats 1 and 2: too small
INSERT INTO house_to_cats (house_id, cat_id) VALUES (1, 1), (1, 2);
-- house with cats 1 2 3 4: too big
INSERT INTO house_to_cats (house_id, cat_id) VALUES (2, 1), (2, 2), (2, 3), (2, 4);
-- house with cats 1 2 3: just right
INSERT INTO house_to_cats (house_id, cat_id) VALUES (3, 1), (3, 2), (3, 3);

Eu preciso de uma consulta que pegue uma lista arbitrária de gatos e retorne uma casa correspondente, se existir. Eu vim com isso:

SELECT
    house_id
FROM (
    SELECT
          house_id
        , ARRAY_AGG(cat_id) as cat_id_agg
    FROM house_to_cats
    JOIN (
        SELECT DISTINCT
              house_id
        FROM house_to_cats
        JOIN (SELECT * FROM UNNEST(ARRAY[1, 2, 3]) cat_id) inn USING (cat_id)
    ) filter USING (house_id)
    GROUP BY house_id
) agg
WHERE cat_id_agg <@ ARRAY[1, 2, 3]
  AND cat_id_agg @> ARRAY[1, 2, 3];

Existe uma maneira melhor de fazer isso?

A ideia por trás da minha consulta: em filter, obtenha o house_idque tem pelo menos um dos nossos gatos neles. Em agg, crie cat_id_aggarrays para todos eles house_ids. E na consulta mais externa, filtre os grupos que não correspondem ao nosso conjunto.

Esta é uma continuação de: A cobertura de índices no PostgreSQL ajuda as colunas JOIN?

Considere o inverso do esquema na outra pergunta em que você filtra na tabela unida:

CREATE TABLE thing_types(
   id              INTEGER PRIMARY KEY
 , first_lvl_type  TEXT
 , second_lvl_type TEXT
);

CREATE TABLE things(
   id         INTEGER PRIMARY KEY
 , thing_type INTEGER REFERENCES thing_types(id)
 , t1c1       INTEGER
);

E uma consulta assim:

SELECT things.t1c1
FROM   things
JOIN   thing_types ON things.thing_type = thing_types.id
WHERE  thing_types.first_lvl_type = 'Book'
AND    thing_types.second_lvl_type = 'Biography';

É loucura ter um índice como:

CREATE INDEX ON thing_types(first_lvl_type, second_lvl_type, id);

que cobre a chave primária para uso nessa junção? O índice será usado como um índice de cobertura para ajudar JOINna consulta acima? Devo alterar minha estratégia de indexação para cobrir a chave primária com mais frequência quando sei que a tabela será JOINeditada assim?

Eu tenho um monte de tabelas que se parecem vagamente com isso:

CREATE TABLE table1(id INTEGER PRIMARY KEY, t1c1 INTEGER, t1c2 INTEGER);
CREATE TABLE table2(id INTEGER PRIMARY KEY, t1 INTEGER REFERENCES table1(id), t2c1 INTEGER);

E eu faço muitas junções onde estou tentando filtrar na tabela unida para obter coisas da primeira tabela, assim:

SELECT t1c1
FROM table1
JOIN table2 ON table2.t1 = table1.id
WHERE t2c1 = 42;

Quando vou escrever índices para uma tabela, olho para as colunas que são usadas na cláusula WHERE e construo índices para satisfazê-las. Então, para esta consulta, eu acabaria escrevendo um índice como este:

CREATE INDEX ON table2 (t2c1);

E esse índice é pelo menos elegível para uso nessa consulta.

Minha pergunta é que, se eu escrever um índice assim:

CREATE INDEX ON table2 (t2c1, t1);

O índice será usado como um índice de cobertura para ajudar o JOIN na consulta acima? Devo alterar minha estratégia de escrita de índice para cobrir colunas de chave estrangeira?

eu tenho uma mesa

CREATE TABLE (game, team, player, position);

Se eu criar um índice único em (game, player, position)recebo uma mesa onde no mesmo jogo, o mesmo jogador não pode jogar na mesma posição para os dois times. Isso é útil, mas ainda é possível que alguém insira uma linha onde o mesmo jogador jogue em duas posições diferentes, uma em cada equipe.

É possível escrever algum tipo de índice ou restrição única que impossibilite o mesmo jogador de jogar pelas duas equipes em um jogo?

No sqlite3, os agrupamentos são definidos por coluna. Se CREATE TEMPORARY TABLE foo AS SELECT ...a tabela temporária resultante foonão herdar nenhum agrupamento personalizado das colunas no arquivo SELECT. O que acontece se você CREATE VIEW foo AS SELECT ...? A exibição "tabela" mantém os agrupamentos de colunas ou os perde?

Eu vi na documentação a diferença entre count(*)e count(pk). Eu estava usando count(pk)(onde pkestá um SERIAL PRIMARY KEY) sem saber da existência de count(*).

Minha pergunta é sobre as otimizações internas do Postgres. É inteligente o suficiente para perceber que SERIAL PRIMARY KEYa existirá em todas as linhas e nunca será falso e apenas contará as linhas ou fará verificações redundantes de predicados para cada linha? Eu concordo que isso provavelmente é uma otimização sem sentido, mas estou apenas curioso.

Dei uma olhada na saída de EXPLAINe EXPLAIN VERBOSEpara count(*), count(id)e count(id > 50)para ver se EXPLAINmencionei a verificação dos predicados em sua saída. Não.