Início / dba / Perguntas / 53437
Accepted
ffflabs
Asked: 2013-11-18 05:24:27 +0800 CST

Restringir a exclusão para buscar as últimas N linhas

  • 772

Estou gerenciando uma tabela com vários milhões de registros que estão sendo inseridos em tempo real. Uma parte do meu aplicativo precisa exibir as últimas N linhas inseridas, portanto, no início, apenas consultei:

select id, logdate, content from measurements order by logdate DESC limit 500;

Alguns dias depois, descobri que era mais rápido definir idcomo be (para este exemplo) 10000000000 - extract(epoch from logdate)e usá-lo como PRIMARY KEY, então

select id, date, content from measurements limit 500;

iria naturalmente ordenar por id, produzindo assim os registros mais recentes.

À medida que a tabela crescia, ela se tornava incontrolável, então recorri ao particionamento. Eu fiz exatamente como a documentação diz :

CREATE TABLE measurement_y2007m11 (
CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2007m12 (
CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2008m01 (
CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
) INHERITS (measurement);

e distribuí as linhas existentes em suas respectivas partições.

O problema é que estou particionando em logdate, mas ao consultar não sei o intervalo de tempo. Quando consulto as últimas 500 linhas, não consigo saber se são da semana passada, do último mês do último trimestre. Portanto, o planejador de consulta sempre verifica todas as partições.

Não acredito que ninguém tenha lidado com esse mesmo problema antes, parece trivial e, no entanto, me deixa intrigado.

postgresql index

1 respostas

  1. Best Answer

    Equívoco 1: "Ordem natural"

    <query without ORDER BY>
    ... naturalmente ordenaria por id, portanto, gerando os registros mais recentes.

    Não há ordem natural em uma SELECTdeclaração. Sem ORDER BYvocê obter linhas em ordem arbitrária. Geralmente, essa será a ordem mais barata na qual o Postgres pode satisfazer sua consulta, ou seja, a ordem na qual as tuplas são armazenadas fisicamente ou na qual são recuperadas após uma consulta de índice. Mas não há garantia alguma. Se sua declaração parecia funcionar, isso foi pura sorte/coincidência e pode quebrar a qualquer momento.

    Em vez disso, use sua primeira consulta . Se logdatefor, de fato, do tipo date, ou se você precisar ter certeza, deverá adicionar mais ORDER BYitens para desempate e obter uma ordem de classificação estável. Se você não se importa, anexe sua (nova) chave primária (veja abaixo):

    SELECT id, logdate, content
FROM   measurement
ORDER  BY logdate DESC, measurement_id DESC
LIMIT 500;

    measurement-idSe for garantido que a linha mais recente (maior ) terá o mais recente logdate, você pode apenas ORDER BY measurement_id DESC, mas não considere isso garantido. Em um ambiente multiusuário, uma linha com mais tarde logdatepode ser gravada antes de outra linha com mais cedo logdate.

    Esta é uma das razões pelas quais sua ideia para a nova chave primária não é muito útil:

    10000000000 - extract(epoch from logdate), e usá-lo comoPRIMARY KEY

    A outra razão: está fadado a falhar mais cedo ou mais tarde se logdatenão for garantido que seja único - o que provavelmente não é.

    Em vez disso, use uma serialcoluna measurement_idcomo chave primária. Ou bigserialse você espera mais de 2147483647 linhas ao longo do tempo.

    Índice

    Você afirma que fez exatamente como diz a documentação, e a documentação diz :

    Para cada partição, crie um índice na(s) coluna(s) chave, bem como...

    E mais abaixo:

    Provavelmente também precisamos de índices nas colunas-chave:

    CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
...

    A única pequena diferença: o exemplo no manual usa a forma singular mais sensata para o nome da tabela: measurementem vez de measurements.

    Se você seguir meu conselho:

    ORDER  BY logdate DESC, measurement_id DESC

    Faça aquilo:

    CREATE INDEX measurement_y2006m02_logdate
ON measurement_y2006m02 (logdate DESC, measurement_id DESC);
...

    Mais sobre por que isso provavelmente ajudaria:

    Equívoco 2: "verificar todas as partições"

    Portanto, o planejador de consulta sempre verifica todas as partições.

    O planejador de consulta planejará verificar todas as partições em sequência. Porém, assim que a consulta for satisfeita (500 linhas recuperadas), ela interromperá a execução. Teste com EXPLAIN ANALYZE, você verá a anotação (never executed)atrás das partições restantes.

    Se o planejador não for inteligente o suficiente para derivar a melhor sequência para digitalizar a partir de sua configuração (não é possível testar agora), você pode ajudar com uma UNION ALLconsulta nas partições:

    (
SELECT measurement_id, logdate, content FROM measurement_y2006m03_logdate
ORDER  BY logdate DESC, measurement_id DESC
)            -- parens needed to include ORDER BY in individual legs of UNION query
UNION ALL
(
SELECT measurement_id, logdate, content FROM measurement_y2006m02_logdate
ORDER  BY logdate DESC, measurement_id DESC
)
...          -- latest partition first

LIMIT 500;

    Mas isso pode não ser necessário.

    • 5

relate perguntas