Slow(?) postgresql queries, huge translate queue backlog

[Bubu]

Describe the issue

On translate.codeberg.org we have a translate celery queue backlog of currently ~32k tasks (was >220k a few weeks ago when I stared first looking at this though), many of which are apparently executing various different very slow (~30-90s) db queries, leading to a not really decreasing task queue size as well as permanent 4 core 100% cpu utilization on the server through postgres.

Happy to provide more info about our DB contents task queue, project configs, running queries, query plans, etc.

I already tried

• I've read and searched the documentation.
• I've searched for similar filed issues in this repository.

Steps to reproduce the behavior

See comment(s) because github complains about too long issue body. sigh

Expected behavior

No response

Screenshots

No response

Exception traceback

How do you run Weblate?

Docker container

Weblate versions

5.16.2.1 docker container

Weblate deploy checks

Additional context

No response

[Bubu]

I've looked at the postgres active queries as well as the celery task queue in redis to try and make sense of this. I found some queries and somewhat understand the reason why these are slow but I have no deeper understanding of the reason behind these queries being executed like this. (and if there's maybe a more fundamental problem in how some of the projects here are setup, etc.)

Slow query 1:

EXPLAIN ANALYZE SELECT "trans_unit"."id", "trans_unit"."translation_id", "trans_unit"."id_hash", "trans_unit"."location", "trans_unit"."context", "trans_unit"."note", "trans_unit"."flags", "trans_unit"."source", "trans_unit"."previous_source", "trans_unit"."target", "trans_unit"."state", "trans_unit"."original_state", "trans_unit"."details", "trans_unit"."position", "trans_unit"."num_words", "trans_unit"."priority", "trans_unit"."timestamp", "trans_unit"."last_updated", "trans_unit"."extra_flags", "trans_unit"."explanation", "trans_unit"."variant_id", "trans_unit"."automatically_translated", "trans_unit"."source_unit_id" FROM "trans_unit" INNER JOIN "trans_translation" ON ("trans_unit"."translation_id" = "trans_translation"."id") INNER JOIN "trans_component" ON ("trans_translation"."component_id" = "trans_component"."id") WHERE ("trans_component"."project_id" IN (SELECT U0."id" FROM "trans_project" U0 WHERE (U0."access_control" IN (0, 1) OR U0."id" IN (168, 521, 91))) AND "trans_unit"."source" % 'Prefer and only use where native' = true AND "trans_unit"."state" >= 20 AND "trans_component"."source_language_id" = 147 AND "trans_translation"."language_id" = 514 AND NOT (MD5(LOWER("trans_unit"."target")) = (MD5(LOWER('');

                                                                                 QUERY PLAN                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=2880.86..6072.34 rows=1 width=207) (actual time=48462.951..48462.969 rows=0.00 loops=1)
   Buffers: shared hit=14746 read=121964 written=1
   ->  Nested Loop  (cost=2880.59..6071.97 rows=1 width=211) (actual time=48462.950..48462.964 rows=0.00 loops=1)
         Buffers: shared hit=14746 read=121964 written=1
         ->  Hash Join  (cost=2880.31..6065.58 rows=1 width=211) (actual time=48462.950..48462.961 rows=0.00 loops=1)
               Hash Cond: (trans_unit.translation_id = trans_translation.id)
               Buffers: shared hit=14746 read=121964 written=1
               ->  Bitmap Heap Scan on trans_unit  (cost=2792.32..5976.62 rows=370 width=207) (actual time=6542.722..48462.530 rows=90.00 loops=1)
                     Recheck Cond: (source % 'Prefer and only use where native'::text)
                     Rows Removed by Index Recheck: 1960618
                     Filter: ((state >= 20) AND (md5(lower(target)) <> 'd41d8cd98f00b204e9800998ecf8427e'::text))
                     Rows Removed by Filter: 61
                     Heap Blocks: exact=57593 lossy=66431
                     Buffers: shared hit=14745 read=121942 written=1
                     ->  Bitmap Index Scan on unit_source_fulltext  (cost=0.00..2792.23 rows=826 width=0) (actual time=2102.713..2102.717 rows=205260.00 loops=1)
                           Index Cond: (source % 'Prefer and only use where native'::text)
                           Index Searches: 1
                           Buffers: shared hit=2603 read=5543
               ->  Hash  (cost=87.68..87.68 rows=25 width=8) (actual time=0.309..0.313 rows=25.00 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     Buffers: shared hit=1 read=22
                     ->  Bitmap Heap Scan on trans_translation  (cost=4.48..87.68 rows=25 width=8) (actual time=0.124..0.290 rows=25.00 loops=1)
                           Recheck Cond: (language_id = 514)
                           Heap Blocks: exact=21
                           Buffers: shared hit=1 read=22
                           ->  Bitmap Index Scan on trans_translation_language_id_030f0b30  (cost=0.00..4.47 rows=25 width=0) (actual time=0.044..0.045 rows=25.00 loops=1)
                                 Index Cond: (language_id = 514)
                                 Index Searches: 1
                                 Buffers: shared hit=1 read=1
         ->  Index Scan using trans_component_pkey on trans_component  (cost=0.28..6.38 rows=1 width=8) (never executed)
               Index Cond: (id = trans_translation.component_id)
               Filter: (source_language_id = 147)
               Index Searches: 0
   ->  Index Scan using trans_project_pkey on trans_project u0  (cost=0.27..0.37 rows=1 width=4) (never executed)
         Index Cond: (id = trans_component.project_id)
         Filter: ((access_control = ANY ('{0,1}'::integer[])) OR (id = ANY ('{168,521,91}'::integer[])))
         Index Searches: 0
 Planning:
   Buffers: shared hit=1032 read=10
 Planning Time: 13.144 ms
 Execution Time: 48464.771 ms
(41 rows)

Query 2:

EXPLAIN ANALYZE SELECT "memory_memory"."id", "memory_memory"."source_language_id", "memory_memory"."target_language_id", "memory_memory"."source", "memory_memory"."target", "memory_memory"."origin", "memory_memory"."context", "memory_memory"."user_id", "memory_memory"."project_id", "memory_memory"."from_file", "memory_memory"."shared", "memory_memory"."status", "trans_project"."id", "trans_project"."name", "trans_project"."slug", "trans_project"."web", "trans_project"."instructions", "trans_project"."set_language_team", "trans_project"."use_shared_tm", "trans_project"."contribute_shared_tm", "trans_project"."autoclean_tm", "trans_project"."access_control", "trans_project"."enforced_2fa", "trans_project"."translation_review", "trans_project"."source_review", "trans_project"."commit_policy", "trans_project"."enable_hooks", "trans_project"."language_aliases", "trans_project"."secondary_language_id", "trans_project"."check_flags", "trans_project"."machinery_settings" FROM "memory_memory" LEFT OUTER JOIN "trans_project" ON ("memory_memory"."project_id" = "trans_project"."id") WHERE (("memory_memory"."from_file" OR "memory_memory"."shared" OR "memory_memory"."project_id" = 521) AND "memory_memory"."source" % [very very long string] = true AND "memory_memory"."source_language_id" = 147 AND "memory_memory"."target_language_id" = 44) LIMIT 50;

                                                                                 QUERY PLAN                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=16714.55..16761.98 rows=1 width=333) (actual time=49790.893..49790.896 rows=0.00 loops=1)
   Buffers: shared hit=16761 read=33511
   ->  Nested Loop Left Join  (cost=16714.55..16761.98 rows=1 width=333) (actual time=49790.891..49790.894 rows=0.00 loops=1)
         Join Filter: (memory_memory.project_id = trans_project.id)
         Buffers: shared hit=16761 read=33511
         ->  Bitmap Heap Scan on memory_memory  (cost=16714.55..16722.57 rows=1 width=151) (actual time=49790.889..49790.891 rows=0.00 loops=1)
               Recheck Cond: ((source % [very, very long string]::text) AND (target_language_id = 44) AND (source_language_id = 147))
               Rows Removed by Index Recheck: 2
               Filter: (from_file OR shared OR (project_id = 521))
               Heap Blocks: exact=2
               Buffers: shared hit=16761 read=33511
               ->  Bitmap Index Scan on memory_source_trgm  (cost=0.00..16714.55 rows=2 width=0) (actual time=49787.119..49787.119 rows=2.00 loops=1)
                     Index Cond: ((source % [very, very long string]::text) AND (target_language_id = 44) AND (source_language_id = 147))
                     Index Searches: 1
                     Buffers: shared hit=16746 read=33494
         ->  Seq Scan on trans_project  (cost=0.00..33.07 rows=507 width=182) (never executed)
 Planning:
   Buffers: shared hit=542 read=61
 Planning Time: 213.886 ms
 Execution Time: 49795.934 ms
(82 rows)

Note, I've replaced one part of the query and the query planner result here with [very, very long string]. The original string was this:

Details

A community-led free & open source maps app based on OpenStreetMap data and reinforced with commitment to transparency, privacy and being not-for-profit. CoMaps is a fork/spin-off of Organic Maps, which in turn is a fork of Maps.ME.+
| +
| Read on about reasons for the project and its direction at codeberg.org/comaps. +
| Join the community there and help make the best maps app +
| • Use the app and spread the word about it +
| • Give feedback and report issues +
| • Update map data in the app or on the OpenStreetMap website -- +
| +
| ‣ Offline-focused: Plan and navigate your trip abroad without the need for cellular service, search waypoints while on a distant hike, etc. All app functions are designed to work offline. +
| ‣ Respecting Privacy: The app is designed with privacy in mind - does not identify people, does not track, and does not collect personal information. Ads-free. +
| ‣ Simple and Polished: essential easy to use features that just work. -- +
| ‣ Saves Your Battery and Space: Doesn’t drain your battery like other navigation apps. Compact maps save precious space on your phone. +
| ‣ Free and Built by the Community: People like you helped build the app by adding places to OpenStreetMap, testing and giving feedback on features and contributing their development skills and money. +
| ‣ Open and Transparent Decision-making and Financials, Not-for-profit and Fully Open Source. +
| +
| Main Features: +
| • Downloadable detailed maps with places which are not available with Google Maps +
| • Outdoor mode with highlighted hiking trails, campsites, water sources, peaks, contour lines, etc +
| • Walking paths and cycleways -- +
| • Points of interest like restaurants, gas stations, hotels, shops, sightseeings and many more +
| • Search by name or an address or by point of interest category +
| • Navigation with voice announcements for walking, cycling, or driving +
| • Bookmark your favorite places with a single tap -- +
| • Offline Wikipedia articles +
| • Subway transit layer and directions +
| • Track recording +
| • Export and import bookmarks and tracks in KML, KMZ, GPX formats -- +
| • A dark mode to use during the night +
| • Improve map data for everyone using a basic built-in editor +
| +
| Freedom Is Here +
| Discover your journey, navigate the world with privacy and community at the forefront!

And the last one I looked at, with the same long string and likely a very similar performance problem:

explain analyze SELECT "trans_unit"."id", "trans_unit"."translation_id", "trans_unit"."id_hash", "trans_unit"."location", "trans_unit"."context", "trans_unit"."note", "trans_unit"."flags", "trans_unit"."source", "trans_unit"."previous_source", "trans_unit"."target", "trans_unit"."state", "trans_unit"."original_state", "trans_unit"."details", "trans_unit"."position", "trans_unit"."num_words", "trans_unit"."priority", "trans_unit"."timestamp", "trans_unit"."last_updated", "trans_unit"."extra_flags", "trans_unit"."explanation", "trans_unit"."variant_id", "trans_unit"."automatically_translated", "trans_unit"."source_unit_id" FROM "trans_unit" INNER JOIN "trans_translation" ON ("trans_unit"."translation_id" = "trans_translation"."id") INNER JOIN "trans_component" ON ("trans_translation"."component_id" = "trans_component"."id") WHERE ("trans_unit"."source" % [very long string] = true AND "trans_unit"."state" >= 20 AND "trans_component"."source_language_id" = 147 AND "trans_translation"."language_id" = 539 AND NOT (MD5(LOWER("trans_unit"."target")) = (MD5(LOWER('')))));


                                                                                 QUERY PLAN                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=14924.04..18097.81 rows=1 width=206) (actual time=86636.157..86636.164 rows=0.00 loops=1)
   Buffers: shared hit=12913 read=45896
   ->  Hash Join  (cost=14923.76..18093.42 rows=1 width=210) (actual time=86636.156..86636.162 rows=0.00 loops=1)
         Hash Cond: (trans_unit.translation_id = trans_translation.id)
         Buffers: shared hit=12913 read=45896
         ->  Bitmap Heap Scan on trans_unit  (cost=14625.92..17795.10 rows=180 width=206) (actual time=86035.389..86635.381 rows=89.00 loops=1)
               Recheck Cond: (source % [very long string]::text)
               Rows Removed by Index Recheck: 146
               Filter: ((state >= 20) AND (md5(lower(target)) <> 'd41d8cd98f00b204e9800998ecf8427e'::text))
               Rows Removed by Filter: 72
               Heap Blocks: exact=282
               Buffers: shared hit=12834 read=45862
               ->  Bitmap Index Scan on unit_source_fulltext  (cost=0.00..14625.87 rows=822 width=0) (actual time=86033.438..86033.439 rows=307.00 loops=1)
                     Index Cond: (source % [very long string]::text)
                     Index Searches: 1
                     Buffers: shared hit=12156 read=45448
         ->  Hash  (cost=296.23..296.23 rows=129 width=8) (actual time=0.572..0.576 rows=129.00 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 14kB
               Buffers: shared hit=79 read=34
               ->  Bitmap Heap Scan on trans_translation  (cost=5.29..296.23 rows=129 width=8) (actual time=0.052..0.536 rows=129.00 loops=1)
                     Recheck Cond: (language_id = 539)
                     Heap Blocks: exact=111
                     Buffers: shared hit=79 read=34
                     ->  Bitmap Index Scan on trans_translation_language_id_030f0b30  (cost=0.00..5.25 rows=129 width=0) (actual time=0.015..0.015 rows=131.00 loops=1)
                           Index Cond: (language_id = 539)
                           Index Searches: 1
                           Buffers: shared hit=2
   ->  Index Scan using trans_component_pkey on trans_component  (cost=0.28..4.39 rows=1 width=4) (never executed)
         Index Cond: (id = trans_translation.component_id)
         Filter: (source_language_id = 147)
         Index Searches: 0
 Planning:
   Buffers: shared hit=620 read=92
 Planning Time: 179.413 ms
 Execution Time: 86641.792 ms
(97 rows)

Here's the first 5 tasks of the translate queue:

LRANGE translate 0 4
1) "{\"body\": \"W1tdLCB7Im1vZGUiOiAic3VnZ2VzdCIsICJxIjogInN0YXRlOjx0cmFuc2xhdGVkIiwgImF1dG9fc291cmNlIjogIm10IiwgImVuZ2luZXMiOiBbIndlYmxhdGUiLCAid2VibGF0ZS10cmFuc2xhdGlvbi1tZW1vcnkiXSwgInRocmVzaG9sZCI6IDgwLCAiY29tcG9uZW50IjogbnVsbCwgInVzZXJfaWQiOiAzMzU0LCAidW5pdF9pZHMiOiBbMTk4NTc3MzVdLCAidHJhbnNsYXRpb25faWQiOiA1MjkyOH0sIHsiY2FsbGJhY2tzIjogbnVsbCwgImVycmJhY2tzIjogbnVsbCwgImNoYWluIjogbnVsbCwgImNob3JkIjogbnVsbH1d\", \"content-encoding\": \"utf-8\", \"content-type\": \"application/json\", \"headers\": {\"lang\": \"py\", \"task\": \"weblate.trans.tasks.auto_translate\", \"id\": \"53c7d90e-ac9f-4677-a363-c454b2ec5a23\", \"shadow\": null, \"eta\": null, \"expires\": null, \"group\": null, \"group_index\": null, \"retries\": 0, \"timelimit\": [null, null], \"root_id\": \"b7e28224-ece4-4ec8-84e9-3928482c5f66\", \"parent_id\": \"b7e28224-ece4-4ec8-84e9-3928482c5f66\", \"argsrepr\": \"()\", \"kwargsrepr\": \"{'mode': 'suggest', 'q': 'state:<translated', 'auto_source': 'mt', 'engines': ['weblate', 'weblate-translation-memory'], 'threshold': 80, 'component': None, 'user_id': 3354, 'unit_ids': [19857735], 'translation_id': 52928}\", \"origin\": \"gen306@179b01379c4e\", \"ignore_result\": false, \"replaced_task_nesting\": 0, \"stamped_headers\": null, \"stamps\": {}}, \"properties\": {\"correlation_id\": \"53c7d90e-ac9f-4677-a363-c454b2ec5a23\", \"reply_to\": \"e141d2d7-d424-33b5-8140-796cf5e59348\", \"delivery_mode\": 2, \"delivery_info\": {\"exchange\": \"\", \"routing_key\": \"translate\"}, \"priority\": 0, \"body_encoding\": \"base64\", \"delivery_tag\": \"370c145c-cfef-46b0-8d86-480c6e44e74d\"}}"
2) "{\"body\": \"W1tdLCB7Im1vZGUiOiAic3VnZ2VzdCIsICJxIjogInN0YXRlOjx0cmFuc2xhdGVkIiwgImF1dG9fc291cmNlIjogIm10IiwgImVuZ2luZXMiOiBbIndlYmxhdGUiLCAid2VibGF0ZS10cmFuc2xhdGlvbi1tZW1vcnkiXSwgInRocmVzaG9sZCI6IDgwLCAiY29tcG9uZW50IjogbnVsbCwgInVzZXJfaWQiOiAzMzU0LCAidW5pdF9pZHMiOiBbMTk4NjQwNTJdLCAidHJhbnNsYXRpb25faWQiOiA0Mjk2Nn0sIHsiY2FsbGJhY2tzIjogbnVsbCwgImVycmJhY2tzIjogbnVsbCwgImNoYWluIjogbnVsbCwgImNob3JkIjogbnVsbH1d\", \"content-encoding\": \"utf-8\", \"content-type\": \"application/json\", \"headers\": {\"lang\": \"py\", \"task\": \"weblate.trans.tasks.auto_translate\", \"id\": \"f2a2afc8-a67a-40e1-95d4-8cb6081512c8\", \"shadow\": null, \"eta\": null, \"expires\": null, \"group\": null, \"group_index\": null, \"retries\": 0, \"timelimit\": [null, null], \"root_id\": \"098eb25f-7286-4fde-9c12-dd78645de98b\", \"parent_id\": \"098eb25f-7286-4fde-9c12-dd78645de98b\", \"argsrepr\": \"()\", \"kwargsrepr\": \"{'mode': 'suggest', 'q': 'state:<translated', 'auto_source': 'mt', 'engines': ['weblate', 'weblate-translation-memory'], 'threshold': 80, 'component': None, 'user_id': 3354, 'unit_ids': [19864052], 'translation_id': 42966}\", \"origin\": \"gen306@179b01379c4e\", \"ignore_result\": false, \"replaced_task_nesting\": 0, \"stamped_headers\": null, \"stamps\": {}}, \"properties\": {\"correlation_id\": \"f2a2afc8-a67a-40e1-95d4-8cb6081512c8\", \"reply_to\": \"48c8b69e-b1d1-3006-99e0-a13fda8135b0\", \"delivery_mode\": 2, \"delivery_info\": {\"exchange\": \"\", \"routing_key\": \"translate\"}, \"priority\": 0, \"body_encoding\": \"base64\", \"delivery_tag\": \"faaec746-61ba-4e81-9013-8afc08e52a1c\"}}"
3) "{\"body\": \"W1tdLCB7Im1vZGUiOiAic3VnZ2VzdCIsICJxIjogInN0YXRlOjx0cmFuc2xhdGVkIiwgImF1dG9fc291cmNlIjogIm10IiwgImVuZ2luZXMiOiBbIndlYmxhdGUiLCAid2VibGF0ZS10cmFuc2xhdGlvbi1tZW1vcnkiXSwgInRocmVzaG9sZCI6IDgwLCAiY29tcG9uZW50IjogbnVsbCwgInVzZXJfaWQiOiAzMzU0LCAidW5pdF9pZHMiOiBbMTk4NjM2NDBdLCAidHJhbnNsYXRpb25faWQiOiA1MjkyOH0sIHsiY2FsbGJhY2tzIjogbnVsbCwgImVycmJhY2tzIjogbnVsbCwgImNoYWluIjogbnVsbCwgImNob3JkIjogbnVsbH1d\", \"content-encoding\": \"utf-8\", \"content-type\": \"application/json\", \"headers\": {\"lang\": \"py\", \"task\": \"weblate.trans.tasks.auto_translate\", \"id\": \"901b5ef3-07ae-4053-b52d-8885504633d1\", \"shadow\": null, \"eta\": null, \"expires\": null, \"group\": null, \"group_index\": null, \"retries\": 0, \"timelimit\": [null, null], \"root_id\": \"b57d2ce4-833a-4bc8-b08f-f030f193d401\", \"parent_id\": \"b57d2ce4-833a-4bc8-b08f-f030f193d401\", \"argsrepr\": \"()\", \"kwargsrepr\": \"{'mode': 'suggest', 'q': 'state:<translated', 'auto_source': 'mt', 'engines': ['weblate', 'weblate-translation-memory'], 'threshold': 80, 'component': None, 'user_id': 3354, 'unit_ids': [19863640], 'translation_id': 52928}\", \"origin\": \"gen306@179b01379c4e\", \"ignore_result\": false, \"replaced_task_nesting\": 0, \"stamped_headers\": null, \"stamps\": {}}, \"properties\": {\"correlation_id\": \"901b5ef3-07ae-4053-b52d-8885504633d1\", \"reply_to\": \"e141d2d7-d424-33b5-8140-796cf5e59348\", \"delivery_mode\": 2, \"delivery_info\": {\"exchange\": \"\", \"routing_key\": \"translate\"}, \"priority\": 0, \"body_encoding\": \"base64\", \"delivery_tag\": \"219edad9-07e6-4ef1-a801-a9c96fb21e87\"}}"
4) "{\"body\": \"W1tdLCB7Im1vZGUiOiAic3VnZ2VzdCIsICJxIjogInN0YXRlOjx0cmFuc2xhdGVkIiwgImF1dG9fc291cmNlIjogIm10IiwgImVuZ2luZXMiOiBbIndlYmxhdGUiLCAid2VibGF0ZS10cmFuc2xhdGlvbi1tZW1vcnkiXSwgInRocmVzaG9sZCI6IDgwLCAiY29tcG9uZW50IjogbnVsbCwgInVzZXJfaWQiOiAyMDMyLCAidW5pdF9pZHMiOiBbMTk4NTc1NThdLCAidHJhbnNsYXRpb25faWQiOiA0Mjk1NX0sIHsiY2FsbGJhY2tzIjogbnVsbCwgImVycmJhY2tzIjogbnVsbCwgImNoYWluIjogbnVsbCwgImNob3JkIjogbnVsbH1d\", \"content-encoding\": \"utf-8\", \"content-type\": \"application/json\", \"headers\": {\"lang\": \"py\", \"task\": \"weblate.trans.tasks.auto_translate\", \"id\": \"6cbda765-f2b6-4d07-bd7c-625ac818f93f\", \"shadow\": null, \"eta\": null, \"expires\": null, \"group\": null, \"group_index\": null, \"retries\": 0, \"timelimit\": [null, null], \"root_id\": \"dd4962b2-885e-4864-b3e0-54efa555d0df\", \"parent_id\": \"dd4962b2-885e-4864-b3e0-54efa555d0df\", \"argsrepr\": \"()\", \"kwargsrepr\": \"{'mode': 'suggest', 'q': 'state:<translated', 'auto_source': 'mt', 'engines': ['weblate', 'weblate-translation-memory'], 'threshold': 80, 'component': None, 'user_id': 2032, 'unit_ids': [19857558], 'translation_id': 42955}\", \"origin\": \"gen306@179b01379c4e\", \"ignore_result\": false, \"replaced_task_nesting\": 0, \"stamped_headers\": null, \"stamps\": {}}, \"properties\": {\"correlation_id\": \"6cbda765-f2b6-4d07-bd7c-625ac818f93f\", \"reply_to\": \"cd4360e5-71e0-31c3-9d31-eff17be2bc57\", \"delivery_mode\": 2, \"delivery_info\": {\"exchange\": \"\", \"routing_key\": \"translate\"}, \"priority\": 0, \"body_encoding\": \"base64\", \"delivery_tag\": \"902fb2d2-31cd-4d6b-a65b-33774c8d9fce\"}}"
5) "{\"body\": \"W1tdLCB7Im1vZGUiOiAic3VnZ2VzdCIsICJxIjogInN0YXRlOjx0cmFuc2xhdGVkIiwgImF1dG9fc291cmNlIjogIm10IiwgImVuZ2luZXMiOiBbIndlYmxhdGUiLCAid2VibGF0ZS10cmFuc2xhdGlvbi1tZW1vcnkiXSwgInRocmVzaG9sZCI6IDgwLCAiY29tcG9uZW50IjogbnVsbCwgInVzZXJfaWQiOiAyMDMyLCAidW5pdF9pZHMiOiBbMTk4NTc3MDhdLCAidHJhbnNsYXRpb25faWQiOiA1MjkxNX0sIHsiY2FsbGJhY2tzIjogbnVsbCwgImVycmJhY2tzIjogbnVsbCwgImNoYWluIjogbnVsbCwgImNob3JkIjogbnVsbH1d\", \"content-encoding\": \"utf-8\", \"content-type\": \"application/json\", \"headers\": {\"lang\": \"py\", \"task\": \"weblate.trans.tasks.auto_translate\", \"id\": \"ed6b9fa5-6077-4143-a5d2-623d56db1999\", \"shadow\": null, \"eta\": null, \"expires\": null, \"group\": null, \"group_index\": null, \"retries\": 0, \"timelimit\": [null, null], \"root_id\": \"0967ddf2-0b6b-4269-aeb9-7174d62527c3\", \"parent_id\": \"0967ddf2-0b6b-4269-aeb9-7174d62527c3\", \"argsrepr\": \"()\", \"kwargsrepr\": \"{'mode': 'suggest', 'q': 'state:<translated', 'auto_source': 'mt', 'engines': ['weblate', 'weblate-translation-memory'], 'threshold': 80, 'component': None, 'user_id': 2032, 'unit_ids': [19857708], 'translation_id': 52915}\", \"origin\": \"gen306@179b01379c4e\", \"ignore_result\": false, \"replaced_task_nesting\": 0, \"stamped_headers\": null, \"stamps\": {}}, \"properties\": {\"correlation_id\": \"ed6b9fa5-6077-4143-a5d2-623d56db1999\", \"reply_to\": \"48c8b69e-b1d1-3006-99e0-a13fda8135b0\", \"delivery_mode\": 2, \"delivery_info\": {\"exchange\": \"\", \"routing_key\": \"translate\"}, \"priority\": 0, \"body_encoding\": \"base64\", \"delivery_tag\": \"54ee8ecd-2d52-44a3-b8c2-45d668cc1286\"}}"

The first of those base64-decoded:

[[], {"mode": "suggest", "q": "state:<translated", "auto_source": "mt", "engines": ["weblate", "weblate-translation-memory"], "threshold": 80, "component": null, "user_id": 3354, "unit_ids": [19857735], "translation_id": 52928}, {"callbacks": null, "errbacks": null, "chain": null, "chord": null}]

[nijel]

All these queries are executed with a non-default trigram limit. Can you please set it and rerun EXPLAIN ANALYZE with SELECT set_limit(0.98);?

PS: Increasing PostgreSQL work_mem might help as well.

[Bubu]

I'm rerunning the queries with the 0.98 limit:

The first one is a lot faster now (~500ms)

weblate=# SELECT set_limit(0.98);
 set_limit 
-----------
      0.98
(1 row)

weblate=# EXPLAIN ANALYZE SELECT "trans_unit"."id", "trans_unit"."translation_id", "trans_unit"."id_hash", "trans_unit"."location", "trans_unit"."context", "trans_unit"."note", "trans_unit"."flags", "trans_unit"."source", "trans_unit"."previous_source", "trans_unit"."target", "trans_unit"."state", "trans_unit"."original_state", "trans_unit"."details", "trans_unit"."position", "trans_unit"."num_words", "trans_unit"."priority", "trans_unit"."timestamp", "trans_unit"."last_updated", "trans_unit"."extra_flags", "trans_unit"."explanation", "trans_unit"."variant_id", "trans_unit"."automatically_translated", "trans_unit"."source_unit_id" FROM "trans_unit" INNER JOIN "trans_translation" ON ("trans_unit"."translation_id" = "trans_translation"."id") INNER JOIN "trans_component" ON ("trans_translation"."component_id" = "trans_component"."id") WHERE ("trans_component"."project_id" IN (SELECT U0."id" FROM "trans_project" U0 WHERE (U0."access_control" IN (0, 1) OR U0."id" IN (168, 521, 91))) AND "trans_unit"."source" % 'Prefer and only use where native'
= true AND "trans_unit"."state" >= 20 AND "trans_component"."source_language_id" = 147 AND "trans_translation"."language_id" = 514 AND NOT (MD5(LOWER("trans_unit"."target")) =
(MD5(LOWER('')))));
                                                                                 QUERY PLAN                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=968.21..4152.07 rows=1 width=206) (actual time=557.583..557.589 rows=0.00 loops=1)
   Buffers: shared hit=4994 read=5340
   ->  Nested Loop  (cost=967.94..4151.70 rows=1 width=210) (actual time=557.582..557.587 rows=0.00 loops=1)
         Buffers: shared hit=4994 read=5340
         ->  Hash Join  (cost=967.66..4144.99 rows=1 width=210) (actual time=557.581..557.585 rows=0.00 loops=1)
               Hash Cond: (trans_unit.translation_id = trans_translation.id)
               Buffers: shared hit=4994 read=5340
               ->  Bitmap Heap Scan on trans_unit  (cost=879.64..4056.50 rows=180 width=206) (actual time=471.786..557.274 rows=90.00 loops=1)
                     Recheck Cond: (source % 'Prefer and only use where native'::text)
                     Rows Removed by Index Recheck: 26
                     Filter: ((state >= 20) AND (md5(lower(target)) <> 'd41d8cd98f00b204e9800998ecf8427e'::text))
                     Rows Removed by Filter: 61
                     Heap Blocks: exact=159
                     Buffers: shared hit=4992 read=5319
                     ->  Bitmap Index Scan on unit_source_fulltext  (cost=0.00..879.59 rows=824 width=0) (actual time=471.545..471.546 rows=177.00 loops=1)
                           Index Cond: (source % 'Prefer and only use where native'::text)
                           Index Searches: 1
                           Buffers: shared hit=4950 read=5109
               ->  Hash  (cost=87.71..87.71 rows=25 width=8) (actual time=0.267..0.268 rows=25.00 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     Buffers: shared hit=2 read=21
                     ->  Bitmap Heap Scan on trans_translation  (cost=4.48..87.71 rows=25 width=8) (actual time=0.112..0.243 rows=25.00 loops=1)
                           Recheck Cond: (language_id = 514)
                           Heap Blocks: exact=21
                           Buffers: shared hit=2 read=21
                           ->  Bitmap Index Scan on trans_translation_language_id_030f0b30  (cost=0.00..4.47 rows=25 width=0) (actual time=0.021..0.021 rows=25.00 loops=1)
                                 Index Cond: (language_id = 514)
                                 Index Searches: 1
                                 Buffers: shared hit=1 read=1
         ->  Index Scan using trans_component_pkey on trans_component  (cost=0.28..6.70 rows=1 width=8) (never executed)
               Index Cond: (id = trans_translation.component_id)
               Filter: (source_language_id = 147)
               Index Searches: 0
   ->  Index Scan using trans_project_pkey on trans_project u0  (cost=0.27..0.37 rows=1 width=4) (never executed)
         Index Cond: (id = trans_component.project_id)
         Filter: ((access_control = ANY ('{0,1}'::integer[])) OR (id = ANY ('{168,521,91}'::integer[])))
         Index Searches: 0
 Planning:
   Buffers: shared hit=19 read=30
 Planning Time: 4.619 ms
 Execution Time: 558.191 ms
(41 rows)

The other two seem also faster, but are still pretty slow (4-6s):

                                                                                                                                                                        QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=17267.01..17314.44 rows=1 width=333) (actual time=3830.226..3830.228 rows=0.00 loops=1)
   Buffers: shared hit=44672 read=31870
   ->  Nested Loop Left Join  (cost=17267.01..17314.44 rows=1 width=333) (actual time=3830.224..3830.226 rows=0.00 loops=1)
         Join Filter: (memory_memory.project_id = trans_project.id)
         Buffers: shared hit=44672 read=31870
         ->  Bitmap Heap Scan on memory_memory  (cost=17267.01..17275.04 rows=1 width=151) (actual time=3830.223..3830.224 rows=0.00 loops=1)
               Recheck Cond: ((source % 'A community-led free & open source maps app based on OpenStreetMap data and reinforced with commitment to transparency, privacy and being not-for-profit. CoMaps is a fork/spin-off of Organic Maps, which in turn is a fork of Maps.ME.                                                                                                                                                                                                                                                                                          +
                                |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     +
                                | Read on about reasons for the project and its direction at <b><i>codeberg.org/comaps</i></b>.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       +
                                | Join the community there and help make the best maps app                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            +
                                | • Use the app and spread the word about it                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          +
                                | • Give feedback and report issues                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   +
                                | • Update map data in the app or on the OpenStreetMap website                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        +
                                |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     +
                                | ‣ <b>Offline-focused</b>: Plan and navigate your trip abroad without the need for cellular service, search waypoints while on a distant hike, etc. All app functions are designed to work offline.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  +
                                | ‣ <b>Respecting Privacy</b>: The app is designed with privacy in mind - does not identify people, does not track, and does not collect personal information. Ads-free.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              +
                                | ‣ <b>Simple and Polished</b>: essential easy to use features that just work.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        +
                                | ‣ <b>Saves Your Battery and Space</b>: Doesn’t drain your battery like other navigation apps. Compact maps save precious space on your phone.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             --Mo                                                                                                                                                                                                                                                                                                                                                                 --Mo                                                                                                                                                                         +
                                | ‣ <b>Free and Built by the Community</b>: People like you helped build the app by adding places to OpenStreetMap, testing and giving feedback on features and contributing their development skills and money.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      +
                                | ‣ <b>Open and Transparent Decision-making and Financials, Not-for-profit and Fully Open Source.</b>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 +
                                |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     +
                                | <b>Main Features</b>:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               +
                                | • Downloadable detailed maps with places which are not available with Google Maps                                                                                                                                                                                                                                           --                                                                                                                                                                                                                                                                                                                                                                 --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       +
                                | • Outdoor mode with highlighted hiking trails, campsites, water sources, peaks, contour lines, etc                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  +
                                | • Walking paths and cycleways                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       +
                                | • Points of interest like restaurants, gas stations, hotels, shops, sightseeings and many more                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      +
                                | • Search by name or an address or by point of interest category                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             --                                                                                                                                                                        +
                                | • Navigation with voice announcements for walking, cycling, or driving                                                                       --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       +
                                | • Bookmark your favorite places with a single tap                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   +
                                | • Offline Wikipedia articles                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        +
                                | • Subway transit layer and directions                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               +
                                | • Track recording                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           --                                                                                                                                                                                                                                                                                                                                                                 --                                                                                                                                                                                                                                                                                                                                                       +
                                | • Export and import bookmarks and tracks in KML, KMZ, GPX formats                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   +
                                | • A dark mode to use during the night                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               +
                                | • Improve map data for everyone using a basic built-in editor                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       +
                                |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     +
                                | <b>Freedom Is Here</b>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              +
                                | Discover your journey, navigate the world with privacy and community at the forefront!'::text) AND (target_language_id = 44) AND (source_language_id = 147))
               Filter: (from_file OR shared OR (project_id = 521))
               Buffers: shared hit=44672 read=31870
               ->  Bitmap Index Scan on memory_source_trgm  (cost=0.00..17267.01 rows=2 width=0) (actual time=3830.213..3830.213 rows=0.00 loops=1)
                     Index Cond: ((source % 'A community-led free & open source maps app based on OpenStreetMap data and reinforced with commitment to transparency, privacy and being not-for-profit. CoMaps is a fork/spin-off of Organic Maps, which in turn is a fork of Maps.ME.                                                                                                                                                                                                                                                                                          +
                                |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     +
                                | Read on about reasons for the project and its direction at <b><i>codeberg.org/comaps</i></b>.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       +
                                | Join the community there and help make the best maps app                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            +
                                | • Use the app and spread the word about it                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          +
                                | • Give feedback and report issues                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   +
                                | • Update map data in the app or on the OpenStreetMap website                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        +
                                |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     +
                                | ‣ <b>Offline-focused</b>: Plan and navigate your trip abroad without the need for cellular service, search waypoints while on a distant hike, etc. All app functions are designed to work offline.                                                                                                                          --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        +
                                | ‣ <b>Respecting Privacy</b>: The app is designed with privacy in mind - does not identify people, does not track, and does not collect personal information. Ads-free.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              +
                                | ‣ <b>Simple and Polished</b>: essential easy to use features that just work.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        +
                                | ‣ <b>Saves Your Battery and Space</b>: Doesn’t drain your battery like other navigation apps. Compact maps save precious space on your phone.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              --Mo                                                                                                                                                                         +
                                | ‣ <b>Free and Built by the Community</b>: People like you helped build the app by adding places to OpenStreetMap, testing and giving feedback on features and contributing their development skills and money.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              --                                                                                                                                                                        +
                                | ‣ <b>Open and Transparent Decision-making and Financials, Not-for-profit and Fully Open Source.</b>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 +
                                |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     +
                                | <b>Main Features</b>:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               +
                                | • Downloadable detailed maps with places which are not available with Google Maps                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       +
                                | • Outdoor mode with highlighted hiking trails, campsites, water sources, peaks, contour lines, etc                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        +
                                | • Walking paths and cycleways                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       +
                                | • Points of interest like restaurants, gas stations, hotels, shops, sightseeings and many more                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      +
                                | • Search by name or an address or by point of interest category                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     +
                                | • Navigation with voice announcements for walking, cycling, or driving                                                                       --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       +
                                | • Bookmark your favorite places with a single tap                                                                                           --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        +
                                | • Offline Wikipedia articles                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        +
                                | • Subway transit layer and directions                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               +
                                | • Track recording                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            --                                                                                                                                                                                                                                                                                                                                                       +
                                | • Export and import bookmarks and tracks in KML, KMZ, GPX formats                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           --                                                                                                                                                                                                                                                                                                                                                        +
                                | • A dark mode to use during the night                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               +
                                | • Improve map data for everyone using a basic built-in editor                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       +
                                |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     +
                                | <b>Freedom Is Here</b>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              +
                                | Discover your journey, navigate the world with privacy and community at the forefront!'::text) AND (target_language_id = 44) AND (source_language_id = 147))
                     Index Searches: 1
                     Buffers: shared hit=44672 read=31870
         ->  Seq Scan on trans_project  (cost=0.00..33.07 rows=507 width=182) (never executed)
 Planning:
   Buffers: shared hit=15
 Planning Time: 194.170 ms
 Execution Time: 3835.980 ms
(80 rows)

                                                                                                                                                                         QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=15096.23..18277.89 rows=1 width=206) (actual time=5130.228..5130.233 rows=0.00 loops=1)
   Buffers: shared hit=45505 read=46929
   ->  Hash Join  (cost=15095.95..18273.28 rows=1 width=210) (actual time=5130.227..5130.230 rows=0.00 loops=1)
         Hash Cond: (trans_unit.translation_id = trans_translation.id)
         Buffers: shared hit=45505 read=46929
         ->  Bitmap Heap Scan on trans_unit  (cost=14795.92..17972.78 rows=180 width=206) (actual time=5031.454..5129.211 rows=37.00 loops=1)
               Recheck Cond: (source % 'A community-led free & open source maps app based on OpenStreetMap data and reinforced with commitment to transparency, privacy and being not-for-profit. CoMaps is a fork/spin-off of Organic Maps, which in turn is a fork of Maps.ME.                                                                                                                                                                                                                                                                                          +
                                |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     +
                                | Read on about reasons for the project and its direction at <b><i>codeberg.org/comaps</i></b>.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       +
                                | Join the community there and help make the best maps app                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            +
                                | • Use the app and spread the word about it                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          +
                                | • Give feedback and report issues                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   +
                                | • Update map data in the app or on the OpenStreetMap website                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        +
                                |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     +
                                | ‣ <b>Offline-focused</b>: Plan and navigate your trip abroad without the need for cellular service, search waypoints while on a distant hike, etc. All app functions are designed to work offline.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  +
                                | ‣ <b>Respecting Privacy</b>: The app is designed with privacy in mind - does not identify people, does not track, and does not collect personal information. Ads-free.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              +
                                | ‣ <b>Simple and Polished</b>: essential easy to use features that just work.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        +
                                | ‣ <b>Saves Your Battery and Space</b>: Doesn’t drain your battery like other navigation apps. Compact maps save precious space on your phone.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             --Mo                                                                                                                                                                                                                                                                                                                                                                 --Mo                                                                                                                                                                         +
                                | ‣ <b>Free and Built by the Community</b>: People like you helped build the app by adding places to OpenStreetMap, testing and giving feedback on features and contributing their development skills and money.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      +
                                | ‣ <b>Open and Transparent Decision-making and Financials, Not-for-profit and Fully Open Source.</b>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 +
                                |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     +
                                | <b>Main Features</b>:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               +
                                | • Downloadable detailed maps with places which are not available with Google Maps                                                                                                                                                                                                                                           --                                                                                                                                                                                                                                                                                                                                                                 --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       +
                                | • Outdoor mode with highlighted hiking trails, campsites, water sources, peaks, contour lines, etc                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  +
                                | • Walking paths and cycleways                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       +
                                | • Points of interest like restaurants, gas stations, hotels, shops, sightseeings and many more                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      +
                                | • Search by name or an address or by point of interest category                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             --                                                                                                                                                                        +
                                | • Navigation with voice announcements for walking, cycling, or driving                                                                       --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       +
                                | • Bookmark your favorite places with a single tap                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   +
                                | • Offline Wikipedia articles                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        +
                                | • Subway transit layer and directions                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               +
                                | • Track recording                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           --                                                                                                                                                                                                                                                                                                                                                                 --                                                                                                                                                                                                                                                                                                                                                       +
                                | • Export and import bookmarks and tracks in KML, KMZ, GPX formats                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   +
                                | • A dark mode to use during the night                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               +
                                | • Improve map data for everyone using a basic built-in editor                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       +
                                |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     +
                                | <b>Freedom Is Here</b>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              +
                                | Discover your journey, navigate the world with privacy and community at the forefront!'::text)
               Filter: ((state >= 20) AND (md5(lower(target)) <> 'd41d8cd98f00b204e9800998ecf8427e'::text))
               Rows Removed by Filter: 35
               Heap Blocks: exact=69
               Buffers: shared hit=45412 read=46909
               ->  Bitmap Index Scan on unit_source_fulltext  (cost=0.00..14795.88 rows=824 width=0) (actual time=5029.855..5029.856 rows=72.00 loops=1)
                     Index Cond: (source % 'A community-led free & open source maps app based on OpenStreetMap data and reinforced with commitment to transparency, privacy and being not-for-profit. CoMaps is a fork/spin-off of Organic Maps, which in turn is a fork of Maps.ME.                                                                                                                                                                                                                                                                                          +
                                |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     +
                                | Read on about reasons for the project and its direction at <b><i>codeberg.org/comaps</i></b>.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       +
                                | Join the community there and help make the best maps app                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            +
                                | • Use the app and spread the word about it                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          +
                                | • Give feedback and report issues                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   +
                                | • Update map data in the app or on the OpenStreetMap website                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        +
                                |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     +
                                | ‣ <b>Offline-focused</b>: Plan and navigate your trip abroad without the need for cellular service, search waypoints while on a distant hike--, etc. All app functions are designed to work offline.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  +
                                | ‣ <b>Respecting Privacy</b>: The app is designed with privacy in mind - does not identify people, does not track, and does not collect personal information. Ads-free.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              +
                                | ‣ <b>Simple and Polished</b>: essential easy to use features that just work.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        +
                                | ‣ <b>Saves Your Battery and Space</b>: Doesn’t drain your battery like other navigation apps. Compact maps save precious space on your phone.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              --Mo                                                                                                                                                                         +
                                | ‣ <b>Free and Built by the Community</b>: People like you helped build the app by adding places to OpenStreetMap, testing and giving feedback on features and contributing their development skills and money.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              --                                                                                                                                                                                                                                                                                                                                                        +
                                | ‣ <b>Open and Transparent Decision-making and Financials, Not-for-profit and Fully Open Source.</b>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 +
                                |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     +
                                | <b>Main Features</b>:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               +
                                | • Downloadable detailed maps with places which are not available with Google Maps                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       +
                                | • Outdoor mode with highlighted hiking trails, campsites, water sources, peaks, contour lines, etc                                                                                                                                                                                                                                                                                                                                                                                                          --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        +
                                | • Walking paths and cycleways                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       +
                                | • Points of interest like restaurants, gas stations, hotels, shops, sightseeings and many more                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      +
                                | • Search by name or an address or by point of interest category                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     +
                                | • Navigation with voice announcements for walking, cycling, or driving                                                                       --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       +
                                | • Bookmark your favorite places with a single tap                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   +
                                | • Offline Wikipedia articles                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        +
                                | • Subway transit layer and directions                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               +
                                | • Track recording                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            --                                                                                                                                                                                                                                                                                                                                                       +
                                | • Export and import bookmarks and tracks in KML, KMZ, GPX formats                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        +
                                | • A dark mode to use during the night                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               +
                                | • Improve map data for everyone using a basic built-in editor                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       +
                                |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     +
                                | <b>Freedom Is Here</b>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              +
                                | Discover your journey, navigate the world with privacy and community at the forefront!'::text)
                     Index Searches: 1
                     Buffers: shared hit=45257 read=46884
         ->  Hash  (cost=298.40..298.40 rows=130 width=8) (actual time=0.979..0.980 rows=129.00 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 14kB
               Buffers: shared hit=93 read=20
               ->  Bitmap Heap Scan on trans_translation  (cost=5.29..298.40 rows=130 width=8) (actual time=0.082..0.916 rows=129.00 loops=1)
                     Recheck Cond: (language_id = 539)
                     Heap Blocks: exact=111
                     Buffers: shared hit=93 read=20
                     ->  Bitmap Index Scan on trans_translation_language_id_030f0b30  (cost=0.00..5.26 rows=130 width=0) (actual time=0.035..0.035 rows=131.00 loops=1)
                           Index Cond: (language_id = 539)
                           Index Searches: 1
                           Buffers: shared hit=2
   ->  Index Scan using trans_component_pkey on trans_component  (cost=0.28..4.61 rows=1 width=4) (never executed)
         Index Cond: (id = trans_translation.component_id)
         Filter: (source_language_id = 147)
         Index Searches: 0
 Planning:
   Buffers: shared hit=38
 Planning Time: 250.632 ms
 Execution Time: 5135.181 ms
(96 rows)

[Bubu]

But that makes me honestly more confused as to where all the cpu time is spent in postgres? I suppose (apart from these very weird long string queries) any individual query is not taking that much time. But somehow we are still not really catching up on our work queue, only clearing a few jobs every hour or so? and postgres is executing some variations of the above select queries all the time.

[nijel]

The memory query will be executed with a slightly lower threshold, so that one is most likely the culprit here. I was looking into this today anyway, and #18627 might be a solution.

Have you tried increasing PostgreSQL work_mem if it has any effect? Use SHOW work_mem; to get the current setting and SET work_mem TO '512MB'; to adjust it.

[Bubu]

Increasing work_mem doesn't seem to be doing a lot for these specific queries. I can try to increase it for the postgres instance and see if we get any faster through our task queue, I suppose?

[Bubu]

This does not seem to make any difference either.

Any ideas what else I could check?

[Bubu]

I've increased the postgres shared_buffers as well yesterday evening and maybe that did make a difference as we have cleared around 5k tasks since yesterday. (or it might just be coincidence)

What I don't quite understand is why some of the tasks seem to run for multiple hours without completing but never being stuck in a single postgres query either.

Is there any way I can get more information about the currently executing celery tasks, what they are doing, their progress maybe or anything else?

The only other idea I had was trying to log all the postgres queries and do some analysis over this, but not sure if that would actually reveal anything.