Início / dba / Perguntas / 187055
Accepted
Hassan Baig
Asked: 2017-09-28 06:15:02 +0800 CST

Otimizando instruções de manutenção do postgresql no psql

  • 772

Em um aplicativo semelhante ao reddit (chamado Links) eu mantenho, os usuários postam hiperlinks e outros podem responder publicamente nos referidos links. O primeiro é salvo em uma tabela postgresql 9.3.10 chamada links_link, o último em uma tabela chamada links_publicreply.

Minha tarefa é:

  1. Exclua todos os linkobjetos criados em uma janela de 30 dias.

  2. Exclua todos os publicreplyobjetos associados a objetos em (1) e criados em uma janela de 45 dias.

Aqui está como eu fiz isso para uma janela de tempo de 2016:

begin;
  delete from links_publicreply where submitted_on >= timestamp'2016-07-23 01:01:00' and submitted_on < timestamp'2016-09-07 01:00:00' and answer_to_id in (select id from links_link where submitted_on >=timestamp'2016-07-23 01:00:00' and submitted_on < timestamp'2016-08-23 01:00:00');
  delete from links_link where submitted_on >= timestamp'2016-07-23 01:00:00' and submitted_on < timestamp'2016-08-23 01:00:00';
commit;

Observe que estou consultando links_linkexatamente da mesma maneira duas vezes . Ou seja, uma vez cada em ambas as deletedeclarações. Agora, no Django ORM (do qual sou nativo), eu teria otimizado isso primeiro obtendo todos os links_linkids de objeto necessários separadamente e, em seguida, usando-os em todas as instruções de procedimento. Como faço para otimizar isso na psqllinha de comando?

O conselho mais pertinente que vi está nesta resposta SO , mas parece que a withcláusula deve estar dentro da instrução SQL em que está sendo chamada.

Em outras palavras, o seguinte não funcionaria?

BEGIN;
  DELETE FROM links_publicreply
    WHERE submitted_on >= timestamp '2016-07-23 01:01:00'
        AND submitted_on < timestamp '2016-09-07 01:00:00'
        AND answer_to_id in (
            SELECT id
            FROM links_link
            WHERE submitted_on >= timestamp '2016-07-23 01:00:00'
                AND submitted_on < timestamp '2016-08-23 01:00:00'
    );
    DELETE FROM links_link
    WHERE submitted_on >= timestamp'2016-07-23 01:00:00'
        AND submitted_on < timestamp'2016-08-23 01:00:00';
COMMIT;
postgresql postgresql-9.3

1 respostas

  1. Best Answer

    O conselho mais pertinente que vi está nesta resposta SO, mas parece que a cláusula with deve estar dentro da instrução SQL em que está sendo chamada.

    Sim, você pode usar um modificador WITH, ou seja, um CTE que exclui de uma tabela e retorna os idvalores para o restante da consulta, para que possam ser usados ​​para excluir da segunda tabela:

    WITH
  links_link_deleted AS
    ( DELETE FROM links_link
        WHERE submitted_on >= timestamp'2016-07-23 01:00:00'
          AND submitted_on < timestamp'2016-08-23 01:00:00'
      RETURNING id
    ) 
DELETE FROM links_publicreply
  WHERE submitted_on >= timestamp '2016-07-23 01:01:00'
    AND submitted_on < timestamp '2016-09-07 01:00:00'
    AND answer_to_id IN
        ( SELECT id
          FROM links_link_deleted
        )
 ;

    Em outras palavras, o seguinte não funcionaria?

    Isso funcionaria, mas seriam duas declarações.

    • 2

relate perguntas