Início / dba / Perguntas / 258503
Accepted
Arun
Asked: 2020-01-31 00:26:39 +0800 CST

Postgres 10 - A consulta fica mais lenta com a ordem por

  • 772

Estou executando uma consulta como

select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d';

na mesa que parece 

                Table "public.students"
          Column       |            Type             |             Modifiers              
    -------------------+-----------------------------+------------------------------------
     id                | uuid                        | not null default gen_random_uuid()
     school_id        | uuid                        | 
Indexes:
    "students_pkey" PRIMARY KEY, btree (id)
    "students_school_id_idx" btree (school_id)

O plano de consulta para a instrução select com exatamente onde se parece abaixo-

explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d';
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on students  (cost=581.83..83357.10 rows=24954 width=16)
   Recheck Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)
   ->  Bitmap Index Scan on students_school_id_idx  (cost=0.00..575.59 rows=24954 width=0)
         Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)

Isso é bastante rápido.

Agora adicionamos order by à query com id que degrada a query.(Tal query é gerada pelo Rails como student.first com alguma condição)

explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..488.51 rows=1 width=16)
   ->  Index Scan using students_pkey on students  (cost=0.43..12179370.22 rows=24954 width=16)
         Filter: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)

Como posso melhorar a velocidade para retornar os resultados desta consulta? Atualmente existem cerca de 4990731 registros na tabela e está demorando mais de 2 minutos! Sua execução no RDS com instância db.t2.medium.

ATUALIZAÇÃO Depois de executarAnalyze students;

explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;
                                                       QUERY PLAN                                                    
    -----------------------------------------------------------------------------------------------------------------
     Limit  (cost=8.46..8.46 rows=1 width=16)
       ->  Sort  (cost=8.46..8.46 rows=1 width=16)
             Sort Key: id
             ->  Index Scan using students_school_id_idx on students  (cost=0.43..8.45 rows=1 width=16)
                   Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)

    explain analyze select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;
                                                                          QUERY PLAN                                                                         
    -----------------------------------------------------------------------------------------------------------------------------------------------------------
    Limit  (cost=8.46..8.46 rows=1 width=16) (actual time=1.853..1.855 rows=1 loops=1)
     ->  Sort  (cost=8.46..8.46 rows=1 width=16) (actual time=1.851..1.852 rows=1 loops=1)
           Sort Key: id
           Sort Method: quicksort  Memory: 25kB
           ->  Index Scan using students_school_id_idx on students  (cost=0.43..8.45 rows=1 width=16) (actual time=1.841..1.843 rows=1 loops=1)
                 Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)
    Planning time: 0.145 ms
    Execution time: 1.874 ms
postgresql postgresql-performance

2 respostas

  1. Best Answer

    O PostgreSQL pensa que será mais rápido evitar a ordenação ORDER BYpelo varrendo as linhas na ordem de classificação e descartando as linhas até encontrar uma com a direita school_id.

    Pode haver duas razões pelas quais isso leva mais tempo do que o esperado:

    1. As estatísticas da tabela estão desativadas e o PostgreSQL superestima o número de linhas com esse school_id.

      Calcule novas estatísticas, possivelmente com um valor maior para default_statistics_target, para verificar se esse é o problema:

      ANALYZE students;

    2. As (muitas) linhas com o correto school_idtêm um id, então o PostgreSQL tem que varrer muito mais linhas do que esperava até encontrar uma correspondência.

      Nesse caso, você deve modificar a ORDER BYcláusula para que o PostgreSQL não possa usar o índice errado:

      ... ORDER BY id + 0
    • 2

  2. UUIDas colunas são ruins para o desempenho, pois geralmente não são ordenadas por definição. Sua coluna nomeada idé do tipo UUIDe, portanto, está sujeita a não ser ordenada.

    Quando você apenas executa o simples select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d';, o Query Engine apenas precisa percorrer os dados (HEAP) em sua tabela e desconsiderar os dados que não correspondem à cláusula WHERE ().

    No segundo caso, você está fazendo duas coisas.

    1. Selecionar os dados por meio do índice students_pkeyque produz um conjunto de resultados ordenado, mas que, em última análise, está ziguezagueando pelo heap. Esta é a Index Scan using students_pkey on students (cost=0.43..12179370.22 rows=24954 width=16)parte doEXPLAIN
    2. Filtrando os primeiros resultados com base no students_school_id_idxíndice. Esta é a Filter: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)parte doEXPLAIN

    Você pode querer considerar não usar UUIDs, pois eles vêm com alguma sobrecarga. Leia o artigo Geradores de UUID Sequenciais para obter mais informações.

    Mas também existem desvantagens - eles podem tornar os padrões de acesso muito mais aleatórios em comparação com os identificadores sequenciais tradicionais, causar amplificação de gravação WAL etc. .

    ...e...

    ( ênfase minha)

    Vamos supor que estamos inserindo linhas em uma tabela com uma chave primária UUID (portanto, há um índice exclusivo) e os UUIDs são gerados como valores aleatórios. Na tabela, as linhas podem ser simplesmente anexadas no final, o que é muito barato. Mas e o índice? Como a ordenação dos índices é importante, o banco de dados tem pouca escolha sobre onde inserir o novo item – ele precisa ir para um local específico no índice. Como os valores UUID são gerados de forma aleatória, a localização será aleatória, com distribuição uniforme para todas as páginas de índice.

    ...Porque...

    ( ênfase minha)

    Isso é lamentável, pois funciona contra algoritmos de gerenciamento de cache adaptável – não há um conjunto de páginas acessadas com “frequência” que possamos manter na memória. Se o índice for maior que a memória, a taxa de acertos do cache (tanto para o cache de página quanto para os buffers compartilhados) está fadada a ser ruim. E para índices pequenos, você provavelmente não se importa muito.

    A distribuição dos dados na tabela é sequencial, mas os UUIDs não serão ordenados. Em algum momento, o índice b-tree precisa acessar os dados e, como o índice está sendo usado para ORDER BYrecuperar os dados por meio do índice, os dados reais serão recuperados em um padrão de zig-zag.

    Existem soluções alternativas para esse problema, mas elas envolvem geração de UUID diferente ou o uso de índices clusterizados que têm impacto no desempenho das inserções, pois os dados estão sendo reordenados constantemente.

    Uma boa explicação para o índice B-Tree no PostgreSQL pode ser encontrada aqui

    Basicamente, o que está acontecendo no último nível folha do índice é o seguinte:

    LEAF(n)     76a8c180-3a76-492e-b68a-9d980bb50c11 | fec0b6c3-2112-487c-b10f-c515e1a7d1d1
                                               \    /
                                                \  /
                                                 \/ 
                                                 /\
                                                /  \    
                                               /    \
TABLE DATA  fec0b6c3-2112-487c-b10f-c515e1a7d1d1 | 76a8c180-3a76-492e-b68a-9d980bb50c11

    O índice é ordenado. Os dados não. É por isso que o ODER BY pode induzir uma sobrecarga devido à recuperação em zig-zag dos dados reais.

    • 2

relate perguntas