Nate on Nostr: Again, I could very well be reading this wrong, but the joins dont look necassary to ...

Again, I could very well be reading this wrong, but the joins dont look necassary to me, it could look something like this

SELECT *
FROM nostr_events ne
WHERE (kind IN ($1, $2) AND pubkey IN ($3))
AND EXISTS (
SELECT 1
FROM nostr_tags tag0
WHERE tag0.event_id = ne.id
AND tag0.name = $4
AND tag0.value IN ($5, $6)
))

(this isnt directly taken from your query below, this is just an example)

This is also why i recommended the use of the composite index (event_id, name, value), which I would image would give a huge performance boost on that EXISTS check, but without being able to test it (and make sure it returns the correct results) I could be talking out of my ass