Início / dba / Perguntas / 18808
Accepted
bmargulies
Asked: 2012-06-05 15:47:30 +0800 CST

acelerando uma exclusão no postgresql

  • 772

Eu vim com:

drop table if exists idtemp;
create temp table idtemp as 
      select documentid from taskflag where taskid='coref' and state = 2 
         order by statechanged asc limit howmany;
create unique index on idtemp(documentid);

-- trim taskflag to the first N docs ordered by coref.
delete from taskflag where documentid not in (select documentid from idtemp) ;

Isso é muito lento quando há 120 mil registros em taskflag e estou mantendo 10 mil.

Taskflag se parece com:

\d taskflag
                Table "public.taskflag"
    Column    |            Type             | Modifiers 
--------------+-----------------------------+-----------
 documentid   | character varying(64)       | not null
 taskid       | character varying(64)       | not null
 state        | smallint                    | 
 statechanged | timestamp without time zone | 
Indexes:
    "taskflag_pkey" PRIMARY KEY, btree (documentid, taskid)
    "task_index2" btree (documentid)
    "task_index4" btree (taskid, state, statechanged)

Explique disse:

                                QUERY PLAN                                    
----------------------------------------------------------------------------------
 Delete on taskflag  (cost=0.00..105811822.25 rows=223210 width=6)
   ->  Seq Scan on taskflag  (cost=0.00..105811822.25 rows=223210 width=6)
         Filter: (NOT (SubPlan 1))
         SubPlan 1
           ->  Materialize  (cost=0.00..449.00 rows=10000 width=146)
                 ->  Seq Scan on idtemp  (cost=0.00..184.00 rows=10000 width=146)
(6 rows)

Devo apenas providenciar para que a tabela temporária contenha os que estou mantendo?

postgresql

2 respostas

  1. Best Answer

    A otimização mais fácil provavelmente seria permitir que o planejador usasse um hash anti-junção, reescrevendo a consulta como:

     delete from taskflag where not exists
  (select 1 from idtemp where documentid=taskflag.documentid);

    também pode ser necessário ANALISAR a tabela temporária imediatamente após preenchê-la.

    • 4

  2. Eu configurei um teste. Tentar excluir da maneira que você fez fornece praticamente o mesmo plano de consulta. Se você executar um ANALYZE idtempantes de excluir, o plano muda para o seguinte:

                                    QUERY PLAN
---------------------------------------------------------------------------
 Delete on taskflag  (cost=198.00..3041.00 rows=60000 width=6)
   ->  Seq Scan on taskflag  (cost=198.00..3041.00 rows=60000 width=6)
         Filter: (NOT (hashed SubPlan 1))
         SubPlan 1
           ->  Seq Scan on idtemp  (cost=0.00..173.00 rows=10000 width=24)

    E a maneira como a_horse_with_no_name sugeriu:

    EXPLAIN delete from taskflag where not (taskid='coref' and state = 2);

                             QUERY PLAN
---------------------------------------------------------------------
 Delete on taskflag  (cost=0.00..3509.13 rows=1 width=6)
   ->  Seq Scan on taskflag  (cost=0.00..3509.13 rows=1 width=6)
         Filter: (((taskid)::text <> 'coref'::text) OR (state <> 2))

    Na minha caixa de teste, não tive paciência para esperar a sua versão terminar :) A primeira versão ANALYZEcompleta um pouco mais de 1 segundo (mas leva mais de meio segundo para executar o INSERTe o CREATE INDEX, o que significa cerca de 2 segundos no total), o reformulado DELETEapenas um pouco menos de 1 segundo.

    A segunda sugestão de @a_horse_with_no_name (veja o comentário abaixo) com o seguinte código

    DROP TABLE IF EXISTS idtemp;

CREATE TABLE idtemp AS 
    SELECT documentid 
    FROM taskflag 
    WHERE taskid = 'coref' AND state = 2 
    ORDER BY statechanged ASC LIMIT 10000;

ALTER TABLE idtemp
ADD CONSTRAINT pk_taskflag PRIMARY KEY (documentid, taskid);

CREATE INDEX idx_11 ON idtemp (documentid);

CREATE INDEX idx_12 ON idtemp (taskid, state, statechanged);

ANALYZE idtemp;

DROP TABLE taskflag;

ALTER TABLE idtemp RENAME TO taskflag;

    também leva cerca de 900 ms, o que significa que, neste caso específico (com meus dados de teste específicos), essa abordagem é bastante competitiva. Se sua taskflagtabela tivesse objetos dependentes, não funcionaria.

    • 2

relate perguntas