Início / user-74005

g3rv4's questions

Martin Hope
g3rv4
Asked: 2024-02-15 19:15:00 +0800 CST
  • 10

Eu tenho uma tabela definida como

CREATE TABLE details_search (
    id int4 NOT NULL PRIMARY KEY,
    "search" tsvector NULL
);
CREATE INDEX details_search_idx ON details_search USING gin (search);

Executei isso para ter uma ideia do tamanho:

SELECT pg_size_pretty(pg_relation_size('details_search')) relation_size,
       pg_size_pretty(pg_total_relation_size('details_search')) total_relation_size,
       pg_size_pretty(pg_table_size('details_search')) table_size,
       pg_size_pretty(pg_indexes_size('details_search')) indexes_size;

e estes são os resultados

relation_size|total_relation_size|table_size|indexes_size|
-------------+-------------------+----------+------------+
800 MB       |64 GB              |57 GB     |6830 MB     |

Estou interessado em realizar apenas pesquisas de frases, e elas são usadas de forma agregada. Quando realizo pesquisas de frases com termos incomuns, tudo funciona bem. Agora quando uso uma frase que tem um termo comum, o desempenho sofre muito.

Esta consulta levou 192 segundos:

SELECT COUNT(id)
FROM   details_search
WHERE  search @@ phraseto_tsquery('simple', 'data management')

Aqui está o plano de consulta (e aqui o plano de consulta em uma interface agradável):

  Output: count(id)
  Buffers: shared hit=25942383 read=6354221 written=4588
  I/O Timings: shared/local read=512605.708 write=122.864
  ->  Gather  (cost=178176.43..178176.64 rows=2 width=8) (actual time=192857.512..192861.652 rows=3 loops=1)
        Output: (PARTIAL count(id))
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=25942383 read=6354221 written=4588
        I/O Timings: shared/local read=512605.708 write=122.864
        ->  Partial Aggregate  (cost=177176.43..177176.44 rows=1 width=8) (actual time=192852.434..192852.435 rows=1 loops=3)
              Output: PARTIAL count(id)
              Buffers: shared hit=25942383 read=6354221 written=4588
              I/O Timings: shared/local read=512605.708 write=122.864
              Worker 0:  actual time=192851.530..192851.531 rows=1 loops=1
                Buffers: shared hit=8650807 read=2115877 written=1469
                I/O Timings: shared/local read=170775.853 write=38.985
              Worker 1:  actual time=192848.579..192848.581 rows=1 loops=1
                Buffers: shared hit=8623424 read=2115864 written=1551
                I/O Timings: shared/local read=170720.335 write=41.527
              ->  Parallel Bitmap Heap Scan on details_search  (cost=33664.19..173376.94 rows=1519795 width=4) (actual time=1231.216..192758.374 rows=121050 loops=3)
                    Output: id, search
                    Recheck Cond: (search @@ '''data'' <-> ''management'''::tsquery)
                    Rows Removed by Index Recheck: 2268868
                    Heap Blocks: exact=12114 lossy=22061
                    Buffers: shared hit=25942383 read=6354221 written=4588
                    I/O Timings: shared/local read=512605.708 write=122.864
                    Worker 0:  actual time=1230.572..192759.521 rows=121482 loops=1
                      Buffers: shared hit=8650807 read=2115877 written=1469
                      I/O Timings: shared/local read=170775.853 write=38.985
                    Worker 1:  actual time=1227.317..192754.854 rows=120483 loops=1
                      Buffers: shared hit=8623424 read=2115864 written=1551
                      I/O Timings: shared/local read=170720.335 write=41.527
                    ->  Bitmap Index Scan on job_posts_details_search_idx  (cost=0.00..32752.32 rows=3647509 width=0) (actual time=1226.674..1226.675 rows=3956386 loops=1)
                          Index Cond: (search @@ '''data'' <-> ''management'''::tsquery)
                          Buffers: shared hit=832 read=2242
                          I/O Timings: shared/local read=424.365
Settings: effective_cache_size = '13153520kB', search_path = 'public, public, "$user"'
Query Identifier: 1461135140272243366
Planning:
  Buffers: shared hit=194
Planning Time: 7.346 ms
Execution Time: 192861.763 ms

A maior parte do tempo foi para a leitura na varredura paralela de heap de bitmap. Também foi uma leitura bastante lenta, de 97 MB/s considerando que possui SSDs (e possui um SSD exclusivamente para cache de dados ). Isso não melhora se eu pg_prewarmcarregar a tabela antes da consulta.

Vejo que sim Recheck Cond: (search @@ '''data'' <-> ''management'''::tsquery), então acho que está extraindo todos os dados do trabalho do disco para verificar a condição na searchcoluna real, como se apenas verificar o índice não fosse suficiente para validar se há uma correspondência de frase. Isso explicaria por que esse problema ocorre apenas em termos comuns.

O que eu poderia fazer para otimizar essas pesquisas de frases? Ficarei feliz em considerar limitações de possíveis coisas a serem pesquisadas (como "fazer apenas consultas de até 3 palavras") ou alterações nas configurações do servidor (para acelerar essas leituras incômodas), se isso puder trazer consistência ao tempo de consulta.

postgresql