I have three queries that have have been running for over 1 hour at 100% CPU. I identified them with: sudo -u postgres psql -c "SELECT (now() - xact_start) as t, pid, query FROM pg_stat_activity WHERE state IN ('idle in transaction', 'active') order by t desc;"; And got the output below. I rebuilt earlier today. It’s not an urgent problem as we have a 12 core server and this keeps 1 core occupied full time, worth looking in to though. t | pid | query -----------------+-------+-------------------------------------------------------------------------------------------------------------------------------------- 01:02:34.648308 | 1455 | SELECT + | | posts.id + | | FROM posts + | | LEFT JOIN post_search_data pd + | | ON pd.locale = 'en' + | | AND pd.version = 2 + | | AND pd.post_id = posts.id + | | LEFT JOIN topics ON topics.id = posts.topic_id + | | WHERE pd.post_id IS NULL + | | AND topics.id IS NOT NULL + | | AND topics.deleted_at IS NULL + | | AND posts.raw != '' + | | ORDER BY posts.id DESC + | | LIMIT 20000 + | | 01:02:34.648308 | 1461 | SELECT + | | posts.id + | | FROM posts + | | LEFT JOIN post_search_data pd + | | ON pd.locale = 'en' + | | AND pd.version = 2 + | | AND pd.post_id = posts.id + | | LEFT JOIN topics ON topics.id = posts.topic_id + | | WHERE pd.post_id IS NULL + | | AND topics.id IS NOT NULL + | | AND topics.deleted_at IS NULL + | | AND posts.raw != '' + | | ORDER BY posts.id DESC + | | LIMIT 20000 + | | 01:02:34.648308 | 1462 | SELECT + | | posts.id + | | FROM posts + | | LEFT JOIN post_search_data pd + | | ON pd.locale = 'en' + | | AND pd.version = 2 + | | AND pd.post_id = posts.id + | | LEFT JOIN topics ON topics.id = posts.topic_id + | | WHERE pd.post_id IS NULL + | | AND topics.id IS NOT NULL + | | AND topics.deleted_at IS NULL + | | AND posts.raw != '' + | | ORDER BY posts.id DESC + | | LIMIT 20000

The problem is slowly getting worse - the original queries are still running and almost exactly 2 hours later, 3 more started. They’re now occupying 2/12 of my cores. If this continues overnight it will turn into an actual problem. Update: this query was introduced by this recent commit. Perhaps it’s missing an index on some tables? I have 4.1M records in the posts and post_search_data tables and 1.5M in the topics table, so the double LEFT JOIN could be a problem if something is not indexed correctly. Not sure if this helps, but here’s an EXPLAIN of the query on my DB: sudo -u postgres psql discourse -c "EXPLAIN SELECT posts.id FROM posts LEFT JOIN post_search_data pd ON pd.locale='en' AND pd.version=2 AND pd.post_id=posts.id LEFT JOIN topics ON topics.id=posts.topic_id WHERE pd.post_id IS NULL AND topics.id IS NOT NULL AND topics.deleted_at IS NULL AND posts.raw!='' ORDER BY posts.id DESC LIMIT 20000;"; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Limit (cost=2000.88..124179.27 rows=20000 width=4) -> Nested Loop Anti Join (cost=2000.88..29146177.32 rows=4770758 width=4) Join Filter: (pd.post_id = posts.id) -> Gather Merge (cost=1000.88..21351547.22 rows=4770860 width=4) Workers Planned: 2 -> Nested Loop (cost=0.86..20799871.57 rows=1987858 width=4) -> Parallel Index Scan Backward using posts_pkey on posts (cost=0.43..12078793.28 rows=1988522 width=8) Filter: (raw <> ''::text) -> Index Only Scan using index_topics_on_id_and_deleted_at on topics (cost=0.43..4.39 rows=1 width=4) Index Cond: ((id = posts.topic_id) AND (id IS NOT NULL) AND (deleted_at IS NULL)) -> Materialize (cost=1000.00..495214.55 rows=102 width=4) -> Gather (cost=1000.00..495214.04 rows=102 width=4) Workers Planned: 2 -> Parallel Seq Scan on post_search_data pd (cost=0.00..494203.84 rows=42 width=4) Filter: (((locale)::text = 'en'::text) AND (version = 2))

This query will get posts who don’t have search data, so we can fix those. It runs in less than 1 second in Meta, using a very weak database. Why do you have so many posts without search data? Maybe an import left you in a bad state?

[image] Falco: Why do you have so many posts without search data? I’m not aware that we do - we have never had problems with search. How can I see if a search is missing search data? [image] Falco: Maybe an import left you in a bad state? That is possible, but again: we’ve been using Discourse after migrating from vBulletin for almost a year now without a single problem with search. This issue started after I updated today.

I ran the previous version of this query, which only took about 1 second on my system too: SELECT p2.id FROM posts p2 LEFT JOIN post_search_data pd ON pd.locale = 'en' AND pd.version = 2 AND p2.id = pd.post_id WHERE pd.post_id IS NULL This returned 3139 rows.

Hmmm, the query was changed to be more restrict, and return less stuff to re-index. Can you run an EXPLAIN ANALYZE of the query?

I’m running it now, but it’s already taking several minutes - is that expected?

Oh yeah, it may not work in your current state. Cut down the limit, like: explain analyze SELECT posts.id FROM posts LEFT JOIN post_search_data pd ON pd.locale = 'en' AND pd.version = 2 AND pd.post_id = posts.id LEFT JOIN topics ON topics.id = posts.topic_id WHERE pd.post_id IS NULL AND topics.id IS NOT NULL AND topics.deleted_at IS NULL AND posts.raw != '' ORDER BY posts.id DESC LIMIT 200;

Great, that finished very fast: sudo -u postgres psql discourse -c "EXPLAIN ANALYZE SELECT posts.id FROM posts LEFT JOIN post_search_data pd ON pd.locale='en' AND pd.version=2 AND pd.post_id=posts.id LEFT JOIN topics ON topics.id=posts.topic_id WHERE pd.post_id IS NULL AND topics.id IS NOT NULL AND topics.deleted_at IS NULL AND posts.raw!='' ORDER BY posts.id DESC LIMIT 200;"; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=1.29..2470.34 rows=200 width=4) (actual time=2.318..46.698 rows=200 loops=1) -> Nested Loop Anti Join (cost=1.29..58896117.57 rows=4770758 width=4) (actual time=2.317..46.664 rows=200 loops=1) -> Nested Loop (cost=0.86..33044192.61 rows=4770860 width=4) (actual time=0.132..39.384 rows=2238 loops=1) -> Index Scan Backward using posts_pkey on posts (cost=0.43..12113605.04 rows=4772452 width=8) (actual time=0.075..27.719 rows=2239 loops=1) Filter: (raw <> ''::text) Rows Removed by Filter: 9 -> Index Only Scan using index_topics_on_id_and_deleted_at on topics (cost=0.43..4.39 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=2239) Index Cond: ((id = posts.topic_id) AND (id IS NOT NULL) AND (deleted_at IS NULL)) Heap Fetches: 2106 -> Index Scan using posts_search_pkey on post_search_data pd (cost=0.43..5.42 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2238) Index Cond: (post_id = posts.id) Filter: (((locale)::text = 'en'::text) AND (version = 2)) Rows Removed by Filter: 0 Planning time: 1.923 ms Execution time: 46.760 ms (15 rows)

That’s strange. Can you push that limit to 500? Maybe 1000? Also, you can kill the old running queries and run a vacuum over posts, topics and post_search_data ?

Once I push it to 500 it’s taking a very long time again (still not finished). I need a little guidance on killing the processes and doing the vacuum, sorry :slight_smile:

Some queries that don't finish

Product Support

bartv (Bart ) April 5, 2019, 6:55pm 15

Interesting - I didn’t realise I needed to do any of those actions as a Discourse admin. Is there a HOWTO about this, by any chance?

Topic		Replies	Views
Long-Running Sidekiq Jobs Feature	21	2202	December 24, 2020
PostgreSQL runaway IO Self-hosting	35	3873	March 3, 2021
Slow Sidekiq + Postmaster using 95%+ CPU (32 cores) after Postgresql Version Upgrade Self-hosting server-resources	22	3642	May 29, 2020
Slow Profile Loads with 100GB+ database Feature	42	3711	December 29, 2020
Slow SQL query causes homepage to load in 2-4 sec Product Support	15	1866	February 7, 2018

Some queries that don't finish

Related topics