Unless Im reading this wrong (which I could very well be) the performance would likely be increased dramatically if the unions were removed, as this creates a temporary table in postgresql (or atleast it does in MYSQL which im used to) which adds a lot of overhead. I guess this is probably hard to get rid of however, as Im going to assume this is dynamically generated SQL?

Another performance optimization would be using EXISTS in a few of the queries where the INNER JOIN on nostr_tags is used just to filter (most of them) 

Id also suggest if you make the EXISTS change that you create a composite index on (event_id, name, value)

This is my experience with MYSQL though, I know postgres is more efficent in some manners so maybe all of this is automatically done under the hood

If you want me to have a look further into this I would be happy to, but Id probably need to spin up a database (and therefore would need some data) 

 tags to events is a many-to-many relationship, so the joins are necessary. I've never heard of EXIST before 🤔  

 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 

 select [...] where exists (select 1 from blah where cond);