Início / dba / Perguntas / 110636
Accepted
happy_marmoset
Asked: 2015-08-11 23:54:24 +0800 CST

Baixo desempenho na consulta com LIMIT quando adiciono um ORDER BY?

  • 772

Dada a tabela:

Coluna Modelo Modificadores Armazenar
Eu iria bigint não nulo padrão nextval('items_id_seq'::regclass) avião
dados texto não nulo estendido
object_id bigint não nulo avião

e índices:

  • "items_pkey" CHAVE PRIMÁRIA, btree (id)
  • "items_object_id_idx" btree (object_id)

Quando eu executo:

SELECT *
FROM items
WHERE object_id = 123
LIMIT 1;

Ele retorna 0 linhas muito rapidamente. Porém, quando executo essa query com ORDER BY, ela trava por muito tempo:

SELECT *
FROM items
WHERE object_id = 123
ORDER BY id DESC  -- I added the ORDER BY
LIMIT 1;

O que explica essa discrepância?

Planos de consulta

Consulta rápida (sem ORDER BY)

                                                                          QUERY PLAN                                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..3.34 rows=1 width=63) (actual time=0.014..0.014 rows=0 loops=1)
   ->  Index Scan using items_object_id_operation_idx on items  (cost=0.56..2579.16 rows=929 width=63) (actual time=0.013..0.013 rows=0 loops=1)
         Index Cond: (object_id = 123::bigint)
 Total runtime: 0.029 ms

Consulta lenta (com o ORDER BY)

                                                                            QUERY PLAN                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.44..1269.14 rows=1 width=63) (actual time=873796.061..873796.061 rows=0 loops=1)
   ->  Index Scan Backward using items_pkey on items  (cost=0.44..1164670.11 rows=918 width=63) (actual time=873796.059..873796.059 rows=0 loops=1)
         Filter: (object_id = 123::bigint)
         Rows Removed by Filter: 27942522
 Total runtime: 873796.113 ms
postgresql postgresql-performance

2 respostas

  1. Tentando explicar porque há diferença de desempenho entre as duas consultas.

    Este: SELECT * FROM "items" WHERE "object_id" = '123' LIMIT 1é satisfeito por qualquer linha com o correspondente object_id, então o índice on object_idé uma escolha natural. A consulta requer E/S mínima: varredura de índice para encontrar o primeiro valor correspondente mais uma leitura de heap para buscar a linha inteira.

    A alternativa: SELECT * FROM "items" WHERE "object_id" = '123' ORDER BY "id" DESC LIMIT 1exige que todas as linhas com a correspondência object_idsejam classificadas por outra coluna, id, e então a linha com o valor máximo de idseja retornada. Se você fosse usar o índice em object_id, você precisaria executar as seguintes operações: digitalizar o índice para encontrar todos os correspondentes object_id; para cada correspondência, vá buscar a linha real; em seguida, classifique todas as linhas buscadas ide retorne aquela com o maior id.

    A alternativa escolhida pelo otimizador, presumivelmente baseada no object_idhistograma, é: varrer o índice de idtrás para frente, em sua totalidade; para cada valor, vá buscar a linha e verifique se o valor object_idcorresponde; retornar a primeira linha correspondente, que terá o idvalor máximo possível. Essa alternativa evita a classificação das linhas, então acho que o otimizador prefere usar o índice em object_id.

    A presença de um índice (object_id asc, id desc)permite ainda outra alternativa: digitalizar esse novo índice para a primeira entrada correspondente ao object_idvalor fornecido, que por definição terá o idvalor mais alto; vá buscar uma linha correspondente e retorne. Obviamente, esta é a abordagem mais eficiente.

    • 19
  2. Best Answer

    Existem dois métodos que encontrei para tornar isso mais rápido,

    • Adicione um índice melhor.
    • Otimize a consulta, com uma cerca de otimização.

    Índice

    Um método é adicionar um índice melhor, conforme encontrado na resposta de mustaccio . Isso tem a vantagem de resultar na consulta mais rápida.

    Cerca de Otimização

    Outro método é cercar a consulta, envolvendo-a em uma subseleção. Observe que a consulta interna NÃO tem o arquivo LIMIT. Esta solução pode ser muito lenta. Você pode ver que há 4239 linhas correspondentes object_id = 123. Isso significa que, embora você possa recuperar essas linhas quase instantaneamente (já que é uma varredura de índice e muito rápida), você AINDA precisa classificá-las posteriormente. A solução Mustaccio envolve classificá-los em um índice (tornando-o obviamente muito mais rápido).

    SELECT *
FROM (
     SELECT *
     FROM items 
     WHERE object_id = 123
     ORDER BY id DESC
) AS items
ORDER BY id DESC
LIMIT 1;

                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Limit  (cost=16629.84..16629.86 rows=1 width=59)
   ->  Sort  (cost=16629.84..16640.44 rows=4239 width=59)
         Sort Key: items.id
         ->  Bitmap Heap Scan on items  (cost=125.42..16374.45 rows=4239 width=59)
               Recheck Cond: (object_id = 123::bigint)
                   ->  Bitmap Index Scan on items_object_id_idx  (cost=0.00..124.36 rows=4239 width=0)
                     Index Cond: (object_id = 123::bigint)
    • 9

relate perguntas