Início / dba / Perguntas / 290514
Accepted
mlissner
Asked: 2021-04-27 15:50:55 +0800 CST

Por que o ASC é 100x mais rápido que o DESC e o que posso fazer a respeito?

  • 772

Estou prestes a entender isso, mas não entendo a correção que preciso. A consulta a seguir leva cerca de um minuto, mais ou menos, para retornar 20 registros:

explain (analyze, buffers)
select "search_docket"."id"
FROM "search_docket"
WHERE "search_docket"."court_id" = 'delctcompl'
ORDER BY "search_docket"."id" desC
LIMIT 20; 
                                                                           QUERY PLAN                                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..2791.05 rows=20 width=4) (actual time=74.950..41059.055 rows=20 loops=1)
   Buffers: shared hit=38524709 dirtied=10
   ->  Index Scan Backward using search_docket_pkey on search_docket  (cost=0.56..8837737.95 rows=63342 width=4) (actual time=74.947..41059.022 rows=20 loops=1)
         Filter: ((court_id)::text = 'delctcompl'::text)
         Rows Removed by Filter: 41862720
         Buffers: shared hit=38524709 dirtied=10
 Planning time: 0.450 ms
 Execution time: 41059.233 ms

Inverter a ordem de classificação leva cerca de 60ms:

explain (analyze, buffers)
select "search_docket"."id"
FROM "search_docket"
WHERE "search_docket"."court_id" = 'delctcompl'
ORDER BY "search_docket"."id" asC
LIMIT 20; 
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..2791.05 rows=20 width=4) (actual time=63.701..63.939 rows=20 loops=1)
   Buffers: shared hit=45783
   ->  Index Scan using search_docket_pkey on search_docket  (cost=0.56..8837737.95 rows=63342 width=4) (actual time=63.698..63.933 rows=20 loops=1)
         Filter: ((court_id)::text = 'delctcompl'::text)
         Rows Removed by Filter: 67080
         Buffers: shared hit=45783
 Planning time: 0.426 ms
 Execution time: 63.971 ms

Você pode ver que no primeiro, fica desagradável aqui:

         Rows Removed by Filter: 41862720
         Buffers: shared hit=38524709 dirtied=10

Aqui estão os índices na tabela (omitindo os que não estão relacionados a esta consulta):

Indexes:
    "search_docket_pkey" PRIMARY KEY, btree (id)
    "search_docket_7a46e69c" btree (court_id)
    "search_docket_court_id_2d2438b2594e74ba_like" btree (court_id varchar_pattern_ops)

Pensamentos dispersos:

  1. A coluna do tribunal tem cardinalidade muito baixa. Tem cerca de 500 valores em cerca de 50 milhões de linhas.

  2. Acho que poderia adicionar um índice descendente em court_id, e talvez isso consertasse, mas isso não parece certo.

  3. Talvez eu precise de um índice de várias colunas aqui search_docket.ide search_docket.court_id? Parece desligado.

Existe algo melhor que eu deveria estar fazendo aqui que faria o índice funcionar melhor?

postgresql performance

2 respostas

  1. Best Answer

    O ideal para essas consultas seria em index on (court_id, id)e com as colunas nessa ordem. Deve ser extremamente rápido em qualquer direção. E uma vez que você o tenha, você deve ser capaz de se livrar do índice simples em court_id, pois não seria mais muito bom.

    • 3

  2. As linhas que satisfazem a condição (estimada em cerca de 63.000) têm um id. O PostgreSQL não sabe disso, então considera a varredura de índice igualmente atraente em qualquer direção.

    Se você sabe que é sempre assim, use DESC. Caso contrário, e você deseja evitar a varredura de índice nesse índice, altere a ORDER BYcláusula para que ela não corresponda à expressão indexada, por exemplo, adicionando + 0.

    • 0

relate perguntas