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.