Início / user-280732

richie's questions

Martin Hope
richie
Asked: 2023-10-25 00:17:39 +0800 CST
  • 6

Estou executando o PostgreSQL 11 shared_buffersconfigurado para 3 GB no meu Mac. Eu tenho uma tabela jobcom 5 milhões de linhas. A estrutura da tabela é

                           Table "public.job"
   Column   |           Type           | Collation | Nullable | Default
------------+--------------------------+-----------+----------+---------
 id         | uuid                     |           | not null |
 name       | text                     |           |          |
 created_on | timestamp with time zone |           |          |
 updated_on | timestamp with time zone |           |          |
Indexes:
    "job_pkey" PRIMARY KEY, btree (id)
    "job_created_on_idx" btree (created_on)
    "job_name_idx" btree (name)
    "job_updated_on_idx" btree (updated_on)
    "job_updated_on_name_compound_asc_idx" btree (updated_on, upper(name))
    "job_updated_on_name_compound_desc_idx" btree (updated_on DESC, upper(name))

Observe que criei um índice composto nas colunas updated_one name.

Quando executo query select name, created_on from job where created_on >= '2023-10-08 00:00:00+08'::timestamp with time zone AND created_on < '2023-10-16 00:00:00+08' ORDER BY updated_on ASC, UPPER(name::text) ASC limit 25, o PostgreSQL usa o índice composto job_updated_on_name_compound_asc_idxe leva mais de 4 segundos.

Plano de execução

Limit  (cost=0.43..102.29 rows=25 width=61) (actual time=4549.668..4550.235 rows=25 loops=1)
   Buffers: shared hit=4859940
   ->  Index Scan using job_updated_on_name_compound_asc_idx on job  (cost=0.43..416764.16 rows=102293 width=61) (actual time=4549.667..4550.230 rows=25 loops=1)
         Filter: ((created_on >= '2023-10-08 00:00:00+08'::timestamp with time zone) AND (created_on < '2023-10-16 00:00:00+08'::timestamp with time zone))
         Rows Removed by Filter: 4828894
         Buffers: shared hit=4859940
 Planning Time: 0.218 ms
 Execution Time: 4550.260 ms

Há um índice na created_oncoluna, mas não é usado. Posso forçar o PostgreSQL a usar o índice da created_oncoluna anexando idà cláusula order by . A consulta é select name, created_on from job where created_on >= '2023-10-08 00:00:00+08'::timestamp with time zone AND created_on < '2023-10-16 00:00:00+08' ORDER BY updated_on ASC, UPPER(name::text) ASC, id limit 25;. Desta vez, o PostgreSQL usa o índice da created_oncoluna e retorna o resultado muito rápido.

Plano de execução

Limit  (cost=52190.61..52193.52 rows=25 width=77) (actual time=125.192..138.055 rows=25 loops=1)
   Buffers: shared hit=42788
   ->  Gather Merge  (cost=52190.61..62136.44 rows=85244 width=77) (actual time=125.191..138.049 rows=25 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=42788
         ->  Sort  (cost=51190.58..51297.14 rows=42622 width=77) (actual time=119.359..119.362 rows=20 loops=3)
               Sort Key: updated_on, (upper(name)), id
               Sort Method: top-N heapsort  Memory: 30kB
               Worker 0:  Sort Method: top-N heapsort  Memory: 31kB
               Worker 1:  Sort Method: top-N heapsort  Memory: 31kB
               Buffers: shared hit=42788
               ->  Parallel Bitmap Heap Scan on job  (cost=2512.94..49987.82 rows=42622 width=77) (actual time=19.915..109.984 rows=36562 loops=3)
                     Recheck Cond: ((created_on >= '2023-10-08 00:00:00+08'::timestamp with time zone) AND (created_on < '2023-10-16 00:00:00+08'::timestamp with time zone))
                     Heap Blocks: exact=24557
                     Buffers: shared hit=42738
                     ->  Bitmap Index Scan on job_created_on_idx  (cost=0.00..2487.36 rows=102293 width=0) (actual time=16.909..16.909 rows=109685 loops=1)
                           Index Cond: ((created_on >= '2023-10-08 00:00:00+08'::timestamp with time zone) AND (created_on < '2023-10-16 00:00:00+08'::timestamp with time zone))
                           Buffers: shared hit=395
 Planning Time: 0.168 ms
 Execution Time: 138.115 ms

A diferença no tempo de execução torna-se maior se o banco de dados estiver ocupado atualizando uma grande coluna de linhas.

O índice composto foi criado para melhorar o desempenho da classificação e é muito útil em alguns casos. Como meu sistema gera o SQL dinamicamente com base na seleção do usuário, a condição e a classificação da consulta podem variar. Neste caso específico, adicionar idà cláusula order by para evitar o uso de um índice composto pode melhorar o desempenho, mas talvez em alguns outros casos, usar o índice composto seja melhor, então não posso simplesmente remover o índice composto.

Também verifiquei a tabela pg_stats e aqui está o resultado:

  attname   | inherited | n_distinct | most_common_vals
------------+-----------+------------+------------------
 id         | f         |         -1 |
 name       | f         |         -1 |
 created_on | f         |  -0.908167 |
 updated_on | f         |         -1 |

Eu tenho duas perguntas:

  1. Para a consulta acima, obviamente é melhor usar o índice created_on. Por que o PostgreSQL escolhe o índice composto da cláusula order by ? Existe algo que eu possa configurar no PostgreSQL para permitir que ele use o índice correto?
  2. Parece que o PostgreSQL não usará índices de colunas na condição de consulta e ordenará por . Está Filtersob o índice composto, embora a coluna usada esteja Filterindexada. É possível que o PostgreSQL use o índice composto para ordenar por e o índice para a coluna de condição de consulta juntos em uma única consulta?
postgresql