Início / dba / Perguntas / 121237
Accepted
Gonzalo Vasquez
Asked: 2015-11-17 06:51:39 +0800 CST

Melhore o desempenho da consulta com muitas junções

  • 772

A partir do seguinte plano de explicação:

explain SELECT imei, cliente_avl_equipo.id_cliente AS idcliente,
               cliente_avl_vehiculo.grupo AS grupo, avl_vehiculo.id,
               avl_vehiculo.id_clase, avl_vehiculo_clase.icono AS icono,
               avl_vehiculo.descripcion, avl_vehiculo.patente,
               avl_vehiculo.info, avl_vehiculo.conductor,
               avl_vehiculo.fijo_chasis, avl_vehiculo.enabled
        FROM   cliente_avl_vehiculo,avl_vehiculo, avl_vehiculo_equipo,
               cliente_avl_equipo, avl_vehiculo_clase
        WHERE  avl_vehiculo.id                 =avl_vehiculo_equipo.id
        AND    cliente_avl_vehiculo.vehiculo_id=avl_vehiculo.id
        AND    cliente_avl_equipo.imei_equipo  =avl_vehiculo_equipo.imei
        AND    avl_vehiculo_clase.id           =avl_vehiculo.id_clase
        AND    avl_vehiculo_equipo.imei        =1234

Eu recebo uma saída como esta:

Nested Loop  (cost=0.00..22.57 rows=1 width=125
    ->  Nested Loop  (cost=0.00..22.29 rows=1 width=67)
        ->  Nested Loop  (cost=0.00..14.92 rows=1 width=63)
          ->  Nested Loop  (cost=0.00..14.58 rows=1 width=63)
                ->  Index Scan using fki_avl_vehiculo_equipo_imei on avl_vehiculo_equipo  (cost=0.00..7.28 rows=1 width=12)
                      Index Cond: (imei = 1234)
                ->  Index Scan using index_avl_vehiculo_id on avl_vehiculo  (cost=0.00..7.29 rows=1 width=51)
                      Index Cond: (id = avl_vehiculo_equipo.id)
          ->  Index Scan using index_cliente_avl_vehiculo_id on cliente_avl_vehiculo  (cost=0.00..0.33 rows=1 width=8)
               Index Cond: (vehiculo_id = avl_vehiculo.id)
       ->  Index Scan using fki_cliente_avl_equipo_imei on cliente_avl_equipo  (cost=0.00..7.36 rows=1 width=12)
              Index Cond: (imei_equipo = 1234)
    ->  Index Scan using index_avl_vehiculo_clase_id on avl_vehiculo_clase  (cost=0.00..0.27 rows=1 width=62)
        Index Cond: (id = avl_vehiculo.id_clase)

Não estou muito familiarizado com as saídas explicativas, mas as Index Scanlinhas parecem boas para mim, mas estou em dúvida com Nested Loopelas, qualquer conselho seria apreciado.

Saída adicionada explain(analyze, verbose, buffers)conforme solicitado por @a_horse_with_no_name:

Nested Loop  (cost=0.00..22.57 rows=1 width=125) (actual time=0.122..0.122 rows=0 loops=1)
  Output: avl_vehiculo_equipo.imei, cliente_avl_equipo.id_cliente, cliente_avl_vehiculo.grupo, avl_vehiculo.id, avl_vehiculo.id_clase, avl_vehiculo_clase.icono, avl_vehiculo.descripcion, avl_vehiculo.patente, avl_vehiculo.info, avl_vehiculo.conductor, avl_ (...)
  Buffers: shared hit=6
  ->  Nested Loop  (cost=0.00..22.29 rows=1 width=67) (actual time=0.121..0.121 rows=0 loops=1)
        Output: cliente_avl_vehiculo.grupo, avl_vehiculo.id, avl_vehiculo.id_clase, avl_vehiculo.descripcion, avl_vehiculo.patente, avl_vehiculo.info, avl_vehiculo.conductor, avl_vehiculo.fijo_chasis, avl_vehiculo.enabled, avl_vehiculo_equipo.imei, cliente (...)
        Buffers: shared hit=6
        ->  Nested Loop  (cost=0.00..14.92 rows=1 width=63) (actual time=0.120..0.120 rows=0 loops=1)
              Output: cliente_avl_vehiculo.grupo, avl_vehiculo.id, avl_vehiculo.id_clase, avl_vehiculo.descripcion, avl_vehiculo.patente, avl_vehiculo.info, avl_vehiculo.conductor, avl_vehiculo.fijo_chasis, avl_vehiculo.enabled, avl_vehiculo_equipo.imei
              Buffers: shared hit=6
              ->  Nested Loop  (cost=0.00..14.58 rows=1 width=63) (actual time=0.119..0.119 rows=0 loops=1)
                    Output: avl_vehiculo.id, avl_vehiculo.id_clase, avl_vehiculo.descripcion, avl_vehiculo.patente, avl_vehiculo.info, avl_vehiculo.conductor, avl_vehiculo.fijo_chasis, avl_vehiculo.enabled, avl_vehiculo_equipo.imei, avl_vehiculo_equipo.id
                    Buffers: shared hit=6
                    ->  Index Scan using fki_avl_vehiculo_equipo_imei on public.avl_vehiculo_equipo  (cost=0.00..7.28 rows=1 width=12) (actual time=0.118..0.118 rows=0 loops=1)
                          Output: avl_vehiculo_equipo.imei, avl_vehiculo_equipo.id, avl_vehiculo_equipo.movil, avl_vehiculo_equipo.creation, avl_vehiculo_equipo.updated, avl_vehiculo_equipo.power_input, avl_vehiculo_equipo.ign_input, avl_vehiculo_equipo.po (...)
                          Index Cond: (avl_vehiculo_equipo.imei = 1234)
                          Buffers: shared hit=6
                    ->  Index Scan using index_avl_vehiculo_id on public.avl_vehiculo  (cost=0.00..7.29 rows=1 width=51) (never executed)
                          Output: avl_vehiculo.id, avl_vehiculo.id_clase, avl_vehiculo.descripcion, avl_vehiculo.patente, avl_vehiculo.info, avl_vehiculo.numero, avl_vehiculo.conductor, avl_vehiculo.combustible, avl_vehiculo.consumo, avl_vehiculo.marca, av (...)
                          Index Cond: (avl_vehiculo.id = avl_vehiculo_equipo.id)
              ->  Index Scan using index_cliente_avl_vehiculo_id on public.cliente_avl_vehiculo  (cost=0.00..0.33 rows=1 width=8) (never executed)
                    Output: cliente_avl_vehiculo.cliente_rut, cliente_avl_vehiculo.vehiculo_id, cliente_avl_vehiculo.id_cliente, cliente_avl_vehiculo.grupo
                    Index Cond: (cliente_avl_vehiculo.vehiculo_id = avl_vehiculo.id)
        ->  Index Scan using fki_cliente_avl_equipo_imei on public.cliente_avl_equipo  (cost=0.00..7.36 rows=1 width=12) (never executed)
              Output: cliente_avl_equipo.rut_cliente, cliente_avl_equipo.imei_equipo, cliente_avl_equipo.id_cliente
              Index Cond: (cliente_avl_equipo.imei_equipo = 1234)
  ->  Index Scan using index_avl_vehiculo_clase_id on public.avl_vehiculo_clase  (cost=0.00..0.27 rows=1 width=62) (never executed)
        Output: avl_vehiculo_clase.id, avl_vehiculo_clase.descripcion, avl_vehiculo_clase.categoria, avl_vehiculo_clase.icono
        Index Cond: (avl_vehiculo_clase.id = avl_vehiculo.id_clase)
Total runtime: 1.535 ms
postgresql index

1 respostas

  1. Best Answer

    Use aliases de tabela, sintaxe JOIN explícita, corte o ruído e formate o código para torná-lo legível por humanos:

    SELECT ave.imei
     , cae.id_cliente AS idcliente  -- odd alias?
     , cav.grupo
     , av.id
     , av.id_clase
     , avc.icono
     , av.descripcion
     , av.patente
     , av.info
     , av.conductor
     , av.fijo_chasis
     , av.enabled
FROM   avl_vehiculo_equipo  ave
JOIN   cliente_avl_equipo   cae ON cae.imei_equipo = ave.imei
JOIN   avl_vehiculo         av  ON av.id           = ave.id
JOIN   cliente_avl_vehiculo cav ON cav.vehiculo_id = av.id
JOIN   avl_vehiculo_clase   avc ON avc.id          = av.id_clase
WHERE  ave.imei = 1234;

    Agora é evidente que, exceto para avl_vehiculo, você só precisa de duas colunas de cada tabela, provavelmente apenas integercolunas (seria o ideal).

    Para melhor desempenho de leitura otimizado para esta consulta, tenha este conjunto de índices:

    ave(imei, id)                -- index only scan
cae(imei_equipo, id_cliente) -- index only scan
av (id, id_clase)            -- index scan
cav(vehiculo_id, grupo)      -- index only scan
avc(id, icono)               -- index only scan

    Assumindo o Postgres 9.2 ou posterior, e se algumas pré-condições forem atendidas, agora você obterá varreduras somente de índice para todos, exceto avl_vehiculo.

    Eu faria CLUSTER(ou pg_repack se você não puder pagar bloqueios exclusivos) todas as tabelas envolvidas com base nesses índices pelo menos uma vez. Mais:

    A sequência de colunas de índice é importante!

    • 1

relate perguntas