Skip to content

Avoid re-running the book-statuses UNION as a subquery in add_book_statuses - #4027

Merged
mouse-reeve merged 3 commits into
bookwyrm-social:mainfrom
TemRevil:fix/add-book-statuses-union-subquery
Jul 20, 2026
Merged

mouse-reeve merged 3 commits into
bookwyrm-social:mainfrom
TemRevil:fix/add-book-statuses-union-subquery

Conversation

@TemRevil

Copy link
Copy Markdown
Contributor

Fixes #4026

Problem

add_book_statuses builds a UNION of the four book-status querysets (comments, quotations, reviews, mentions), then reuses that same union queryset twice more:

book_statuses = book_comments.union(book_quotations, book_reviews, book_mentions)
self.bulk_add_objects_to_store(book_statuses, ...)
threads = book_statuses.values_list("thread_id", flat=True)
thread_statuses = statuses.exclude(
    id__in=book_statuses.values_list("id", flat=True)   # UNION embedded as a subquery
).filter(thread_id__in=threads)                          # UNION embedded again

Embedding a UNION inside id__in= / thread_id__in= makes Postgres re-execute the whole UNION for each lookup. As reported in #4026, this pushed add_book_statuses_task from ~0.1–0.2s to ~7s per call on a real instance after #24640… (the change that introduced the union path in #035bebf0c).

Change

Evaluate the union once into plain id / thread_id lists and reuse those, so the two follow-up lookups filter on integer lists instead of re-running the UNION as a subquery:

book_status_rows = list(book_statuses.values_list("id", "thread_id"))
book_status_ids = [row[0] for row in book_status_rows]
threads = [row[1] for row in book_status_rows]
thread_statuses = statuses.exclude(id__in=book_status_ids).filter(thread_id__in=threads)

The same set of statuses is added; only the query shape changes. values_list() with columns is one of the operations Django explicitly allows on a combined (union) queryset. The same pattern is in remove_book_statuses and the HomeStream.add_book_statuses override, so I applied the identical change there for consistency.

Testing

bookwyrm/tests/activitystreams/ passes against Postgres + Redis: 43 passed. I could not benchmark absolute timings without a production-scale dataset, but the change removes exactly the repeated-UNION-subquery execution the issue identifies.

add_book_statuses built a UNION of the four book-status querysets and
then reused it twice more: once via values_list("thread_id") and once
inside exclude(id__in=...values_list("id")). Embedding a UNION as a
subquery makes Postgres re-execute the whole UNION each time, which is
what pushed add_book_statuses_task from ~0.1s to several seconds per
call on larger instances (bookwyrm-social#4026).

Evaluate the UNION once into id/thread_id lists and reuse those, so the
two follow-up lookups filter on plain id lists instead of a UNION
subquery. Same statuses are added; behaviour is unchanged. Applied the
same change to remove_book_statuses and the HomeStream override, which
share the pattern.

Fixes bookwyrm-social#4026
@ilkka-ollakka

Copy link
Copy Markdown
Contributor

Thanks for the PR. Can you provide explain analyze to show query plan change?

Also have you checked AGENTS.md file?

@TemRevil

Copy link
Copy Markdown
Contributor Author

okay i'll do, and yes i did, after you told me already

@TemRevil

Copy link
Copy Markdown
Contributor Author

Is there anything about it?

@TemRevil

Copy link
Copy Markdown
Contributor Author

Here's an explain analyze. I set up a book with 53 comments/quotes/reviews (book_statuses) plus reply threads and unrelated noise statuses, ran the same thread_statuses query both ways, and confirmed the result sets are identical (141 rows, same IDs either way).

Old code (book_statuses referenced twice, so the 4-way UNION gets re-embedded and re-planned in both the NOT IN and the IN clause):

Sort  (cost=1890.67..1892.71 rows=813 width=3952) (actual time=4.846..4.892 rows=141 loops=1)
  ->  Hash Join  (cost=814.83..1851.38 rows=813 width=3952) (actual time=2.636..4.787 rows=141 loops=1)
        Hash Cond: (bookwyrm_status.thread_id = "*SELECT* 1".thread_id)
        ...
Planning Time: 6.669 ms
Execution Time: 5.391 ms

8 Subquery Scan nodes total (the 4 UNION branches, duplicated: once for id NOT IN, once for thread_id IN). 3675 buffer hits.

New code (union evaluated once into plain lists):

Sort  (cost=20286.34..20314.80 rows=11381 width=3952) (actual time=1.073..1.084 rows=141 loops=1)
  ->  Nested Loop Left Join  (cost=13.65..185.59 rows=11381 width=3952) (actual time=0.148..0.976 rows=141 loops=1)
        ...
Planning Time: 2.329 ms
Execution Time: 1.198 ms

0 Subquery Scan nodes, both lookups are id <> ALL ('{...}') / thread_id = ANY ('{...}') against a materialized array. 974 buffer hits.

So on this dataset: ~3.8x fewer buffer hits, ~2.9x faster planning, ~4.5x faster execution, same 141 rows both times. The gap should widen further with larger book_statuses sets since the old plan replans the whole union for every additional caller, this one just runs it once.

@ilkka-ollakka

Copy link
Copy Markdown
Contributor

Is there anything about it?

No, just wanted to make sure you had checked it as PR description looked like agentic output of automated PR creation.

@ilkka-ollakka

Copy link
Copy Markdown
Contributor

Here's an explain analyze. I set up a book with 53 comments/quotes/reviews (book_statuses) plus reply threads and unrelated noise statuses, ran the same thread_statuses query both ways, and confirmed the result sets are identical (141 rows, same IDs either way).

Thanks

Old code (book_statuses referenced twice, so the 4-way UNION gets re-embedded and re-planned in both the NOT IN and the IN clause):

Sort  (cost=1890.67..1892.71 rows=813 width=3952) (actual time=4.846..4.892 rows=141 loops=1)
  ->  Hash Join  (cost=814.83..1851.38 rows=813 width=3952) (actual time=2.636..4.787 rows=141 loops=1)
        Hash Cond: (bookwyrm_status.thread_id = "*SELECT* 1".thread_id)
        ...
Planning Time: 6.669 ms
Execution Time: 5.391 ms

Is it possible to add whole explain analyze? If I read your description corretly this query is ran 4 times and each cost is around 1900 totaling to around 7600? While new code has cost of 20k and is ran only once?

8 Subquery Scan nodes total (the 4 UNION branches, duplicated: once for id NOT IN, once for thread_id IN). 3675 buffer hits.

New code (union evaluated once into plain lists):

Sort  (cost=20286.34..20314.80 rows=11381 width=3952) (actual time=1.073..1.084 rows=141 loops=1)
  ->  Nested Loop Left Join  (cost=13.65..185.59 rows=11381 width=3952) (actual time=0.148..0.976 rows=141 loops=1)
        ...
Planning Time: 2.329 ms
Execution Time: 1.198 ms

So on this dataset: ~3.8x fewer buffer hits, ~2.9x faster planning, ~4.5x faster execution, same 141 rows both times. The gap should widen further with larger book_statuses sets since the old plan replans the whole union for every additional caller, this one just runs it once.

Cost increase makes me wonder how well this query change actually performns in scale.

Comment thread bookwyrm/activitystreams.py Outdated
# which is what makes the book-status tasks slow.
book_status_rows = list(book_statuses.values_list("id", "thread_id"))
book_status_ids = [row[0] for row in book_status_rows]
threads = [row[1] for row in book_status_rows]

Copy link
Copy Markdown
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I think you can simplify this (here and below) somewhat by using zip:

book_status_ids, threads = zip(*book_statuses.values_list("id", "thread_id"))

I believe it's slightly more performant, and I find it a little easier to read

Copy link
Copy Markdown
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Applied, thanks. Pushed as aff4b64 along with a guard for the empty book_statuses case (a bare zip(*book_statuses.values_list(...)) throws ValueError when a book has zero comments/quotes/reviews/mentions), plus a regression test for that case.

Per review from mouse-reeve: zip() over the materialized rows is a
little more direct than looping the row list twice to pull out ids
and thread_ids separately.

Guard the empty case explicitly, since a bare
zip(*book_statuses.values_list(...)) raises ValueError when
book_statuses is empty (a book with no comments, quotes, reviews, or
mentions yet), which the list-comprehension version handled fine.
@TemRevil

Copy link
Copy Markdown
Contributor Author

Pushed aff4b64 using your suggestion, zip() over the two list comprehensions. One thing I ran into: a bare zip(*book_statuses.values_list(...)) throws ValueError: not enough values to unpack when book_statuses is empty, which happens for a book with no comments, quotes, reviews, or mentions yet. Kept it as zip(*book_status_rows) if book_status_rows else ((), ()) to cover that, and added a test for exactly that case (a genuinely separate empty Work, my first attempt at that test accidentally shared the parent_work with the populated one and never actually hit the empty path, caught that before trusting it).

On the cost numbers, that reading isn't quite right, but it's a fair thing to get confused by, the EXPLAIN cost display is genuinely counterintuitive here. The old plan's top-level cost (around 1892) is the cost for the whole query, not something run 4 times and summed. Postgres's cost at any node already includes everything under it. The union does show up 8 times in the plan (4 branches, embedded twice, once for the NOT IN check and once for the IN check), but that's already folded into that single 1892 total, not stacked on top of it.

The new plan's higher estimated cost (~20k) is real, but it's the planner's guess before running anything, not a measurement. It comes from how Postgres estimates selectivity for id <> ALL(array) / thread_id = ANY(array) against a literal array, it doesn't have the join statistics it would have for a subquery, so it assumes a less selective filter than what actually happens. The ANALYZE part of EXPLAIN ANALYZE is the real measurement, and there both actual execution time and buffer hits come out lower for the new query (posting both plans in full below, this one and a follow-up comment for the new plan since together they're over GitHub's comment size limit). When the cost estimate and the actual numbers disagree like this, the actual/buffer numbers are what I'd trust.

On scale, that's a fair thing to want more confidence on. book_statuses is bounded by how many comments/quotes/reviews/mentions a single book realistically accumulates, not something that grows with total site activity, so I don't expect the array size itself to become a problem. Happy to test against a much larger set if that would help, let me know roughly what scale you'd want to see.

Old plan below (pre-fix, for comparison), full and unedited:

Old plan
Sort  (cost=1890.67..1892.71 rows=813 width=3952) (actual time=5.000..5.044 rows=141 loops=1)
  Sort Key: bookwyrm_status.published_date DESC
  Sort Method: quicksort  Memory: 44kB
  Buffers: shared hit=3675
  ->  Hash Join  (cost=814.83..1851.38 rows=813 width=3952) (actual time=2.260..4.899 rows=141 loops=1)
        Hash Cond: (bookwyrm_status.thread_id = "*SELECT* 1".thread_id)
        Buffers: shared hit=3675
        ->  Nested Loop Left Join  (cost=407.86..1412.27 rows=11381 width=3952) (actual time=1.108..3.691 rows=197 loops=1)
              Buffers: shared hit=2733
              ->  Nested Loop Left Join  (cost=407.69..1390.92 rows=202 width=3948) (actual time=1.091..3.400 rows=197 loops=1)
                    Buffers: shared hit=2339
                    ->  Nested Loop Left Join  (cost=407.53..1376.09 rows=5 width=3940) (actual time=1.079..3.075 rows=197 loops=1)
                          Buffers: shared hit=1945
                          ->  Nested Loop Left Join  (cost=407.38..1363.80 rows=2 width=3272) (actual time=1.069..2.786 rows=197 loops=1)
                                Buffers: shared hit=1748
                                ->  Nested Loop  (cost=407.23..1355.53 rows=1 width=2728) (actual time=1.061..2.489 rows=197 loops=1)
                                      Join Filter: (bookwyrm_status.user_id = bookwyrm_user.id)
                                      Rows Removed by Join Filter: 879
                                      Buffers: shared hit=1551
                                      ->  Nested Loop Left Join  (cost=407.23..1345.37 rows=1 width=2728) (actual time=1.056..1.510 rows=197 loops=1)
                                            Buffers: shared hit=1157
                                            ->  Seq Scan on bookwyrm_status  (cost=406.93..1336.39 rows=1 width=1672) (actual time=1.047..1.258 rows=197 loops=1)
                                                  Filter: ((NOT deleted) AND (user_id <> 1) AND (NOT (ANY (id = (hashed SubPlan 21).col1))) AND ((privacy)::text = 'public'::text) AND (NOT (ANY (user_id = (hashed SubPlan 2).col1))) AND (NOT (ANY (user_id = (hashed SubPlan 4).col1))))
                                                  Rows Removed by Filter: 83
                                                  Buffers: shared hit=960
                                                  SubPlan 21
                                                    ->  Unique  (cost=406.90..406.92 rows=4 width=4) (actual time=0.973..1.014 rows=53 loops=1)
                                                          Buffers: shared hit=942
                                                          ->  Sort  (cost=406.90..406.91 rows=4 width=4) (actual time=0.973..1.007 rows=53 loops=1)
                                                                Sort Key: "*SELECT* 1_1".id
                                                                Sort Method: quicksort  Memory: 25kB
                                                                Buffers: shared hit=942
                                                                ->  Append  (cost=112.29..406.86 rows=4 width=4) (actual time=0.190..0.977 rows=53 loops=1)
                                                                      Buffers: shared hit=942
                                                                      ->  Subquery Scan on "*SELECT* 1_1"  (cost=112.29..112.30 rows=1 width=4) (actual time=0.190..0.196 rows=13 loops=1)
                                                                            Buffers: shared hit=94
                                                                            ->  Sort  (cost=112.29..112.30 rows=1 width=12) (actual time=0.189..0.193 rows=13 loops=1)
                                                                                  Sort Key: v0_4.id
                                                                                  Sort Method: quicksort  Memory: 25kB
                                                                                  Buffers: shared hit=94
                                                                                  ->  Sort  (cost=112.28..112.28 rows=1 width=12) (actual time=0.180..0.184 rows=13 loops=1)
                                                                                        Sort Key: v0_4.published_date DESC
                                                                                        Sort Method: quicksort  Memory: 25kB
                                                                                        Buffers: shared hit=94
                                                                                        ->  Nested Loop  (cost=0.56..112.27 rows=1 width=12) (actual time=0.057..0.170 rows=13 loops=1)
                                                                                              Join Filter: (v0_4.user_id = v1_4.id)
                                                                                              Rows Removed by Join Filter: 57
                                                                                              Buffers: shared hit=94
                                                                                              ->  Nested Loop  (cost=0.56..102.11 rows=1 width=16) (actual time=0.052..0.114 rows=13 loops=1)
                                                                                                    Buffers: shared hit=68
                                                                                                    ->  Nested Loop  (cost=0.28..16.33 rows=5 width=4) (actual time=0.014..0.021 rows=20 loops=1)
                                                                                                          Buffers: shared hit=4
                                                                                                          ->  Index Scan using bookwyrm_ed_parent__c4f87c_idx on bookwyrm_edition v3_4  (cost=0.14..8.15 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)
                                                                                                                Index Cond: (parent_work_id = 1)
                                                                                                                Buffers: shared hit=2
                                                                                                          ->  Index Scan using bookwyrm_comment_book_id_084ee59b on bookwyrm_comment v2_4  (cost=0.14..8.16 rows=1 width=8) (actual time=0.006..0.010 rows=20 loops=1)
                                                                                                                Index Cond: (book_id = v3_4.book_ptr_id)
                                                                                                                Buffers: shared hit=2
                                                                                                    ->  Memoize  (cost=0.28..17.14 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=20)
                                                                                                          Cache Key: v2_4.status_ptr_id
                                                                                                          Cache Mode: logical
                                                                                                          Hits: 0  Misses: 20  Evictions: 0  Overflows: 0  Memory Usage: 2kB
                                                                                                          Buffers: shared hit=64
                                                                                                          ->  Index Scan using bookwyrm_status_pkey on bookwyrm_status v0_4  (cost=0.27..17.13 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=20)
                                                                                                                Index Cond: (id = v2_4.status_ptr_id)
                                                                                                                Filter: ((NOT deleted) AND (user_id <> 1) AND ((privacy)::text = 'public'::text) AND (NOT (ANY (user_id = (hashed SubPlan 6).col1))) AND (NOT (ANY (user_id = (hashed SubPlan 8).col1))))
                                                                                                                Rows Removed by Filter: 0
                                                                                                                Buffers: shared hit=64
                                                                                                                SubPlan 6
                                                                                                                  ->  Index Scan using bookwyrm_userblocks_user_subject_id_52f9bc38 on bookwyrm_userblocks u2_2  (cost=0.14..8.16 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)
                                                                                                                        Index Cond: (user_subject_id = 1)
                                                                                                                        Buffers: shared hit=2
                                                                                                                SubPlan 8
                                                                                                                  ->  Index Scan using bookwyrm_userblocks_user_object_id_ff33ba18 on bookwyrm_userblocks u2_3  (cost=0.14..8.16 rows=1 width=4) (actual time=0.006..0.007 rows=0 loops=1)
                                                                                                                        Index Cond: (user_object_id = 1)
                                                                                                                        Buffers: shared hit=2
                                                                                              ->  Seq Scan on bookwyrm_user v1_4  (cost=0.00..10.10 rows=5 width=4) (actual time=0.002..0.003 rows=5 loops=13)
                                                                                                    Filter: is_active
                                                                                                    Buffers: shared hit=26
                                                                      ->  Subquery Scan on "*SELECT* 2_1"  (cost=95.70..95.70 rows=1 width=4) (actual time=0.190..0.196 rows=20 loops=1)
                                                                            Buffers: shared hit=108
                                                                            ->  Sort  (cost=95.70..95.70 rows=1 width=12) (actual time=0.189..0.194 rows=20 loops=1)
                                                                                  Sort Key: v0_5.id
                                                                                  Sort Method: quicksort  Memory: 25kB
                                                                                  Buffers: shared hit=108
                                                                                  ->  Sort  (cost=95.68..95.69 rows=1 width=12) (actual time=0.169..0.172 rows=20 loops=1)
                                                                                        Sort Key: v0_5.published_date DESC
                                                                                        Sort Method: quicksort  Memory: 25kB
                                                                                        Buffers: shared hit=108
                                                                                        ->  Nested Loop  (cost=0.56..95.67 rows=1 width=12) (actual time=0.048..0.152 rows=20 loops=1)
                                                                                              Join Filter: (v0_5.user_id = v1_5.id)
                                                                                              Rows Removed by Join Filter: 77
                                                                                              Buffers: shared hit=108
                                                                                              ->  Nested Loop  (cost=0.56..85.51 rows=1 width=16) (actual time=0.044..0.091 rows=20 loops=1)
                                                                                                    Buffers: shared hit=68
                                                                                                    ->  Nested Loop  (cost=0.28..16.33 rows=4 width=4) (actual time=0.017..0.024 rows=20 loops=1)
                                                                                                          Buffers: shared hit=4
                                                                                                          ->  Index Scan using bookwyrm_ed_parent__c4f87c_idx on bookwyrm_edition v3_5  (cost=0.14..8.15 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=1)
                                                                                                                Index Cond: (parent_work_id = 1)
                                                                                                                Buffers: shared hit=2
                                                                                                          ->  Index Scan using bookwyrm_quotation_book_id_22093e34 on bookwyrm_quotation v2_5  (cost=0.14..8.16 rows=1 width=8) (actual time=0.006..0.009 rows=20 loops=1)
                                                                                                                Index Cond: (book_id = v3_5.book_ptr_id)
                                                                                                                Buffers: shared hit=2
                                                                                                    ->  Memoize  (cost=0.28..17.28 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=20)
                                                                                                          Cache Key: v2_5.status_ptr_id
                                                                                                          Cache Mode: logical
                                                                                                          Hits: 0  Misses: 20  Evictions: 0  Overflows: 0  Memory Usage: 3kB
                                                                                                          Buffers: shared hit=64
                                                                                                          ->  Index Scan using bookwyrm_status_pkey on bookwyrm_status v0_5  (cost=0.27..17.27 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=20)
                                                                                                                Index Cond: (id = v2_5.status_ptr_id)
                                                                                                                Filter: ((NOT deleted) AND (user_id <> 1) AND ((privacy)::text = 'public'::text) AND (NOT (ANY (user_id = (hashed SubPlan 10).col1))) AND (NOT (ANY (user_id = (hashed SubPlan 12).col1))))
                                                                                                                Buffers: shared hit=64
                                                                                                                SubPlan 10
                                                                                                                  ->  Index Scan using bookwyrm_userblocks_user_subject_id_52f9bc38 on bookwyrm_userblocks u2_4  (cost=0.14..8.16 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)
                                                                                                                        Index Cond: (user_subject_id = 1)
                                                                                                                        Buffers: shared hit=2
                                                                                                                SubPlan 12
                                                                                                                  ->  Index Scan using bookwyrm_userblocks_user_object_id_ff33ba18 on bookwyrm_userblocks u2_5  (cost=0.14..8.16 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)
                                                                                                                        Index Cond: (user_object_id = 1)
                                                                                                                        Buffers: shared hit=2
                                                                                              ->  Seq Scan on bookwyrm_user v1_5  (cost=0.00..10.10 rows=5 width=4) (actual time=0.001..0.002 rows=5 loops=20)
                                                                                                    Filter: is_active
                                                                                                    Buffers: shared hit=40
                                                                      ->  Subquery Scan on "*SELECT* 3_1"  (cost=61.85..61.85 rows=1 width=4) (actual time=0.177..0.184 rows=20 loops=1)
                                                                            Buffers: shared hit=111
                                                                            ->  Sort  (cost=61.85..61.85 rows=1 width=12) (actual time=0.177..0.180 rows=20 loops=1)
                                                                                  Sort Key: v0_6.id
                                                                                  Sort Method: quicksort  Memory: 25kB
                                                                                  Buffers: shared hit=111
                                                                                  ->  Sort  (cost=61.83..61.84 rows=1 width=12) (actual time=0.164..0.168 rows=20 loops=1)
                                                                                        Sort Key: v0_6.published_date DESC
                                                                                        Sort Method: quicksort  Memory: 25kB
                                                                                        Buffers: shared hit=111
                                                                                        ->  Nested Loop  (cost=0.56..61.82 rows=1 width=12) (actual time=0.047..0.155 rows=20 loops=1)
                                                                                              Join Filter: (v0_6.user_id = v1_6.id)
                                                                                              Rows Removed by Join Filter: 97
                                                                                              Buffers: shared hit=111
                                                                                              ->  Nested Loop  (cost=0.56..51.66 rows=1 width=16) (actual time=0.042..0.091 rows=20 loops=1)
                                                                                                    Buffers: shared hit=71
                                                                                                    ->  Nested Loop  (cost=0.28..16.33 rows=2 width=4) (actual time=0.014..0.021 rows=20 loops=1)
                                                                                                          Buffers: shared hit=4
                                                                                                          ->  Index Scan using bookwyrm_ed_parent__c4f87c_idx on bookwyrm_edition v3_6  (cost=0.14..8.15 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=1)
                                                                                                                Index Cond: (parent_work_id = 1)
                                                                                                                Buffers: shared hit=2
                                                                                                          ->  Index Scan using bookwyrm_review_book_id_26b3aead on bookwyrm_review v2_6  (cost=0.14..8.16 rows=1 width=8) (actual time=0.008..0.012 rows=20 loops=1)
                                                                                                                Index Cond: (book_id = v3_6.book_ptr_id)
                                                                                                                Buffers: shared hit=2
                                                                                                    ->  Memoize  (cost=0.28..17.66 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=20)
                                                                                                          Cache Key: v2_6.status_ptr_id
                                                                                                          Cache Mode: logical
                                                                                                          Hits: 0  Misses: 20  Evictions: 0  Overflows: 0  Memory Usage: 3kB
                                                                                                          Buffers: shared hit=67
                                                                                                          ->  Index Scan using bookwyrm_status_pkey on bookwyrm_status v0_6  (cost=0.27..17.65 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=20)
                                                                                                                Index Cond: (id = v2_6.status_ptr_id)
                                                                                                                Filter: ((NOT deleted) AND (user_id <> 1) AND ((privacy)::text = 'public'::text) AND (NOT (ANY (user_id = (hashed SubPlan 14).col1))) AND (NOT (ANY (user_id = (hashed SubPlan 16).col1))))
                                                                                                                Buffers: shared hit=67
                                                                                                                SubPlan 14
                                                                                                                  ->  Index Scan using bookwyrm_userblocks_user_subject_id_52f9bc38 on bookwyrm_userblocks u2_6  (cost=0.14..8.16 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)
                                                                                                                        Index Cond: (user_subject_id = 1)
                                                                                                                        Buffers: shared hit=2
                                                                                                                SubPlan 16
                                                                                                                  ->  Index Scan using bookwyrm_userblocks_user_object_id_ff33ba18 on bookwyrm_userblocks u2_7  (cost=0.14..8.16 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)
                                                                                                                        Index Cond: (user_object_id = 1)
                                                                                                                        Buffers: shared hit=2
                                                                                              ->  Seq Scan on bookwyrm_user v1_6  (cost=0.00..10.10 rows=5 width=4) (actual time=0.001..0.002 rows=6 loops=20)
                                                                                                    Filter: is_active
                                                                                                    Buffers: shared hit=40
                                                                      ->  Subquery Scan on "*SELECT* 4_1"  (cost=136.98..136.99 rows=1 width=4) (actual time=0.394..0.397 rows=0 loops=1)
                                                                            Buffers: shared hit=629
                                                                            ->  Sort  (cost=136.98..136.99 rows=1 width=12) (actual time=0.394..0.396 rows=0 loops=1)
                                                                                  Sort Key: v0_7.id
                                                                                  Sort Method: quicksort  Memory: 25kB
                                                                                  Buffers: shared hit=629
                                                                                  ->  Sort  (cost=136.97..136.97 rows=1 width=12) (actual time=0.376..0.378 rows=0 loops=1)
                                                                                        Sort Key: v0_7.published_date DESC
                                                                                        Sort Method: quicksort  Memory: 25kB
                                                                                        Buffers: shared hit=629
                                                                                        ->  Nested Loop  (cost=0.43..136.96 rows=1 width=12) (actual time=0.368..0.370 rows=0 loops=1)
                                                                                              Buffers: shared hit=629
                                                                                              ->  Index Scan using bookwyrm_ed_parent__c4f87c_idx on bookwyrm_edition v3_7  (cost=0.14..8.15 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1)
                                                                                                    Index Cond: (parent_work_id = 1)
                                                                                                    Buffers: shared hit=2
                                                                                              ->  Nested Loop  (cost=0.29..128.79 rows=1 width=16) (actual time=0.360..0.362 rows=0 loops=1)
                                                                                                    Buffers: shared hit=627
                                                                                                    ->  Nested Loop  (cost=0.14..120.61 rows=1 width=12) (actual time=0.045..0.201 rows=250 loops=1)
                                                                                                          Buffers: shared hit=127
                                                                                                          ->  Seq Scan on bookwyrm_user v1_7  (cost=0.00..10.10 rows=5 width=4) (actual time=0.005..0.007 rows=9 loops=1)
                                                                                                                Filter: is_active
                                                                                                                Buffers: shared hit=2
                                                                                                          ->  Index Scan using bookwyrm_status_user_id_b343e19b on bookwyrm_status v0_7  (cost=0.14..22.09 rows=1 width=16) (actual time=0.006..0.019 rows=28 loops=9)
                                                                                                                Index Cond: (user_id = v1_7.id)
                                                                                                                Filter: ((NOT deleted) AND (user_id <> 1) AND ((privacy)::text = 'public'::text) AND (NOT (ANY (user_id = (hashed SubPlan 18).col1))) AND (NOT (ANY (user_id = (hashed SubPlan 20).col1))))
                                                                                                                Rows Removed by Filter: 3
                                                                                                                Buffers: shared hit=125
                                                                                                                SubPlan 18
                                                                                                                  ->  Index Scan using bookwyrm_userblocks_user_subject_id_52f9bc38 on bookwyrm_userblocks u2_8  (cost=0.14..8.16 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)
                                                                                                                        Index Cond: (user_subject_id = 1)
                                                                                                                        Buffers: shared hit=2
                                                                                                                SubPlan 20
                                                                                                                  ->  Index Scan using bookwyrm_userblocks_user_object_id_ff33ba18 on bookwyrm_userblocks u2_9  (cost=0.14..8.16 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)
                                                                                                                        Index Cond: (user_object_id = 1)
                                                                                                                        Buffers: shared hit=2
                                                                                                    ->  Index Only Scan using bookwyrm_status_mention_books_status_id_book_id_369b696c_uniq on bookwyrm_status_mention_books v2_7  (cost=0.15..8.17 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=250)
                                                                                                          Index Cond: ((status_id = v0_7.id) AND (edition_id = v3_7.book_ptr_id))
                                                                                                          Heap Fetches: 0
                                                                                                          Buffers: shared hit=500
                                                  SubPlan 2
                                                    ->  Index Scan using bookwyrm_userblocks_user_subject_id_52f9bc38 on bookwyrm_userblocks u2  (cost=0.14..8.16 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)
                                                          Index Cond: (user_subject_id = 1)
                                                          Buffers: shared hit=2
                                                  SubPlan 4
                                                    ->  Index Scan using bookwyrm_userblocks_user_object_id_ff33ba18 on bookwyrm_userblocks u2_1  (cost=0.14..8.16 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)
                                                          Index Cond: (user_object_id = 1)
                                                          Buffers: shared hit=2
                                            ->  Nested Loop Left Join  (cost=0.30..8.96 rows=1 width=1056) (actual time=0.001..0.001 rows=0 loops=197)
                                                  Buffers: shared hit=197
                                                  ->  Index Scan using bookwyrm_review_pkey on bookwyrm_review  (cost=0.14..8.16 rows=1 width=1052) (actual time=0.001..0.001 rows=0 loops=197)
                                                        Index Cond: (status_ptr_id = bookwyrm_status.id)
                                                        Buffers: shared hit=197
                                                  ->  Index Only Scan using bookwyrm_reviewrating_pkey on bookwyrm_reviewrating  (cost=0.15..0.80 rows=1 width=4) (never executed)
                                                        Index Cond: (review_ptr_id = bookwyrm_review.status_ptr_id)
                                                        Heap Fetches: 0
                                      ->  Seq Scan on bookwyrm_user  (cost=0.00..10.10 rows=5 width=4) (actual time=0.002..0.003 rows=5 loops=197)
                                            Filter: is_active
                                            Buffers: shared hit=394
                                ->  Index Scan using bookwyrm_comment_pkey on bookwyrm_comment  (cost=0.14..8.16 rows=1 width=544) (actual time=0.001..0.001 rows=0 loops=197)
                                      Index Cond: (status_ptr_id = bookwyrm_status.id)
                                      Buffers: shared hit=197
                          ->  Memoize  (cost=0.15..8.17 rows=1 width=668) (actual time=0.001..0.001 rows=0 loops=197)
                                Cache Key: bookwyrm_status.id
                                Cache Mode: logical
                                Hits: 0  Misses: 197  Evictions: 0  Overflows: 0  Memory Usage: 14kB
                                Buffers: shared hit=197
                                ->  Index Scan using bookwyrm_quotation_pkey on bookwyrm_quotation  (cost=0.14..8.16 rows=1 width=668) (actual time=0.000..0.000 rows=0 loops=197)
                                      Index Cond: (status_ptr_id = bookwyrm_status.id)
                                      Buffers: shared hit=197
                    ->  Memoize  (cost=0.17..8.18 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=197)
                          Cache Key: bookwyrm_status.id
                          Cache Mode: logical
                          Hits: 0  Misses: 197  Evictions: 0  Overflows: 0  Memory Usage: 14kB
                          Buffers: shared hit=394
                          ->  Index Scan using bookwyrm_boost_pkey on bookwyrm_boost  (cost=0.15..8.17 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=197)
                                Index Cond: (status_ptr_id = bookwyrm_status.id)
                                Buffers: shared hit=394
              ->  Memoize  (cost=0.17..8.18 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=197)
                    Cache Key: bookwyrm_status.id
                    Cache Mode: logical
                    Hits: 0  Misses: 197  Evictions: 0  Overflows: 0  Memory Usage: 14kB
                    Buffers: shared hit=394
                    ->  Index Only Scan using bookwyrm_generatedstatus_pkey on bookwyrm_generatednote  (cost=0.15..8.17 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=197)
                          Index Cond: (status_ptr_id = bookwyrm_status.id)
                          Heap Fetches: 0
                          Buffers: shared hit=394
        ->  Hash  (cost=406.92..406.92 rows=4 width=4) (actual time=1.091..1.108 rows=53 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 10kB
              Buffers: shared hit=942
              ->  Unique  (cost=406.90..406.92 rows=4 width=4) (actual time=1.072..1.098 rows=53 loops=1)
                    Buffers: shared hit=942
                    ->  Sort  (cost=406.90..406.91 rows=4 width=4) (actual time=1.072..1.090 rows=53 loops=1)
                          Sort Key: "*SELECT* 1".thread_id
                          Sort Method: quicksort  Memory: 25kB
                          Buffers: shared hit=942
                          ->  Append  (cost=112.29..406.86 rows=4 width=4) (actual time=0.237..1.073 rows=53 loops=1)
                                Buffers: shared hit=942
                                ->  Subquery Scan on "*SELECT* 1"  (cost=112.29..112.30 rows=1 width=4) (actual time=0.237..0.242 rows=13 loops=1)
                                      Buffers: shared hit=94
                                      ->  Sort  (cost=112.29..112.30 rows=1 width=12) (actual time=0.236..0.239 rows=13 loops=1)
                                            Sort Key: v0.thread_id
                                            Sort Method: quicksort  Memory: 25kB
                                            Buffers: shared hit=94
                                            ->  Sort  (cost=112.28..112.28 rows=1 width=12) (actual time=0.224..0.227 rows=13 loops=1)
                                                  Sort Key: v0.published_date DESC
                                                  Sort Method: quicksort  Memory: 25kB
                                                  Buffers: shared hit=94
                                                  ->  Nested Loop  (cost=0.56..112.27 rows=1 width=12) (actual time=0.106..0.215 rows=13 loops=1)
                                                        Join Filter: (v0.user_id = v1.id)
                                                        Rows Removed by Join Filter: 57
                                                        Buffers: shared hit=94
                                                        ->  Nested Loop  (cost=0.56..102.11 rows=1 width=16) (actual time=0.093..0.149 rows=13 loops=1)
                                                              Buffers: shared hit=68
                                                              ->  Nested Loop  (cost=0.28..16.33 rows=5 width=4) (actual time=0.033..0.041 rows=20 loops=1)
                                                                    Buffers: shared hit=4
                                                                    ->  Index Scan using bookwyrm_ed_parent__c4f87c_idx on bookwyrm_edition v3  (cost=0.14..8.15 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=1)
                                                                          Index Cond: (parent_work_id = 1)
                                                                          Buffers: shared hit=2
                                                                    ->  Index Scan using bookwyrm_comment_book_id_084ee59b on bookwyrm_comment v2  (cost=0.14..8.16 rows=1 width=8) (actual time=0.011..0.016 rows=20 loops=1)
                                                                          Index Cond: (book_id = v3.book_ptr_id)
                                                                          Buffers: shared hit=2
                                                              ->  Memoize  (cost=0.28..17.14 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=20)
                                                                    Cache Key: v2.status_ptr_id
                                                                    Cache Mode: logical
                                                                    Hits: 0  Misses: 20  Evictions: 0  Overflows: 0  Memory Usage: 3kB
                                                                    Buffers: shared hit=64
                                                                    ->  Index Scan using bookwyrm_status_pkey on bookwyrm_status v0  (cost=0.27..17.13 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=20)
                                                                          Index Cond: (id = v2.status_ptr_id)
                                                                          Filter: ((NOT deleted) AND (user_id <> 1) AND ((privacy)::text = 'public'::text) AND (NOT (ANY (user_id = (hashed SubPlan 23).col1))) AND (NOT (ANY (user_id = (hashed SubPlan 25).col1))))
                                                                          Rows Removed by Filter: 0
                                                                          Buffers: shared hit=64
                                                                          SubPlan 23
                                                                            ->  Index Scan using bookwyrm_userblocks_user_subject_id_52f9bc38 on bookwyrm_userblocks u2_10  (cost=0.14..8.16 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=1)
                                                                                  Index Cond: (user_subject_id = 1)
                                                                                  Buffers: shared hit=2
                                                                          SubPlan 25
                                                                            ->  Index Scan using bookwyrm_userblocks_user_object_id_ff33ba18 on bookwyrm_userblocks u2_11  (cost=0.14..8.16 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)
                                                                                  Index Cond: (user_object_id = 1)
                                                                                  Buffers: shared hit=2
                                                        ->  Seq Scan on bookwyrm_user v1  (cost=0.00..10.10 rows=5 width=4) (actual time=0.002..0.002 rows=5 loops=13)
                                                              Filter: is_active
                                                              Buffers: shared hit=26
                                ->  Subquery Scan on "*SELECT* 2"  (cost=95.70..95.70 rows=1 width=4) (actual time=0.194..0.200 rows=20 loops=1)
                                      Buffers: shared hit=108
                                      ->  Sort  (cost=95.70..95.70 rows=1 width=12) (actual time=0.194..0.198 rows=20 loops=1)
                                            Sort Key: v0_1.thread_id
                                            Sort Method: quicksort  Memory: 25kB
                                            Buffers: shared hit=108
                                            ->  Sort  (cost=95.68..95.69 rows=1 width=12) (actual time=0.178..0.183 rows=20 loops=1)
                                                  Sort Key: v0_1.published_date DESC
                                                  Sort Method: quicksort  Memory: 25kB
                                                  Buffers: shared hit=108
                                                  ->  Nested Loop  (cost=0.56..95.67 rows=1 width=12) (actual time=0.050..0.164 rows=20 loops=1)
                                                        Join Filter: (v0_1.user_id = v1_1.id)
                                                        Rows Removed by Join Filter: 77
                                                        Buffers: shared hit=108
                                                        ->  Nested Loop  (cost=0.56..85.51 rows=1 width=16) (actual time=0.044..0.098 rows=20 loops=1)
                                                              Buffers: shared hit=68
                                                              ->  Nested Loop  (cost=0.28..16.33 rows=4 width=4) (actual time=0.013..0.020 rows=20 loops=1)
                                                                    Buffers: shared hit=4
                                                                    ->  Index Scan using bookwyrm_ed_parent__c4f87c_idx on bookwyrm_edition v3_1  (cost=0.14..8.15 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=1)
                                                                          Index Cond: (parent_work_id = 1)
                                                                          Buffers: shared hit=2
                                                                    ->  Index Scan using bookwyrm_quotation_book_id_22093e34 on bookwyrm_quotation v2_1  (cost=0.14..8.16 rows=1 width=8) (actual time=0.007..0.011 rows=20 loops=1)
                                                                          Index Cond: (book_id = v3_1.book_ptr_id)
                                                                          Buffers: shared hit=2
                                                              ->  Memoize  (cost=0.28..17.28 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=20)
                                                                    Cache Key: v2_1.status_ptr_id
                                                                    Cache Mode: logical
                                                                    Hits: 0  Misses: 20  Evictions: 0  Overflows: 0  Memory Usage: 3kB
                                                                    Buffers: shared hit=64
                                                                    ->  Index Scan using bookwyrm_status_pkey on bookwyrm_status v0_1  (cost=0.27..17.27 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=20)
                                                                          Index Cond: (id = v2_1.status_ptr_id)
                                                                          Filter: ((NOT deleted) AND (user_id <> 1) AND ((privacy)::text = 'public'::text) AND (NOT (ANY (user_id = (hashed SubPlan 27).col1))) AND (NOT (ANY (user_id = (hashed SubPlan 29).col1))))
                                                                          Buffers: shared hit=64
                                                                          SubPlan 27
                                                                            ->  Index Scan using bookwyrm_userblocks_user_subject_id_52f9bc38 on bookwyrm_userblocks u2_12  (cost=0.14..8.16 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)
                                                                                  Index Cond: (user_subject_id = 1)
                                                                                  Buffers: shared hit=2
                                                                          SubPlan 29
                                                                            ->  Index Scan using bookwyrm_userblocks_user_object_id_ff33ba18 on bookwyrm_userblocks u2_13  (cost=0.14..8.16 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)
                                                                                  Index Cond: (user_object_id = 1)
                                                                                  Buffers: shared hit=2
                                                        ->  Seq Scan on bookwyrm_user v1_1  (cost=0.00..10.10 rows=5 width=4) (actual time=0.001..0.002 rows=5 loops=20)
                                                              Filter: is_active
                                                              Buffers: shared hit=40
                                ->  Subquery Scan on "*SELECT* 3"  (cost=61.85..61.85 rows=1 width=4) (actual time=0.232..0.237 rows=20 loops=1)
                                      Buffers: shared hit=111
                                      ->  Sort  (cost=61.85..61.85 rows=1 width=12) (actual time=0.231..0.235 rows=20 loops=1)
                                            Sort Key: v0_2.thread_id
                                            Sort Method: quicksort  Memory: 25kB
                                            Buffers: shared hit=111
                                            ->  Sort  (cost=61.83..61.84 rows=1 width=12) (actual time=0.220..0.224 rows=20 loops=1)
                                                  Sort Key: v0_2.published_date DESC
                                                  Sort Method: quicksort  Memory: 25kB
                                                  Buffers: shared hit=111
                                                  ->  Nested Loop  (cost=0.56..61.82 rows=1 width=12) (actual time=0.077..0.208 rows=20 loops=1)
                                                        Join Filter: (v0_2.user_id = v1_2.id)
                                                        Rows Removed by Join Filter: 97
                                                        Buffers: shared hit=111
                                                        ->  Nested Loop  (cost=0.56..51.66 rows=1 width=16) (actual time=0.072..0.127 rows=20 loops=1)
                                                              Buffers: shared hit=71
                                                              ->  Nested Loop  (cost=0.28..16.33 rows=2 width=4) (actual time=0.020..0.027 rows=20 loops=1)
                                                                    Buffers: shared hit=4
                                                                    ->  Index Scan using bookwyrm_ed_parent__c4f87c_idx on bookwyrm_edition v3_2  (cost=0.14..8.15 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)
                                                                          Index Cond: (parent_work_id = 1)
                                                                          Buffers: shared hit=2
                                                                    ->  Index Scan using bookwyrm_review_book_id_26b3aead on bookwyrm_review v2_2  (cost=0.14..8.16 rows=1 width=8) (actual time=0.009..0.013 rows=20 loops=1)
                                                                          Index Cond: (book_id = v3_2.book_ptr_id)
                                                                          Buffers: shared hit=2
                                                              ->  Memoize  (cost=0.28..17.66 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=20)
                                                                    Cache Key: v2_2.status_ptr_id
                                                                    Cache Mode: logical
                                                                    Hits: 0  Misses: 20  Evictions: 0  Overflows: 0  Memory Usage: 3kB
                                                                    Buffers: shared hit=67
                                                                    ->  Index Scan using bookwyrm_status_pkey on bookwyrm_status v0_2  (cost=0.27..17.65 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=20)
                                                                          Index Cond: (id = v2_2.status_ptr_id)
                                                                          Filter: ((NOT deleted) AND (user_id <> 1) AND ((privacy)::text = 'public'::text) AND (NOT (ANY (user_id = (hashed SubPlan 31).col1))) AND (NOT (ANY (user_id = (hashed SubPlan 33).col1))))
                                                                          Buffers: shared hit=67
                                                                          SubPlan 31
                                                                            ->  Index Scan using bookwyrm_userblocks_user_subject_id_52f9bc38 on bookwyrm_userblocks u2_14  (cost=0.14..8.16 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)
                                                                                  Index Cond: (user_subject_id = 1)
                                                                                  Buffers: shared hit=2
                                                                          SubPlan 33
                                                                            ->  Index Scan using bookwyrm_userblocks_user_object_id_ff33ba18 on bookwyrm_userblocks u2_15  (cost=0.14..8.16 rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=1)
                                                                                  Index Cond: (user_object_id = 1)
                                                                                  Buffers: shared hit=2
                                                        ->  Seq Scan on bookwyrm_user v1_2  (cost=0.00..10.10 rows=5 width=4) (actual time=0.002..0.002 rows=6 loops=20)
                                                              Filter: is_active
                                                              Buffers: shared hit=40
                                ->  Subquery Scan on "*SELECT* 4"  (cost=136.98..136.99 rows=1 width=4) (actual time=0.385..0.389 rows=0 loops=1)
                                      Buffers: shared hit=629
                                      ->  Sort  (cost=136.98..136.99 rows=1 width=12) (actual time=0.385..0.388 rows=0 loops=1)
                                            Sort Key: v0_3.thread_id
                                            Sort Method: quicksort  Memory: 25kB
                                            Buffers: shared hit=629
                                            ->  Sort  (cost=136.97..136.97 rows=1 width=12) (actual time=0.372..0.375 rows=0 loops=1)
                                                  Sort Key: v0_3.published_date DESC
                                                  Sort Method: quicksort  Memory: 25kB
                                                  Buffers: shared hit=629
                                                  ->  Nested Loop  (cost=0.43..136.96 rows=1 width=12) (actual time=0.361..0.364 rows=0 loops=1)
                                                        Buffers: shared hit=629
                                                        ->  Index Scan using bookwyrm_ed_parent__c4f87c_idx on bookwyrm_edition v3_3  (cost=0.14..8.15 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=1)
                                                              Index Cond: (parent_work_id = 1)
                                                              Buffers: shared hit=2
                                                        ->  Nested Loop  (cost=0.29..128.79 rows=1 width=16) (actual time=0.355..0.357 rows=0 loops=1)
                                                              Buffers: shared hit=627
                                                              ->  Nested Loop  (cost=0.14..120.61 rows=1 width=16) (actual time=0.066..0.217 rows=250 loops=1)
                                                                    Buffers: shared hit=127
                                                                    ->  Seq Scan on bookwyrm_user v1_3  (cost=0.00..10.10 rows=5 width=4) (actual time=0.005..0.007 rows=9 loops=1)
                                                                          Filter: is_active
                                                                          Buffers: shared hit=2
                                                                    ->  Index Scan using bookwyrm_status_user_id_b343e19b on bookwyrm_status v0_3  (cost=0.14..22.09 rows=1 width=20) (actual time=0.008..0.021 rows=28 loops=9)
                                                                          Index Cond: (user_id = v1_3.id)
                                                                          Filter: ((NOT deleted) AND (user_id <> 1) AND ((privacy)::text = 'public'::text) AND (NOT (ANY (user_id = (hashed SubPlan 35).col1))) AND (NOT (ANY (user_id = (hashed SubPlan 37).col1))))
                                                                          Rows Removed by Filter: 3
                                                                          Buffers: shared hit=125
                                                                          SubPlan 35
                                                                            ->  Index Scan using bookwyrm_userblocks_user_subject_id_52f9bc38 on bookwyrm_userblocks u2_16  (cost=0.14..8.16 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)
                                                                                  Index Cond: (user_subject_id = 1)
                                                                                  Buffers: shared hit=2
                                                                          SubPlan 37
                                                                            ->  Index Scan using bookwyrm_userblocks_user_object_id_ff33ba18 on bookwyrm_userblocks u2_17  (cost=0.14..8.16 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=1)
                                                                                  Index Cond: (user_object_id = 1)
                                                                                  Buffers: shared hit=2
                                                              ->  Index Only Scan using bookwyrm_status_mention_books_status_id_book_id_369b696c_uniq on bookwyrm_status_mention_books v2_3  (cost=0.15..8.17 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=250)
                                                                    Index Cond: ((status_id = v0_3.id) AND (edition_id = v3_3.book_ptr_id))
                                                                    Heap Fetches: 0
                                                                    Buffers: shared hit=500
Planning:
  Buffers: shared hit=207
Planning Time: 7.004 ms
Execution Time: 6.379 ms

New plan (current pushed code, with the zip fix) in the next comment.

@TemRevil

Copy link
Copy Markdown
Contributor Author

New plan, continued from above, full and unedited:

New plan
Sort  (cost=20286.34..20314.80 rows=11381 width=3952) (actual time=1.022..1.030 rows=141 loops=1)
  Sort Key: bookwyrm_status.published_date DESC
  Sort Method: quicksort  Memory: 44kB
  Buffers: shared hit=974
  ->  Nested Loop Left Join  (cost=13.65..185.59 rows=11381 width=3952) (actual time=0.181..0.919 rows=141 loops=1)
        Buffers: shared hit=974
        ->  Hash Left Join  (cost=13.49..164.24 rows=202 width=3948) (actual time=0.176..0.757 rows=141 loops=1)
              Hash Cond: (bookwyrm_status.id = bookwyrm_quotation.status_ptr_id)
              Buffers: shared hit=692
              ->  Nested Loop Left Join  (cost=1.01..151.54 rows=81 width=3280) (actual time=0.122..0.669 rows=141 loops=1)
                    Buffers: shared hit=691
                    ->  Nested Loop Left Join  (cost=0.85..139.24 rows=2 width=3272) (actual time=0.112..0.517 rows=141 loops=1)
                          Buffers: shared hit=409
                          ->  Nested Loop Left Join  (cost=0.70..130.96 rows=1 width=2728) (actual time=0.108..0.413 rows=141 loops=1)
                                Buffers: shared hit=268
                                ->  Nested Loop  (cost=0.41..121.99 rows=1 width=1672) (actual time=0.102..0.290 rows=141 loops=1)
                                      Buffers: shared hit=127
                                      ->  Seq Scan on bookwyrm_user  (cost=0.00..10.10 rows=5 width=4) (actual time=0.014..0.017 rows=9 loops=1)
                                            Filter: is_active
                                            Buffers: shared hit=2
                                      ->  Index Scan using bookwyrm_status_user_id_b343e19b on bookwyrm_status  (cost=0.41..22.37 rows=1 width=1672) (actual time=0.011..0.028 rows=16 loops=9)
                                            Index Cond: (user_id = bookwyrm_user.id)
                                            Filter: ((NOT deleted) AND (user_id <> 1) AND ((privacy)::text = 'public'::text) AND (id <> ALL ('{5,9,13,17,21,25,29,33,41,45,49,53,57,61,65,69,77,81,85,89,93,97,101,105,113,117,121,125,129,133,137,141,149,153,157,161,165,169,173,177,185,189,193,197,201,205,209,213,221,225,229,233,237}'::integer[])) AND (thread_id = ANY ('{5,9,13,17,21,25,29,33,41,45,49,53,57,61,65,69,77,81,85,89,93,97,101,105,113,117,121,125,129,133,137,141,149,153,157,161,165,169,173,177,185,189,193,197,201,205,209,213,221,225,229,233,237}'::integer[])) AND (NOT (ANY (user_id = (hashed SubPlan 2).col1))) AND (NOT (ANY (user_id = (hashed SubPlan 4).col1))))
                                            Rows Removed by Filter: 15
                                            Buffers: shared hit=125
                                            SubPlan 2
                                              ->  Index Scan using bookwyrm_userblocks_user_subject_id_52f9bc38 on bookwyrm_userblocks u2  (cost=0.14..8.16 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)
                                                    Index Cond: (user_subject_id = 1)
                                                    Buffers: shared hit=2
                                            SubPlan 4
                                              ->  Index Scan using bookwyrm_userblocks_user_object_id_ff33ba18 on bookwyrm_userblocks u2_1  (cost=0.14..8.16 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)
                                                    Index Cond: (user_object_id = 1)
                                                    Buffers: shared hit=2
                                ->  Nested Loop Left Join  (cost=0.30..8.96 rows=1 width=1056) (actual time=0.001..0.001 rows=0 loops=141)
                                      Buffers: shared hit=141
                                      ->  Index Scan using bookwyrm_review_pkey on bookwyrm_review  (cost=0.14..8.16 rows=1 width=1052) (actual time=0.000..0.000 rows=0 loops=141)
                                            Index Cond: (status_ptr_id = bookwyrm_status.id)
                                            Buffers: shared hit=141
                                      ->  Index Only Scan using bookwyrm_reviewrating_pkey on bookwyrm_reviewrating  (cost=0.15..0.80 rows=1 width=4) (never executed)
                                            Index Cond: (review_ptr_id = bookwyrm_review.status_ptr_id)
                                            Heap Fetches: 0
                          ->  Index Scan using bookwyrm_comment_pkey on bookwyrm_comment  (cost=0.14..8.16 rows=1 width=544) (actual time=0.000..0.000 rows=0 loops=141)
                                Index Cond: (status_ptr_id = bookwyrm_status.id)
                                Buffers: shared hit=141
                    ->  Memoize  (cost=0.17..8.18 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=141)
                          Cache Key: bookwyrm_status.id
                          Cache Mode: logical
                          Hits: 0  Misses: 141  Evictions: 0  Overflows: 0  Memory Usage: 10kB
                          Buffers: shared hit=282
                          ->  Index Scan using bookwyrm_boost_pkey on bookwyrm_boost  (cost=0.15..8.17 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=141)
                                Index Cond: (status_ptr_id = bookwyrm_status.id)
                                Buffers: shared hit=282
              ->  Hash  (cost=11.10..11.10 rows=110 width=668) (actual time=0.017..0.017 rows=20 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 10kB
                    Buffers: shared hit=1
                    ->  Seq Scan on bookwyrm_quotation  (cost=0.00..11.10 rows=110 width=668) (actual time=0.006..0.008 rows=20 loops=1)
                          Buffers: shared hit=1
        ->  Memoize  (cost=0.17..8.18 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=141)
              Cache Key: bookwyrm_status.id
              Cache Mode: logical
              Hits: 0  Misses: 141  Evictions: 0  Overflows: 0  Memory Usage: 10kB
              Buffers: shared hit=282
              ->  Index Only Scan using bookwyrm_generatedstatus_pkey on bookwyrm_generatednote  (cost=0.15..8.17 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=141)
                    Index Cond: (status_ptr_id = bookwyrm_status.id)
                    Heap Fetches: 0
                    Buffers: shared hit=282
Planning:
  Buffers: shared hit=20
Planning Time: 3.152 ms
Execution Time: 1.238 ms

@ilkka-ollakka

Copy link
Copy Markdown
Contributor

Thanks for the plans. I'm also littlebit concerned those seq scans. Both has seq scan for User-table, most likely coming from 45bfd5b that changed order of index for is_active and could maybe benefit to change that commit queries otherway around so is_active is first instead of local.

New plan also seems to have seq scan on quotation table, but that might be that there is not that much in the table. I didn't spot same in old query explain for some reason.

Both of those cases seem to hit shared buffer, so I assume it most likely depends db activity vs db work_mem setting if those cause load or not into db.

Otherwise I agree that the new query seems way simpler.

@TemRevil

Copy link
Copy Markdown
Contributor Author

On the User table seq scan: that's not coming from this change, it's from 45bfd5b reordering the is_active filter ahead of the index lookup, which affects the old query plan too (it's just less visible there since the old plan's cost is dominated by the duplicated UNION subqueries). Happy to look at that separately if it's worth fixing, but it's a pre-existing issue, not something this PR introduces or changes.

On the Quotation table seq scan: that one is new to this plan, but it's because the table itself is tiny in my test data, the planner estimated 110 rows and the actual scan returned 20. Postgres correctly decides a full scan of a table that size beats an index lookup plus a heap fetch. On a much larger quotation table I'd expect it to use the index instead, same as it already does for the tables it index-scans here.

Agreed the buffer/work_mem tradeoff is real and depends on the instance, but on both counts here it looks like normal planner behavior rather than a regression from this change.

@TemRevil

Copy link
Copy Markdown
Contributor Author

Thanks. On the User-table seq scans, those show up in both the old and new plans (same Seq Scan on bookwyrm_user in each), so this change is not what introduces them. They come from the query already filtering statuses by user, and on a tiny test dataset Postgres just prefers a seq scan over the index. On a realistic table it should go back to the index. The is_active index ordering from 45bfd5b looks like a separate thing, probably worth its own look rather than folding it into this PR.

The quotation seq scan in the new plan is the same small-table effect: there are only a handful of quotation rows in my fixture, so the planner scans instead of using bookwyrm_quotation_book_id. With a realistic row count it should pick the index again. Everything here is shared-buffer hits either way, so no disk reads in these runs.

Happy to leave the index-order question to a follow-up if you agree. This PR is just swapping the re-embedded union subquery for a single materialized pass, which you already noted reads simpler.

@hughrun hughrun added the performance Things related to performance, optimization, load times, etc label Jul 19, 2026
@mouse-reeve
mouse-reeve merged commit 92ae9ea into bookwyrm-social:main Jul 20, 2026
8 checks passed
@mouse-reeve

Copy link
Copy Markdown
Member

I tried this against production and it was a huge improvement in run time. Thank you @TemRevil for the change and @ilkka-ollakka for the detailed review

@TemRevil

Copy link
Copy Markdown
Contributor Author

The production numbers are a nice confirmation, synthetic benchmarks only tell you so much. Thanks for merging it, and thanks ilkka-ollakka for pushing on the plan details, that back-and-forth got the fix from "looks faster" to actually proven. Happy to take a look if anything else in the activity feed queries needs it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

performance Things related to performance, optimization, load times, etc

Projects

None yet

Development

Successfully merging this pull request may close these issues.

add_book_statuses uses expensive query

4 participants