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:
- 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? - 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 estejaFilterindexada. É 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?
Parece que as colunas criada_on e atualizada_on estão altamente correlacionadas entre si. Mas o PostgreSQL não possui nenhum mecanismo para saber disso. Supõe implicitamente que eles não estão correlacionados. Não há nada que você possa fazer sobre essa suposição em qualquer versão lançada ou em desenvolvimento do PostgreSQL.
Ele pressupõe que será necessário filtrar cerca de 25/102.293 dos 5 milhões de linhas, ou cerca de 1.200 delas, antes de interromper a varredura do índice. Mas como toda a parte inicial da varredura do índice é descartada (com grande custo) pela condição de filtrocreated_on, ela realmente precisa filtrar 4.859.940 linhas antes de encontrar as 25 para manter. Portanto, a estimativa está errada por um fator de cerca de 4.000.
Se suas colunas seguirem a semântica intuitiva implícita em seus nomes, uma linha não poderá ser atualizada antes de ser criada, portanto, a condição criada_on >= '2023-10-08 00:00:00+08' também implica uma atualização_on >= '2023- 10-08 00:00:00+08'. Se você fornecer manualmente essa condição inferida, a varredura pulará toda a parte inicial do índice e se tornará muito rápida em minhas mãos. O planejador não fornecerá essa inferência para você, nem mesmo se você tiver uma restrição CHECK que teoricamente permitiria isso, mas talvez você possa alterar seu aplicativo para gerar automaticamente essa inferência para você.
Com base no fato de que "Linhas removidas por filtro" é quase igual a "Buffers: hit compartilhado" em seu primeiro plano, é evidente que a ordem física das linhas na tabela não está fortemente correlacionada com as colunas de carimbo de data/hora. Então, seguindo a tabela na ordem do índice, ela tem que pular por toda a tabela. Mesmo que seus shared_buffers sejam grandes o suficiente para que tudo fique na memória, isso ainda é bastante lento. Parte disso é provavelmente que desafixar e repinar buffers é uma operação cara, e parte é provavelmente que fazer isso dessa maneira destrói o cache da CPU, e a memória principal é muito mais lenta que o cache da CPU. Validando isso, se eu CLUSTER a tabela usando job_updated_on_name_compound_asc_idx, a consulta fica cerca de 10x mais rápida em minhas mãos. Alternativamente, se eu apenas adicionar create_on ao índice para torná-lo
(updated_on, upper(name), created_on)então ele consegue filtrar os valores criados_on apenas usando o índice sem ter que visitar a tabela, e isso também torna tudo muito mais rápido. Esta última talvez seja a melhor opção, pois o índice se manterá sozinho e poderá ser criado simultaneamente com outras operações, nenhuma das quais se aplica ao CLUSTER.Observe que esse truque parou de funcionar na v13, onde a classificação incremental foi adicionada. Nesse ponto, ele usará alegremente o índice para ordenação primária e, em seguida, usará uma classificação incremental para reordenar apenas as ligações para obter a ordem geral. Se você quiser forçar manualmente o índice a não ser usado, uma abordagem mais segura é fazer com que a primeira coluna do ORDER BY seja uma expressão fictícia que não corresponda ao índice:
Alguma versão futura do PostgreSQL pode se tornar inteligente o suficiente para ver esse truque e ainda usar o índice "errado", mas nenhuma das versões atuais ou em desenvolvimento ainda existe.
Para responder diretamente à sua segunda pergunta, não, ele não combinará índices dessa forma, um para filtrar e outro para ordenar. O código usado para combinar índices é o código bitmap, e isso perde qualquer ordem. Deveria ser possível adicionar um tipo de nó que faça uma varredura de índice regular (que mantém a ordem), mas anexar a ele um bitmap preenchido usado para filtragem. Acho que isso exigiria apenas programação (ou seja, nenhuma alteração na representação dos dados no disco), mas ainda daria muito trabalho e ninguém fez isso. Pensei nisso algumas vezes, mas não fiz nenhuma tentativa concreta. Também seria um tipo de nó bastante inovador e suspeito que seria difícil aceitá-lo na base de código por esse motivo. Também, no seu caso, provavelmente não seria mais eficaz do que já seria apenas adicionar a coluna "filtragem" como a última coluna na definição do índice de ordenação existente. (Acho que o uso real desse código seria quando o bitmap anexado fosse o resultado da combinação de vários índices, o que não parece ser o seu caso)