User avatar
jade "very problematicā„¢" arson. @awoo@gts.apicrim.es
2mo
there are no database performance problems at jaded systems :D
A database query I'm running to report an issue on is currently stuck for 8 minutes onwards and still counting.
3
0
1
0
User avatar
flori_ava_star:~cursor_blinking made-with-estrogen verifiedlesbian @star@amazonawaws.com
2mo
@awoo This looks like a LOT of potential sequential scans and also filters waow
2
0
2
0
User avatar
tan @wyldtom@chaos.social
2mo
@star @awoo the resulting query plan is also great :D

Index Only Scan using statuses_home_timeline_idx on public.statuses status (cost=0.56..938.74 rows=236 width=54) (actual time=0.122..865491.931 rows=9430338 loops=1)

for 20 rows as result :D
2
0
1
0
User avatar
jade "very problematicā„¢" arson. @awoo@gts.apicrim.es
2mo
@wyldtom @star at this point you might as well drop the link :D
codeberg.org/superseriousbusiness/gotosocial/pulls/4761#issuecomment-11727792
0
0
1
0
User avatar
alarmed feet operative: kimothy siddon @kim@k.iim.gay
2mo
@wyldtom @star @awoo i don't understand how it's still running so slow even with the index, and given the planner is entirely relying on it šŸ§
2
0
1
0
User avatar
jade "very problematicā„¢" arson. @awoo@gts.apicrim.es
2mo
@kim @wyldtom @star well, neither do I unfortunately
0
0
1
0
User avatar
flori_ava_star:~cursor_blinking made-with-estrogen verifiedlesbian @star@amazonawaws.com
2mo
@kim @wyldtom @awoo Maybe the index isn't the problem; maybe the planner is severely mis-estimating the plan, and therefore building a terrible plan? I heard that that is a thing that happens, when the analyze daemon/vacuum job hasn't been run on a table for a while for some reason

If you are really keen to find out, I'd suggest throwing the query into a very verbose explain analyze, then using a query plan visualizer to get a good overview of what the database is doing, where it is going wrong, and where most of the time in the query is spent. I actually held a workshop about this very recently :3
1
0
2
0
User avatar
jade "very problematicā„¢" arson. @awoo@gts.apicrim.es
2mo
@star @kim @wyldtom thing is, I did actually run a vacuum job recently enough iirc
1
0
1
0
User avatar
alarmed feet operative: kimothy siddon @kim@k.iim.gay
2mo
@awoo @star @wyldtom could you post in the codeberg thread maybe some explain log dumps of the faster user's home timeline queries on your instance? it could be a compounding problem, i.e. shows up as a bit of extra slowness in small queries but gets worse at a much higher rate than linearly. that might indicate poorer than expected postgres performance in general
0
0
0
0
User avatar
jade "very problematicā„¢" arson. @awoo@gts.apicrim.es
2mo
@star and that's the query for generating my home page!
0
0
1
0