#Enigmatick's timeline retrieval performance was getting a little slow, so I spent some time optimizing my PostgreSQL queries yesterday.

[jdt@enigmatick.social]

#Enigmatick's timeline retrieval performance was getting a little slow, so I spent some time optimizing my PostgreSQL queries yesterday. I was able to take the EXPLAIN ANALYZE loop from 17 seconds (an exaggeration of the actual experience in production of a delay of about 2 seconds) to 53ms. It was mostly down to a JOIN that employs a JSONB_AGG to pull in Actor records associated with an Activity to avoid having to make additional calls. The way I was matching against the attributed_to JSONB column was apparently slowing things down.

I also have some #Wasm calls that proactively transform #E2EE asymmetrically encrypted messages to symmetrically encrypted vault items. Those run on each load of the timeline, but I was able to use spawn_local to move the processing to a background task for timeline views that don't display encrypted content.