Repository navigation
Avoid re-running the book-statuses UNION as a subquery in add_book_statuses - #4027
mouse-reeve merged 3 commits into
Conversation
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
|
Thanks for the PR. Can you provide explain analyze to show query plan change? Also have you checked AGENTS.md file? |
|
okay i'll do, and yes i did, after you told me already |
|
Is there anything about it? |
|
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): 8 New code (union evaluated once into plain lists): 0 Subquery Scan nodes, both lookups are 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. |
No, just wanted to make sure you had checked it as PR description looked like agentic output of automated PR creation. |
Thanks
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?
Cost increase makes me wonder how well this query change actually performns in scale. |
| # 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] |
There was a problem hiding this comment.
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
There was a problem hiding this comment.
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.
|
Pushed aff4b64 using your suggestion, zip() over the two list comprehensions. One thing I ran into: a bare 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 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 planNew plan (current pushed code, with the zip fix) in the next comment. |
|
New plan, continued from above, full and unedited: New plan |
|
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. |
|
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. |
|
Thanks. On the User-table seq scans, those show up in both the old and new plans (same 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 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. |
|
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 |
|
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. |
Fixes #4026
Problem
add_book_statusesbuilds a UNION of the four book-status querysets (comments, quotations, reviews, mentions), then reuses that same union queryset twice more:Embedding a UNION inside
id__in=/thread_id__in=makes Postgres re-execute the whole UNION for each lookup. As reported in #4026, this pushedadd_book_statuses_taskfrom ~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_idlists and reuse those, so the two follow-up lookups filter on integer lists instead of re-running the UNION as a subquery: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 inremove_book_statusesand theHomeStream.add_book_statusesoverride, 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.