Joe's questions

Estou executando um Aurora PostgreSQL em uma db.x2g.2xlargeinstância. A simples criação de tabela temporária apareceu como a terceira maior espera, o que me surpreende, pois esperava que fosse uma das consultas mais leves.

A consulta é chamada em uma transação:

CREATE TEMP TABLE identifiers_to_resolve (  
  scheme_pk BIGINT,
  host_pk BIGINT,
  port INT NOT NULL,
  scheme_specific_part VARCHAR NOT NULL,
  index INTEGER NOT NULL
)  ON COMMIT DROP;

Quero usar a tabela temporária como um armazenamento temporário na transação durante uma consulta mais complicada.

As estatísticas dos insights de desempenho:

• Carga por espera (AAS): 0,42
• Chamadas/s: 123,64
• Acertos pretos/s: 48.499,58
• Gravações pretas/seg: 16,32
• Latência média ms/chamada: 3,24
• Preto atinge/liga 329,27
• Blk escreve/chama 0,13

Todas as outras estatísticas são 0.

Os insights de desempenho registram as esperas como quase todas as "CPU". Captura de tela anexada.

Acho que 123 chamadas por segundo é uma taxa bastante baixa e estou surpreso que esteja sobrecarregando tanto o banco de dados e afetando tantos blocos. Pelos casos de uso que li sobre tabelas temporárias, esperava que tivessem menor impacto.

É típico que a criação de tabelas temporárias cause tanta espera na CPU?

Em um plano de consulta, vejo o tipo de linha sendo lançado. Existe um índice que deveria ser usado e não é. O typecast é uma bandeira vermelha?

A tabela teve um ANALYZE e possui 110 milhões de linhas. Destes, 10% correspondem à consulta.

Aqui está a explicação completa:

explain select * from item_info where subtype = 'Dataset';

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Gather  (cost=1000.00..1592021.24 rows=1320650 width=27)
   Workers Planned: 2
   ->  Parallel Seq Scan on item_info  (cost=0.00..1458956.24 rows=550271 width=27)
         Filter: ((subtype)::text = 'Dataset'::text)
(4 rows)

Time: 0.652 ms=> \d item_info
                     Table "public.item_info"
    Column    |       Type        | Collation | Nullable | Default
--------------+-------------------+-----------+----------+---------
 root_item_pk | bigint            |           | not null |
 type         | character varying |           |          |
 subtype      | character varying |           |          |
Indexes:
    "item_info_pkey" PRIMARY KEY, btree (root_item_pk)
    "item_info_subtype_idx" btree (subtype)
    "item_info_type_idx" btree (type)

Estou usando um grande número de TEMP TABLESe quero ter certeza de que não estou vazando armazenamento.

Eu crio e uso a tabela em uma transação com CREATE TEMP TABLE mytable ... ON COMMIT DROP.

Estou tendo problemas para entender a documentação do CREATE TEMPORARY TABLE. Ele afirma:

O daemon autovacuum não pode acessar e, portanto, não pode aspirar ou analisar tabelas temporárias. Por esse motivo, as operações apropriadas de vácuo e análise devem ser executadas por meio de comandos SQL de sessão.

Vou ON COMMIT DROPrecuperar espaço ou preciso realmente correr VACUUM mytable?

Se eu precisar executar VACUUM, não sei como, pois a tabela temporária não está disponível após o término da transação.

Eu tenho uma tabela grande (talvez talvez um bilhão de linhas, mas atualmente ~ 26 milhões) para a qual quero definir um sinalizador no PK mais alto para um determinado agrupamento, em um lote único.

Optei por criar uma tabela temporária que armazena os PKs que devem ser configurados current=true, o restante deve ser configurado current=false. Fiz uma tabela temporária em vez de uma visão materializada, mas acho que não faria diferença real.

O processo de descobrir o ID máximo para cada um não é muito doloroso:

CREATE TABLE assertion (
    pk integer NOT NULL,
    a bigint NOT NULL,
    b bigint NOT NULL,
    c bigint NOT NULL,
    d integer NOT NULL,
    current boolean DEFAULT false NOT NULL
);

CREATE INDEX assertion_current_idx ON assertion USING btree (current) WHERE (current = true);
CREATE INDEX assertion_current_idx1 ON assertion USING btree (current);
CREATE UNIQUE INDEX assertion_a_b_c_d_idx ON assertion USING btree (a, b, c, d) WHERE (current = true);

SELECT COUNT(pk) FROM assertion;

-- 26916858
-- Time: 2912.403 ms (00:02.912)

CREATE TEMPORARY TABLE assertion_current AS
    (SELECT MAX(pk) as pk, a, b, c, d
      FROM assertion
      GROUP BY a, b, c, d);

-- Time: 72218.755 ms (01:12.219)

ANALYZE assertion_current;

CREATE INDEX ON assertion_current(pk);

-- Time: 22107.698 ms (00:22.108)

SELECT COUNT(pk) FROM assertion_current;

-- 26455092
-- Time: 15650.078 ms (00:15.650)

De acordo com a contagem de assertion_current, precisamos definir o sinalizador 'atual' como verdadeiro para 98% das linhas.

O complicado é como atualizar a assertiontabela em tempo razoável com base nos valores atuais. Existe uma restrição única na a, b, c, d, currentqual deve ser mantida, portanto, a atualização da currentcoluna precisa ser atômica para evitar a quebra da restrição.

Tenho algumas opções:

Opção 1

Atualize apenas os currentvalores que mudam. Isso tem a vantagem de atualizar o menor número de linhas necessário com base em um campo indexado:

BEGIN;
UPDATE assertion
   SET current = false
   WHERE assertion.current = true AND PK NOT IN (SELECT pk FROM assertion_current);
UPDATE assertion
   SET current = true
   WHERE assertion.current = false AND PK IN (SELECT pk FROM assertion_current);
COMMIT;

Mas ambas as consultas envolvem varreduras de sequência nas assertion_currentquais (eu acho) teriam que ser multiplicadas por um grande número de linhas.

Update on assertion  (cost=0.12..431141.55 rows=0 width=0)
   ->  Index Scan using assertion_current_idx on assertion  (cost=0.12..431141.55 rows=1 width=7)
         Index Cond: (current = true)
         Filter: (NOT (SubPlan 1))
         SubPlan 1
           ->  Materialize  (cost=0.00..787318.40 rows=29982560 width=4)
                 ->  Seq Scan on assertion_current  (cost=0.00..520285.60 rows=29982560 width=4)

e

 Update on assertion  (cost=595242.56..596693.92 rows=0 width=0)
   ->  Nested Loop  (cost=595242.56..596693.92 rows=17974196 width=13)
         ->  HashAggregate  (cost=595242.00..595244.00 rows=200 width=10)
               Group Key: assertion_current.pk
               ->  Seq Scan on assertion_current  (cost=0.00..520285.60 rows=29982560 width=10)
         ->  Index Scan using assertion_pkey on assertion  (cost=0.56..8.58 rows=1 width=10)
               Index Cond: (pk = assertion_current.pk)
               Filter: (NOT current)

Isso significa que uma dessas consultas (muitas atuais verdadeiras ou muitas atuais falsas) sempre leva muito tempo.

opção 2

Passe único, mas tem que tocar em todas as linhas desnecessariamente.

UPDATE assertion
   SET current =
     (CASE WHEN assertion.pk IN (select PK from assertion_current)
     THEN TRUE ELSE FALSE END)

mas isso resulta em uma varredura de sequência em assertion_current novamente

 Update on assertion  (cost=0.00..15498697380303.70 rows=0 width=0)
   ->  Seq Scan on assertion  (cost=0.00..15498697380303.70 rows=35948392 width=7)
         SubPlan 1
           ->  Materialize  (cost=0.00..787318.40 rows=29982560 width=4)
                 ->  Seq Scan on assertion_current  (cost=0.00..520285.60 rows=29982560 width=4)

Opção 3

Como a opção 1, mas use WHEREna atualização:

BEGIN;
UPDATE assertion SET current = false WHERE current = true;
UPDATE assertion SET current = true FROM assertion_current
  WHERE assertion.pk = assertion_current.pk;
COMMIT;

mas a segunda consulta envolve duas varreduras seq:

 Update on assertion  (cost=1654256.82..2721576.65 rows=0 width=0)
   ->  Hash Join  (cost=1654256.82..2721576.65 rows=29982560 width=13)
         Hash Cond: (assertion_current.pk = assertion.pk)
         ->  Seq Scan on assertion_current  (cost=0.00..520285.60 rows=29982560 width=10)
         ->  Hash  (cost=1029371.92..1029371.92 rows=35948392 width=10)
               ->  Seq Scan on assertion  (cost=0.00..1029371.92 rows=35948392 width=10)

Opção 4

Obrigado @jjanes, isso levou > 6 horas, então eu cancelei.

UPDATE assertion
   SET current = not current
   WHERE current <>
     (CASE WHEN assertion.pk IN (select PK from assertion_current)
     THEN TRUE ELSE FALSE END)

produz

 Update on assertion  (cost=0.00..11832617068493.14 rows=0 width=0)
   ->  Seq Scan on assertion  (cost=0.00..11832617068493.14 rows=27307890 width=7)
         Filter: (current <> CASE WHEN (SubPlan 1) THEN true ELSE false END)
         SubPlan 1
           ->  Materialize  (cost=0.00..787318.40 rows=29982560 width=4)
                 ->  Seq Scan on assertion_current  (cost=0.00..520285.60 rows=29982560 width=4)

Opção 5

Obrigado @a_horse_with_no_name. Isso leva 24 minutos na minha máquina.

UPDATE assertion tg SET current = EXISTS (SELECT pk FROM assertion_current cr WHERE cr.pk = tg.pk);

dá

 Update on assertion tg  (cost=0.00..233024784.94 rows=0 width=0)
   ->  Seq Scan on assertion tg  (cost=0.00..233024784.94 rows=27445116 width=7)
         SubPlan 1
           ->  Index Only Scan using assertion_current_pk_idx on assertion_current cr  (cost=0.44..8.46 rows=1 width=0)
                 Index Cond: (pk = tg.pk)

Existe uma maneira melhor de conseguir isso em tempo hábil?

Estou fazendo uma alteração. Atualmente, ele está sendo executado em uma mesa grande (300.000.000).

MariaDB [my_database]> ALTER TABLE my_table
    -> add INDEX a (x, y, z),
    -> add INDEX d (x);
Stage: 1 of 2 'copy to tmp table'   60.1% of stage done

Mas o processlist não menciona isso, com consultas repetidas.

MariaDB [my_database]> show full processlist;
+----+------+-----------------+-------------+---------+------+-------+-----------------------+----------+
| Id | User | Host            | db          | Command | Time | State | Info                  | Progress |
+----+------+-----------------+-------------+---------+------+-------+-----------------------+----------+
|  6 | apps | localhost:52235 | my_database | Sleep   |  304 |       | NULL                  |    0.000 |
| 33 | apps | localhost       | my_database | Query   |    0 | NULL  | show full processlist |    0.000 |
+----+------+-----------------+-------------+---------+------+-------+-----------------------+----------+
2 rows in set (0.01 sec)

Eu esperaria que isso aparecesse. Alguma ideia de por que não?

Eu tenho uma tabela PostgreSQL. select *é muito lento enquanto select idé bom e rápido. Acho que pode ser que o tamanho da linha seja muito grande e esteja demorando para transportar, ou pode ser algum outro fator.

Eu preciso de todos os campos (ou quase todos eles), então selecionar apenas um subconjunto não é uma solução rápida. A seleção dos campos que desejo ainda é lenta.

Aqui está meu esquema de tabela menos os nomes:

integer                  | not null default nextval('core_page_id_seq'::regclass)
character varying(255)   | not null
character varying(64)    | not null
text                     | default '{}'::text
character varying(255)   | 
integer                  | not null default 0
text                     | default '{}'::text
text                     | 
timestamp with time zone | 
integer                  | 
timestamp with time zone | 
integer                  | 

O tamanho do campo de texto pode ser de qualquer tamanho. Mas ainda assim, não mais do que alguns kilobytes na pior das hipóteses.

Perguntas

1. Existe algo sobre isso que grita 'louco ineficiente'?
2. Existe uma maneira de medir o tamanho da página na linha de comando do Postgres para me ajudar a depurar isso?