Postgresql – corrigindo dados de registro inválidos

Quando chegam dados de log inválidos de diferentes versões de um aplicativo, queremos forçar a atualização para um valor unificado. Por exemplo:

  • Aplicativo iOS antigo envia source = ‘ios’, partner = ‘viki’

  • Novo aplicativo iOS envia app_id = ‘10004a’

Queremos atualizar os dados da tabela (app_id, source, partner) para os valores (‘10004a’, ‘ios’, ‘viki’);

CREATE TABLE logging(
app_id VARCHAR
(15),
source VARCHAR
(50),
partner VARCHAR
(50)
);

Pergunta: O que é mais rápido, realizando 1 atualização ou 2 atualizações?

1 atualização:

UPDATE logging M
SET source
= 'ios',
partner
= 'viki',
app_id
= '10004a'
WHERE app_id
= '10004a'
OR
(source = 'ios' AND partner = 'viki');

2 atualizações:

UPDATE logging M
SET source
= 'ios',
partner
= 'viki'
WHERE app_id
= '10004a'
AND
(source != 'ios' OR partner != 'viki');

UPDATE logging M

SET app_id
= '10004a'
WHERE
(source = 'ios' AND partner = 'viki')
AND app_id
!= '10004a';

Resposta: Com índices, ambos são quase iguais. Possivelmente porque:

  • Requer apenas 1 varredura para o primeiro.

  • Requer menos operações de atribuição para o segundo.

  • Com 2 atualizações, a nova execução subsequente será muito mais rápida, pois não é necessário realizar atribuições em linhas já corretas.

O teste:

-- Creating 2 identical test tables

CREATE TABLE logging_1
(
app_id VARCHAR
(15),
source VARCHAR
(50),
partner VARCHAR
(50)
);
INSERT INTO logging_1 SELECT
'10004a', '', '' FROM generate_series(1, 1000000);
INSERT INTO logging_1 SELECT
'', 'ios', 'viki' FROM generate_series(1, 1000000);

CREATE INDEX ON logging_1
(app_id);
CREATE INDEX ON logging_1
(source);
CREATE INDEX ON logging_1
(partner);


CREATE TABLE logging_2
(
app_id VARCHAR
(15),
source VARCHAR
(50),
partner VARCHAR
(50)
);
INSERT INTO logging_2 SELECT
'10004a', '', '' FROM generate_series(1, 1000000);
INSERT INTO logging_2 SELECT
'', 'ios', 'viki' FROM generate_series(1, 1000000);

CREATE INDEX ON logging_2
(app_id);
CREATE INDEX ON logging_2
(source);
CREATE INDEX ON logging_2
(partner);


-- 1 update
UPDATE logging_1

SET source
= 'ios',
partner
= 'viki',
app_id
= '10004a'
WHERE app_id
= '10004a'
OR
(source = 'ios' AND partner = 'viki');

# UPDATE 2000000
# Time: 16790.319 ms


-- 2 updates
UPDATE logging_2

SET source
= 'ios',
partner
= 'viki'
WHERE app_id
= '10004a'
AND
(source != 'ios' OR partner != 'viki');

# UPDATE 1000000
# Time: 7591.592 ms


UPDATE logging_2

SET app_id
= '10004a'
WHERE
(source = 'ios' AND partner = 'viki')
AND app_id
!= '10004a';

# UPDATE 1000000
# Time: 10357.224 ms