Início / dba / Perguntas / 302234
Accepted
ConanTheGerbil
Asked: 2021-11-07 07:17:17 +0800 CST

Tempo de execução diferente para a mesma consulta em diferentes bancos de dados Postgres

  • 772

Eu tenho dois bancos de dados idênticos, desenvolvimento e ao vivo.

Eu executo esta consulta em ambos ....

select count(*) FROM DeviceB where deviceID not in (SELECT distinct deviceId FROM Device)

Na dev box a consulta demora cerca de 100ms, na live demora cerca de uma hora.

Ambas as tabelas têm um campo de chave primária chamado deviceId, ambos os bancos de dados têm estrutura idêntica e ambos têm dados muito semelhantes (nenhuma das tabelas contém mais de um milhão de linhas). Ambos os bancos de dados são limpos/analisados ​​regularmente, ambos foram feitos nas últimas 12 horas.

Cada banco de dados está em um computador diferente, o hardware é diferente para cada um, mas em 99% do tempo o live box é cerca de 10% mais lento.

Tanto quanto eu posso dizer, ambos os bancos de dados são configurados da mesma forma.

Meu palpite é que há alguma diferença sutil na distribuição dos dados em cada banco de dados que afeta a escolha do plano de explicação.

Minhas perguntas são:

  • Existe alguma maneira de forçar o banco de dados a usar um índice específico?
  • Existe uma forma diferente de consulta SQL que produziria os mesmos resultados, mas mais rapidamente?

explicar plano dev - leva menos de um segundo:

Aggregate  (cost=7806.68..7806.69 rows=1 width=8)
  ->  Seq Scan on deviceb  (cost=4624.86..7702.06 rows=41848 width=0)
        Filter: (NOT (hashed SubPlan 1))
        SubPlan 1
          ->  Unique  (cost=0.29..4404.59 rows=88108 width=7)
                ->  Index Only Scan using device_pkey on device  (cost=0.29..4184.32 rows=88108 width=7)

explicar o plano ao vivo - leva cerca de uma hora:

Finalize Aggregate  (cost=747368287.67..747368287.68 rows=1 width=8)
  ->  Gather  (cost=747368287.46..747368287.67 rows=2 width=8)
        Workers Planned: 2
        ->  Partial Aggregate  (cost=747367287.46..747367287.47 rows=1 width=8)
              ->  Parallel Seq Scan on deviceb  (cost=0.42..747367217.98 rows=27789 width=0)
                    Filter: (NOT (SubPlan 1))
                    SubPlan 1
                      ->  Materialize  (cost=0.42..26001.96 rows=156913 width=7)
                            ->  Unique  (cost=0.42..22822.40 rows=156913 width=7)
                                  ->  Index Only Scan using device_pkey on device  (cost=0.42..21930.12 rows=156913 width=7)

Eu comparei as versões dev/live das tabelas usando pgAdmin4 - simplesmente destacando o objeto e selecionando as propriedades de cada tabela e seus índices e restrições.

Além disso - meus testes (em ambos os bancos de dados) foram feitos sem outros usuários conectados.

DDL, conforme solicitado:

CREATE TABLE public.device (
    deviceid character(6) COLLATE pg_catalog."default" NOT NULL,
    recentfixtime timestamp without time zone,
    newfixes integer DEFAULT 0,
    lastfixid bigint DEFAULT 0,
    CONSTRAINT device_pkey PRIMARY KEY (deviceid)
        USING INDEX TABLESPACE fastspace
);


CREATE TABLE public.deviceb  (
    deviceid character(6) COLLATE pg_catalog."default" NOT NULL,
    flightid integer DEFAULT 0,
    lastfirstfixtime timestamp without time zone,
    lastprocfixtime timestamp without time zone,
    lastprocfly boolean,
    priority integer DEFAULT 0,
    lastlandtime timestamp without time zone,
    CONSTRAINT deviceb_pkey PRIMARY KEY (deviceid)
        USING INDEX TABLESPACE fastspace
)

ATUALIZAÇÃO - RESOLVIDO, graças a Jjanes, a versão sugerida é incrivelmente rápida em ambos os bancos de dados.

select count(*) FROM DeviceB where NOT EXISTS (
    SELECT 1 from Device where Device.deviceID=DeviceB.deviceID
)

Admito que ainda não tenho certeza de qual é a causa raiz, mas, para ser completo, tentarei responder a algumas das outras perguntas. O Autoanalyse ainda está ativado (pgAdmin/statistics mostra uma última data do Autoanalyse no início desta manhã. ambas as tabelas (ao vivo) têm menos de 200.000 linhas, ambas as tabelas no dev têm menos de 100.000. O hardware nas caixas dev/live é diferente (o maior diferença é que dev tem 32G de memória, live apenas 16G. Possivelmente a diferença mais significativa está nas versões, dev é 13.1, live ainda está em 10.4

postgresql performance

2 respostas

  1. Best Answer

    Fiz uma série de perguntas investigando a causa raiz. Mas, independentemente dos detalhes exatos da causa raiz, você pode se isolar do problema usando NOT EXISTS em vez de NOT IN.

    select count(*) FROM DeviceB where NOT EXISTS (
    SELECT 1 from Device where Device.deviceID=DeviceB.deviceID
)
    • 1

  2. Existe alguma maneira de forçar o banco de dados a usar um índice específico?

    Não. Não diretamente. Você pode "desabilitar" outros métodos de acesso para fazer o Postgres escolher uma varredura de índice ou varredura de índice de bitmap. Ver:

    Mas você não pode instruí-lo diretamente a usar um determinado índice. Esse não é o problema aqui de qualquer maneira.

    Existe uma forma diferente de consulta SQL que produziria os mesmos resultados, mas mais rapidamente?

    Sim. Soltar DISTINCT:

    SELECT count(*) FROM deviceb
WHERE deviceid NOT IN (SELECT deviceid FROM device);

    Tentar remover duplicatas de grandes conjuntos é caro. Como device.deviceidé o PRIMARY KEY, não pode haver duplicatas.

    Se valores NULL ou duplicatas pudessem estar envolvidos, examinaríamos outros estilos de consulta, mas para duas chaves primárias, a consulta deve ser a ideal.

    A varredura sequencial paralela devicebé o problema, como vemos aqui:

    Verificação sequencial paralela no dispositivob (custo=0,42.. 747367217,98 linhas=27789 largura=0)

    Nenhum índice envolvido. Isso não deve ser tão caro. Também me pergunto por que não vemos uma linha como:

    'Linhas removidas pelo filtro: 123456'

    Que versão do Postgres é essa? E como você produziu exatamente o plano de consulta?

    Ou algo está errado com a tabela :

    • Muito mais linhas do que você pensa? Verificar com:
    SELECT count(*) FROM deviceb;
    • Tabela extrema e inchaço do índice? Verificar com:
    SELECT pg_size_pretty(pg_table_size('deviceb'))
     , pg_size_pretty(pg_table_size('deviceb_pkey'));

SELECT * FROM pg_stat_user_tables WHERE relname = 'deviceb';

    Corrija com outro VACUUM ANALYZE deviceb;, talvez até VACUUM FULL ANALYZE deviceb;porque não há outros usuários conectados de qualquer maneira (?)

    • Fechaduras exclusivas?

    Não pode ser isso. Você disse:

    nenhum outro usuário conectado.

    • Problemas de hardware?

    Ou algo está errado com o paralelismo . Tente desativá-lo (para depuração!) e teste novamente:

    SET max_parallel_workers_per_gather = 0;

    Também:

    Ambos os bancos de dados são limpos/analisados ​​regularmente, ambos foram feitos nas últimas 12 horas.

    Parece que você está executando o manual VACUUM/ ANALYZE. Você desativou autovacuum? (Normalmente, você não deveria.)

    Embora a varredura sequencial dolorosamente lenta (paralela) deva ser examinada, eu me pergunto por que não vemos uma varredura somente de índice divicebpara começar. Com mesas recém VACUUM'ed e um TABLESPACEnome fastspaceque eu esperaria tanto. Você está ciente de que alguns parâmetros-chave podem ser definidos porTABLESPACE ? O mais importante seq_page_coste random_page_cost. Curti:

    ALTER TABLESPACE fastspace SET (seq_page_cost = 0.5, random_page_cost = 1);

    Ajuste às características do seu "fastspace".

    • 0

relate perguntas