Currently running another 0.19 -> 1.0 migration with local lemmy.ml prod data.
- Fix any migration issues.
- See how long the migration takes ( ~15h)
- Show DB timings report with prod data again
- Provide a full schema dump
- Grab a few explain analyze queries for the slow ones, so that sql experts can look over and assist
- List posts active (142 ms)
- Get url post (456 ms)
- Get comments for post with various sorts (because all of them are slow)
- Get comments with moderator view (2 seconds?)
- List reports
- Search
Schema
Slower queries (depesz explain analyze)
Speed tests (Updated 2026-05-01 9am)
List posts with different sorts
| sort |
time |
| active |
142ms |
| hot |
101ms |
| new |
102ms |
| old |
80ms |
| top |
85ms |
| most_comments |
79ms |
| new_comments |
89ms |
| controversial |
81ms |
| scaled |
98ms |
List posts for a community with different sorts
| sort |
time |
| active |
81ms |
| hot |
75ms |
| new |
73ms |
| old |
75ms |
| top |
76ms |
| most_comments |
78ms |
| new_comments |
77ms |
| controversial |
76ms |
| scaled |
75ms |
List posts with different listing types
| type |
time |
| all |
123ms |
| local |
99ms |
| subscribed |
123ms |
| moderator_view |
83ms |
| suggested |
76ms |
List posts with show hidden
show hidden: 99ms
List posts with hide read
show read : 97ms
List posts with higher pages
| page # |
time |
| 0 |
98ms |
| 1 |
111ms |
| 2 |
109ms |
| 3 |
109ms |
| 4 |
104ms |
| 5 |
110ms |
| 6 |
104ms |
| 7 |
103ms |
| 8 |
113ms |
| 9 |
119ms |
| avg |
108ms |
List posts for a multi-community with different sorts
| sort |
time |
| active |
73ms |
| hot |
72ms |
| new |
70ms |
| old |
69ms |
| top |
70ms |
| most_comments |
72ms |
| new_comments |
72ms |
| controversial |
71ms |
| scaled |
72ms |
List communities with different sorts
| sort |
time |
| active_six_months |
60ms |
| active_monthly |
57ms |
| active_weekly |
58ms |
| active_daily |
60ms |
| hot |
56ms |
| new |
56ms |
| old |
57ms |
| name_asc |
56ms |
| name_desc |
57ms |
| comments |
54ms |
| posts |
56ms |
| subscribers |
47ms |
| subscribers_local |
24ms |
List communities with different listing types
| type |
time |
| all |
58ms |
| local |
31ms |
| subscribed |
40ms |
| moderator_view |
16ms |
| suggested |
11ms |
List multi-communities with different sorts
| sort |
time |
| new |
7ms |
| old |
6ms |
| name_asc |
6ms |
| name_desc |
7ms |
| communities |
7ms |
| subscribers |
6ms |
| subscribers_local |
6ms |
Get a community
get community: 24ms
Get a post
| type |
time |
| url post |
456ms |
| text post |
42ms |
Get comments for a post with different sorts, tree
| sort |
time |
| hot |
289ms |
| top |
254ms |
| new |
257ms |
| old |
276ms |
| controversial |
255ms |
Get comments for a post with different sorts, flat
| sort |
time |
| hot |
82ms |
| top |
85ms |
| new |
85ms |
| old |
86ms |
| controversial |
82ms |
Get comments for a post slim
getCommentsSlim: 198ms
Get all comments with different sorts
| sort |
time |
| hot |
98ms |
| top |
80ms |
| new |
94ms |
| old |
80ms |
| controversial |
93ms |
Get comments with different types
| type |
time |
| all |
124ms |
| local |
81ms |
| subscribed |
126ms |
| moderator_view |
2038ms |
| suggested |
74ms |
List person content with types
| type |
time |
| all |
90ms |
| comments |
76ms |
| posts |
80ms |
List person saved with types
| type |
time |
| all |
90ms |
| comments |
82ms |
| posts |
78ms |
List person liked with types
| type |
time |
| all |
121ms |
| comments |
116ms |
| posts |
110ms |
| all |
110ms |
| liked_only |
108ms |
| disliked_only |
148ms |
List person read
list person read: 85ms
List person hidden
list person hidden: 27ms
List registration applications
| type |
time |
| unread only |
11ms |
| all |
16ms |
List reports
| type |
time |
| unresolved only |
173ms |
| all |
326ms |
List reports with type
| type |
time |
| all |
321ms |
| posts |
331ms |
| comments |
325ms |
| private_messages |
292ms |
| communities |
159ms |
Search with types
| type |
sort |
time |
| post |
active |
188ms |
| post |
hot |
164ms |
| post |
new |
168ms |
| post |
old |
87ms |
| post |
top |
137ms |
| post |
most_comments |
246ms |
| post |
new_comments |
162ms |
| post |
controversial |
112ms |
| post |
scaled |
165ms |
| comment |
hot |
112ms |
| comment |
top |
105ms |
| comment |
new |
140ms |
| comment |
old |
94ms |
| comment |
controversial |
256ms |
| community |
active_six_months |
72ms |
| community |
active_monthly |
69ms |
| community |
active_weekly |
70ms |
| community |
active_daily |
70ms |
| community |
hot |
64ms |
| community |
new |
63ms |
| community |
old |
53ms |
| community |
name_asc |
64ms |
| community |
name_desc |
62ms |
| community |
comments |
67ms |
| community |
posts |
70ms |
| community |
subscribers |
69ms |
| community |
subscribers_local |
54ms |
| multi community |
new |
7ms |
| multi community |
old |
6ms |
| multi community |
name_asc |
6ms |
| multi community |
name_desc |
6ms |
| multi community |
communities |
6ms |
| multi community |
subscribers |
6ms |
| multi community |
subscribers_local |
6ms |
| person |
new |
25ms |
| person |
old |
24ms |
| person |
post_score |
27ms |
| person |
comment_score |
33ms |
Notifications with types
| type |
time |
| all |
216ms |
| mention |
241ms |
| reply |
207ms |
| subscribed |
197ms |
| private_message |
288ms |
| mod_action |
144ms |
Notifications with unread only
| type |
time |
| all |
207ms |
| unread_only |
203ms |
Liking a comment / post
| type |
time |
| comment |
125ms |
| post |
67ms |
Get modlog with types
| type |
time |
| all |
37ms |
| admin_add |
24ms |
| admin_ban |
27ms |
| admin_allow_instance |
14ms |
| admin_block_instance |
13ms |
| admin_purge_comment |
18ms |
| admin_purge_community |
17ms |
| admin_purge_person |
22ms |
| admin_purge_post |
27ms |
| mod_add_to_community |
30ms |
| mod_ban_from_community |
31ms |
| admin_feature_post_site |
34ms |
| mod_feature_post_community |
34ms |
| mod_change_community_visibility |
13ms |
| mod_lock_post |
40ms |
| mod_remove_comment |
41ms |
| admin_remove_community |
33ms |
| mod_remove_post |
36ms |
| mod_transfer_community |
39ms |
| mod_lock_comment |
13ms |