Luke Hutchison's questions

Tenho a seguinte estrutura de tabela em meu banco de dados Postgres: tabela chat_channelpossui uma coluna otherUserIdque pode ser unida com tabela a coluna userIdde outra tabela, profile. A tabela profilepossui colunas userIdque podem ser unidas à coluna senderUserIdou recipientUserIdna tabela note.

Para qualquer linha que chat_channelcorresponda a determinados critérios de consulta, desejo extrair a única linha de profileonde chat_channel."otherUserId" = profile."userId"(como colunas extras retornadas da consulta). Também quero extrair colunas da linha correspondente notepara cada uma das duas userIdcorrespondências possíveis: where profile."userId" = note."senderUserId"e where profile."userId" = note."recipientUserId".

Estou tendo problemas para descobrir como criar um alias adequado de colunas para essa consulta duplamente aninhada, visto que a consulta interna ingressa na mesma tabela duas vezes.

Tenho que criar um alias para as colunas, pois estou retornando colunas da mesma tabela duas vezes nas linhas de resultados. Além disso, as tabelas compartilham colunas com o mesmo nome (principalmente id).

Tentativa (1):

SELECT    "chat_channel"."id" "chat_channel.id",
          "chat_channel"."channelId" "chat_channel.channelId",
          "chat_channel"."userId" "chat_channel.userId",
          "chat_channel"."otherUserId" "chat_channel.otherUserId",
          "chat_channel"."sortByDateTime" "chat_channel.sortByDateTime",
          "profile_other_user"."userId" "profile_other_user.userId",
          "profile_other_user"."name" "profile_other_user.name",
          "note_viewer_sent"."id" "note_viewer_sent.id",  -- (a)
          "note_viewer_sent"."senderUserId" "note_viewer_sent.senderUserId",
          "note_viewer_sent"."recipientUserId" "note_viewer_sent.recipientUserId",
          "note_viewer_sent"."noteText" "note_viewer_sent.noteText",
          "note_viewer_received"."id" "note_viewer_received.id",
          "note_viewer_received"."senderUserId" "note_viewer_received.senderUserId",
          "note_viewer_received"."recipientUserId" "note_viewer_received.recipientUserId",
          "note_viewer_received"."noteText" "note_viewer_received.noteText"
FROM      "chat_channel"
LEFT JOIN LATERAL (
    SELECT    "profile_other_user"."id",
              "profile_other_user"."userId",
              "profile_other_user"."name"
    FROM      "profile" "profile_other_user"
    LEFT JOIN LATERAL (
        SELECT "note_viewer_sent"."id",
               "note_viewer_sent"."senderUserId",
               "note_viewer_sent"."recipientUserId",
               "note_viewer_sent"."noteText"
        FROM   "note" "note_viewer_sent"  -- (b)
        WHERE  "note_viewer_sent"."recipientUserId" = "profile_other_user"."userId"
                AND "note_viewer_sent"."senderUserId" = $viewerUserId
        LIMIT 1
    ) AS "note_viewer_sent" ON TRUE  -- (c)
    LEFT JOIN LATERAL (
        SELECT "note_viewer_received"."id",
               "note_viewer_received"."senderUserId",
               "note_viewer_received"."recipientUserId",
               "note_viewer_received"."noteText"
        FROM   "note" "note_viewer_received"
        WHERE  "note_viewer_received"."senderUserId" = "profile_other_user"."userId"
                AND "note_viewer_received"."recipientUserId" = $viewerUserId
        LIMIT 1
    ) AS "note_viewer_received" ON TRUE
    WHERE "chat_channel"."otherUserId" = "profile_other_user"."userId"
    LIMIT 1
) AS "profile_other_user" ON TRUE
WHERE "chat_channel"."userId" = $viewerUserId
ORDER BY  "chat_channel"."sortByDateTime" DESC

Problemas com isso:

• Isso dá ERROR: missing FROM-clause entry for table "note_viewer_sent"na linha marcada -- (a).
• Tentar criar um alias para a tabela ( -- (b)) não resolve o problema.
• Tentar criar um alias para os resultados da consulta mais profunda ( -- (c)) não resolve o problema.

Tentativa (2):

No entanto, se eu fizer a FROMcláusula de nível superior

FROM      "chat_channel", "note" "note_viewer_sent", "note" "note_viewer_received"

então o erro não é mais gerado, mas as WHEREcláusulas mais internas parecem ser ignoradas:

WHERE  "note_viewer_sent"."recipientUserId" = "profile_other_user"."userId"
       AND "note_viewer_sent"."senderUserId" = $viewerUserId

Especificamente, cada linha do conjunto de resultados contém colunas da mesma linha (errada) de notes, para a qual a WHEREcondição nem sequer é válida.

Além disso, parece que o efeito padrão de colocar várias tabelas FROMé obter um produto cruzado das tabelas. (Não posso reproduzir isso agora, mas a certa altura eu estava recebendo uma cópia de cada linha de profilepara cada linha de chat_channele as LIMIT 1consultas internas estavam sendo ignoradas.)

Alguma idéia sobre o que estou fazendo de errado aqui?

Tenho a seguinte tabela no Postgres:

CREATE TABLE avg (
    user_id INT,
    numer DOUBLE PRECISION,
    denom DOUBLE PRECISION,
    frac DOUBLE PRECISION
);

Quero realizar o seguinte e estou tendo dificuldade em colocar isso em uma consulta. Basicamente quero poder atualizar algumas médias atomicamente, atualizando separadamente o numerador e o denominador, e depois calcular e atualizar a média fracna mesma operação, com base nos novos valores:

1. Dados os parâmetros ae b, quero encontrar a linha para um determinado user_id, então quero fazer numer += ae denom += bcalcular frac = numer / denoma partir dos novos valores de numere e denom, em seguida, atualizar os valores de numer, denome fracnesta linha.
2. Porém, se não houver nenhuma linha para o solicitado user_id, ou se houver uma linha, mas os valores de numere denomforem nulos, então quero assumir que o valor dessas somas é zero, ou seja, uma nova linha deve ser inserida em a tabela, ou uma operação de atualização deve ser executada se houver uma linha, mas os valores forem nulos, com o resultado sendo definido numer = a, denom = be frac = a / bpara o user_id.
3. Quero garantir que tudo isso aconteça atomicamente.

Pode haver vários numeradores e denominadores em uma determinada tabela, daí a necessidade de ser capaz de inserir (se a linha não existir) ou atualizar (se a linha existir, mas os valores iniciais de um par numerador/denominador específico forem nulo).

Eu tenho uma tabela com milhões de linhas e quero encontrar todas as linhas que contenham qualquer um de uma lista fornecida de alguns milhares de valores em uma coluna específica. Basicamente, quero executar uma IN(...set...)consulta, que é reescrita internamente em uma = ANY(...array...)construção, com um tamanho de matriz de milhares, em uma coluna indexada com milhões de linhas.

Minhas perguntas são:

1. Existe um limite para o tamanho de um conjunto ou array neste tipo de consulta?
2. Como esse tipo de consulta é dimensionado? Presumo que a matriz não esteja indexada, então, presumivelmente, cada valor da matriz atinge o índice, fornecendo escala O(n log N)para nvalores da matriz e Nlinhas da tabela.
3. Qual seria o impacto no rendimento da consulta ao enviar esses tipos de consultas grandes, em meio a um fluxo de consultas mais simples? Em outras palavras, seria bom dividir isso em algumas dezenas de consultas separadas, com, digamos, 100 valores de array em cada uma, para permitir que o trabalho desta consulta fosse intercalado com outras consultas?

O planejador de consultas do Postgres é capaz de reorganizar os termos de uma WHEREcláusula de acordo com as regras de comutatividade e associatividade?

Por exemplo, WHEREtodas as cláusulas a seguir resultam em um plano de consulta com a mesma complexidade de tempo aproximada?:

• WHERE a AND b AND c
• WHERE (a AND b) AND c
• WHERE a AND (b AND c)
• WHERE (c AND b) AND a

Eu tenho uma tabela Postgres com um grande número de colunas indexadas (cerca de 100 colunas indexadas no total e, sim, preciso de todas elas e, sim, todas precisam ser indexadas separadamente). Qualquer atualização de linha faz com que todos os índices sejam atualizados, o que dá muito trabalho para o mecanismo de banco de dados.

Quero entender as implicações de simultaneidade da discussão na página de documentação do Postgres intitulada Considerações sobre bloqueio de índice e também o fato de que o Postgres é de thread único (multiprocesso) , em termos de como o design atual afeta o desempenho do leitor e do gravador para um grande número de consultas simultâneas, visto que tenho muitos índices de colunas.

Minha interpretação dessas coisas é a seguinte (corrija as que estiverem erradas):

• Os gravadores que atualizam linhas individuais não bloqueiam os leitores, a menos que o leitor esteja executando uma consulta que produza um conjunto de resultados que inclua a linha que está sendo atualizada.
• Os escritores só bloqueiam uns aos outros se estiverem tentando atualizar a mesma linha ao mesmo tempo.
• Atualizações simultâneas de índices baseados em btree de vários gravadores são mescladas de acordo com um conjunto de regras que geralmente faz a coisa certa (portanto, atualizar os mesmos índices ao mesmo tempo não causa o bloqueio dos gravadores, a menos que estejam atualizando a mesma linha).

Minhas perguntas são:

• Como pode haver vários leitores ou escritores simultâneos, se o Postgres for de thread único? Se você tiver vários processos em execução, eles simplesmente dependem da consistência entre processos dos caches de disco (ou precisam liberar manualmente o conteúdo no disco) para coordenar atualizações simultâneas?
• E se algo puder ser bloqueado enquanto um grande número de índices estiver sendo atualizado devido a uma atualização de linha? Se alguma coisa puder ser bloqueada durante uma atualização, é possível ativar o equilíbrio entre consistência e disponibilidade para que, por exemplo, uma atualização de linha não seja atômica (ou seja, para que os índices sejam atualizados um de cada vez, mas a atualização de todos os índices não precisa acontecer atomicamente)? Estou bem com a falta de consistência em nome de uma maior simultaneidade.