Slow query when using OR clause

Question

I have 4 tables and it takes quite a long time to query with OR clause (AND clause works fine).

1. News articles

Rows: ~7000

Schema:

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

Indexes:

    "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)

2. M2M User to News

Rows: ~200

Schema:

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

Indexes:

    "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)

3. M2M News to Tags

Rows: ~15000

Schema:

id        | integer
news_id   | integer
tag_id    | integer

Indexes:

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

4. Tags

Rows: ~800

Schema:

id           | integer
name         | text

Indexes:

    "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)

My query is:

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

Which creates this explain analyze:

 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

I tried it with AND clase and got:

 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

How do i speed up my query with OR statement? The only things i can think of are using UNION (which is quite fast) or materialized views with indexes (which i don't think is a good idea, because my tables constantly get data from parser).

P.S: I use PostgreSQL 12.6.

Answer 1

Many issues. Hard to blame a single one.

There is a trigram GIN index on news.text, but your query cannot use it, because text is hidden behind a non-sargable expression. The table has only 7000 rows, and though the table definition suggests wide rows, falling back to seq scan still doesn't cost much, as indicated by the query plan. Still, pointless loss.

There is a text_pattern_ops B-tree index on system_tag.name but your query cannot use it because that index only helps with left-anchored patterns to begin with. Plus, another non-sargable expression. Bad even without index. Still shouldn't matter much for just 800 rows. But in the nested loop it turns out to be a major performance hog. (Not exactly sure why.) See:

• PostgreSQL LIKE query performance variations

A trigram index works for case-insensitive operators ILIKE or ~*, too, you know that right?

Then there are two (incorrect) LEFT JOINS to two tables in what seems to be an n:m relationship. The condition in the WHERE clause makes both act as INNER JOIN. The construct might multiply rows (probably not as intended). You don't use any columns of those relations to begin with. You are giving the query planner a hard time. Use an EXISTS expression instead.

Why cast text columns to ::text?

Don't apply AT TIME ZONE to the table column (for every row!). Apply it to the constant filter criteria (once)! This sargable form may also be able to use an index (probably not in this particular case).

Consider this rewrite:

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;

Audit your indexes, too, as discussed.

Upgrade your outdated version of Postgres. The current iteration of Postgres 12 is 12.18, not 12.6. Ideally, upgrade to the current Postgres 16.

Do all this and you should see a much improved query plan already.

Either way, this leaves the core issue: two (expensive?) conditions on distinct tables are combined with an "ugly OR". You may or may not be able to rewrite with UNION or UNION ALL. Neither is 100 % equivalent! Might buy a lot if possible. See:

• Why is an OR statement slower than UNION?