Posso ativar o PITR depois que o banco de dados foi usado

Question

marylandmusclemachine

Asked: 2024-03-18 00:48:06 +0800 CST2024-03-18 00:48:06 +0800 CST 2024-03-18 00:48:06 +0800 CST

Consulta lenta ao usar a cláusula OR

772

Eu tenho 4 tabelas e leva muito tempo para consultar com a cláusula OR (a cláusula AND funciona bem).

Novos artigos

Linhas: ~7000

Esquema:

id               | integer
publication_date | timestamp with time zone
metadata         | jsonb
text             | text
title            | character varying(2048)
is_spam          | boolean

Índices:

    "news_pkey" PRIMARY KEY, btree (id)
    "news_text_gin" gin (text gin_trgm_ops) WITH (fastupdate=off)
    "news_publication_date_6dfb01cd" btree (publication_date)
    "news_title_aa02bdd6" btree (title)
    "news_title_aa02bdd6_like" btree (title varchar_pattern_ops)

Usuário M2M para notícias

Linhas: ~200

Esquema:

id        | integer
hidden    | boolean
seen      | boolean
news_id   | integer
user_id   | integer

Índices:

    "usernews_pkey" PRIMARY KEY, btree (id)
    "usernews_news_id_8451f0f6" btree (news_id)
    "usernews_user_id_cf9591f3" btree (user_id)
    "unique_user_news_constraint" UNIQUE CONSTRAINT, btree (user_id, news_id)

Notícias M2M para tags

Linhas: ~15.000

Esquema:

id        | integer
news_id   | integer
tag_id    | integer

Índices:

    "newstag_pkey" PRIMARY KEY, btree (id)
    "newstag_news_id_331e55c0" btree (news_id)
    "newstag_tag_id_88f2fc8b" btree (tag_id)

Tag

Linhas: ~800

Esquema:

id           | integer
name         | text

Índices:

    "system_tag_pkey" PRIMARY KEY, btree (id)
    "system_tag_name_0ef0fc9a_like" btree (name text_pattern_ops)
    "system_tag_name_0ef0fc9a_uniq" UNIQUE CONSTRAINT, btree (name)

Minha consulta é:

SELECT 
    "news"."id",
    "news"."metadata", 
    "news"."title",
    "news"."text",
    "news"."publication_date", 
    COALESCE("usernews"."seen", false) AS "seen" 
FROM "news" 
    LEFT OUTER JOIN "newstag" ON ("news"."id" = "newstag"."news_id") 
    LEFT OUTER JOIN "system_tag" ON ("newstag"."tag_id" = "system_tag"."id")
    LEFT OUTER JOIN "usernews" ON ("news"."id" = "usernews"."news_id") 
WHERE 
    (
    NOT "news"."is_spam" AND ("news"."metadata" -> 'appeals_journal') = 'true' 
    AND (UPPER("news"."text"::text) LIKE UPPER('%something%') OR UPPER("system_tag"."name"::text) LIKE UPPER('%something%'))
    AND ("news"."publication_date" AT TIME ZONE 'Europe/London')::date BETWEEN '2022-12-02'::date AND '2025-01-10'::date
    ) 
ORDER BY "news"."publication_date" DESC

O que cria esta análise explicativa:

 Sort  (cost=1389.70..1389.71 rows=1 width=1031) (actual time=2478.054..2478.281 rows=1597 loops=1)
   Sort Key: news.publication_date DESC
   Sort Method: quicksort  Memory: 2351kB
   ->  Nested Loop Left Join  (cost=0.56..1389.69 rows=1 width=1031) (actual time=21.685..2471.670 rows=1597 loops=1)
         Join Filter: (news.id = usernews.news_id)
         Rows Removed by Join Filter: 343317
         ->  Nested Loop Left Join  (cost=0.56..1382.48 rows=1 width=1030) (actual time=21.573..2390.879 rows=1597 loops=1)
               Filter: ((upper(news.text) ~~ '%SOMETHING%'::text) OR (upper(system_tag.name) ~~ '%SOMETHING%'::text))
               Rows Removed by Filter: 13770
               ->  Seq Scan on news  (cost=0.00..1373.53 rows=1 width=1030) (actual time=0.032..40.981 rows=7053 loops=1)
                     Filter: ((NOT is_spam) AND ((metadata -> 'appeals_journal'::text) = 'true'::jsonb) AND ((timezone('Europe/London'::text, publication_date))::date >= '2022-12-02'::date) AND ((timezone('Europe/London'::text, publication_date))::date <= '2025-01-10'::date))
                     Rows Removed by Filter: 14
               ->  Nested Loop Left Join  (cost=0.56..8.91 rows=2 width=26) (actual time=0.008..0.017 rows=2 loops=7053)
                     ->  Index Scan using newstag_news_id_331e55c0 on newstag  (cost=0.29..8.32 rows=2 width=8) (actual time=0.005..0.007 rows=2 loops=7053)
                           Index Cond: (news_id = news.id)
                     ->  Index Scan using system_tag_pkey on system_tag  (cost=0.28..0.30 rows=1 width=26) (actual time=0.003..0.003 rows=1 loops=15329)
                           Index Cond: (id = newstag.tag_id)
         ->  Seq Scan on usernews  (cost=0.00..4.87 rows=187 width=5) (actual time=0.006..0.026 rows=215 loops=1597)
 Planning Time: 0.830 ms
 Execution Time: 2478.637 ms

Eu tentei com AND clase e obtive:

 Sort  (cost=159.27..159.27 rows=1 width=1031) (actual time=127.686..127.725 rows=421 loops=1)
   Sort Key: news.publication_date DESC
   Sort Method: quicksort  Memory: 697kB
   ->  Hash Right Join  (cost=153.68..159.26 rows=1 width=1031) (actual time=126.488..126.824 rows=421 loops=1)
         Hash Cond: (news.news_id = news.id)
         ->  Seq Scan on usernews  (cost=0.00..4.87 rows=187 width=5) (actual time=0.078..0.113 rows=215 loops=1)
         ->  Hash  (cost=153.67..153.67 rows=1 width=1030) (actual time=126.393..126.395 rows=421 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 504kB
               ->  Nested Loop  (cost=20.99..153.67 rows=1 width=1030) (actual time=2.431..125.366 rows=421 loops=1)
                     ->  Nested Loop  (cost=20.70..121.97 rows=19 width=4) (actual time=1.803..3.367 rows=727 loops=1)
                           ->  Seq Scan on system_tag  (cost=0.00..21.98 rows=1 width=4) (actual time=0.098..1.559 rows=2 loops=1)
                                 Filter: (upper(name) ~~ '%SOMETHING%'::text)
                                 Rows Removed by Filter: 817
                           ->  Bitmap Heap Scan on newstag  (cost=20.70..99.45 rows=54 width=8) (actual time=0.131..0.733 rows=364 loops=2)
                                 Recheck Cond: (tag_id = system_tag.id)
                                 Heap Blocks: exact=84
                                 ->  Bitmap Index Scan on newstag_tag_id_88f2fc8b  (cost=0.00..20.69 rows=54 width=0) (actual time=0.119..0.119 rows=364 loops=2)
                                       Index Cond: (tag_id = system_tag.id)
                     ->  Index Scan using news_pkey on news  (cost=0.28..1.60 rows=1 width=1030) (actual time=0.165..0.165 rows=1 loops=727)
                           Index Cond: (id = newstag.news_id)
                           Filter: ((NOT is_spam) AND (upper(text) ~~ '%SOMETHING%'::text) AND ((metadata -> 'appeals_journal'::text) = 'true'::jsonb) AND ((timezone('Europe/London'::text, publication_date))::date >= '2022-12-02'::date) AND ((timezone('Europe/London'::text, publication_date))::date <= '2025-01-10'::date))
                           Rows Removed by Filter: 0
 Planning Time: 1.463 ms
 Execution Time: 127.852 ms

Como posso acelerar minha consulta com a instrução OR? As únicas coisas que consigo pensar são em usar UNION (que é bastante rápido) ou visualizações materializadas com índices (o que não acho uma boa ideia, porque minhas tabelas obtêm dados constantemente do analisador).

PS: Eu uso PostgreSQL 12.6.

1 respostas

Voted

Erwin Brandstetter · Answer 1 · 2024-03-18T13:06:26+08:00

Muitos problemas. Difícil culpar um único.

Existe um índice trigrama GIN em news.text, mas sua consulta não pode usá-lo, porque textestá oculto atrás de uma expressão não sargável. A tabela tem apenas 7.000 linhas e, embora a definição da tabela sugira linhas largas, voltar para a varredura seq ainda não custa muito, conforme indicado pelo plano de consulta. Ainda assim, perda inútil.

Há um text_pattern_opsíndice de árvore B ativado, system_tag.namemas sua consulta não pode usá-lo porque, para começar, esse índice só ajuda com padrões ancorados à esquerda. Além disso, outra expressão não sargável. Ruim mesmo sem índice. Ainda não deveria importar muito por apenas 800 linhas. Mas no loop aninhado ele acaba sendo um grande consumidor de desempenho. (Não sei exatamente por quê.) Veja:

Variações de desempenho de consulta LIKE do PostgreSQL

Um índice trigrama funciona para operadores que não diferenciam maiúsculas de minúsculas ILIKEou ~*também, você sabe disso, certo?

Depois, há duas (incorretas) LEFT JOINSpara duas tabelas no que parece ser um relacionamento n:m. A condição na WHEREcláusula faz com que ambos atuem como INNER JOIN. A construção pode multiplicar linhas (provavelmente não conforme pretendido). Para começar, você não usa nenhuma coluna dessas relações. Você está dificultando o planejador de consultas. Em vez disso, use uma EXISTSexpressão.

Por que lançar textcolunas para ::text?

Não se aplique AT TIME ZONEà coluna da tabela (para cada linha!). Aplique-o aos critérios de filtro constantes (uma vez)! Esta forma sargável também pode usar um índice (provavelmente não neste caso específico).

Considere esta reescrita:

SELECT n.id, n.metadata, n.title, n.text, n.publication_date
     , COALESCE(un.seen, false) AS seen 
FROM   news n
LEFT   JOIN usernews un ON un.news_id = n.id
WHERE  NOT n.is_spam
AND   (n.metadata -> 'appeals_journal') = 'true' -- still terrible
AND    n.publication_date >= timestamp '2022-12-02' AT TIME ZONE 'Europe/London'  -- better
AND    n.publication_date <  timestamp '2025-01-11' AT TIME ZONE 'Europe/London'
AND   (n.text ~* 'something'  -- much better
    OR EXISTS (
          SELECT FROM newstag nt JOIN system_tag st ON nt.tag_id = st.id
          WHERE  nt.news_id =  n.id
          AND    st.name ~* 'something'
          )
      )   
ORDER  BY n.publication_date DESC;

Audite seus índices também, conforme discutido.

Atualize sua versão desatualizada do Postgres. A iteração atual do Postgres 12 é 12.18 , não 12.6. O ideal é atualizar para o Postgres 16 atual.

Faça tudo isso e você já verá um plano de consulta muito melhorado.

De qualquer forma, isso deixa a questão central : duas condições (caras?) em tabelas distintas são combinadas com uma condição "feia OR" . Você pode ou não conseguir reescrever com UNIONou UNION ALL. Nem é 100% equivalente! Talvez compre muito, se possível. Ver:

Por que uma instrução OR é mais lenta que UNION?

Consulta lenta ao usar a cláusula OR

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Consulta lenta ao usar a cláusula OR

1 respostas

relate perguntas