btw there's no limit on any of these, and they need to be returned in reverse-chronological order. It results in this monster query with 6 joins and 55 parameters. Christ.

select * from (select "nostr_events".* from "nostr_events" where "kind" in ($1, $2) and "pubkey" in ($3) limit $4) as "nostr_events" union all select * from (select "nostr_events".* from "nostr_events" where "kind" in ($5, $6, $7) and "pubkey" in ($8, $9)) as "nostr_events" union all select * from (select "nostr_events".* from "nostr_events" inner join "nostr_tags" as "tag0" on "tag0"."event_id" = "nostr_events"."id" where "kind" in ($10) and "tag0"."name" = $11 and "tag0"."value" in ($12, $13) order by "created_at" desc) as "nostr_events" union all select * from (select "nostr_events".* from "nostr_events" where "kind" in ($14, $15, $16) and "pubkey" in ($17)) as "nostr_events" union all select * from (select "nostr_events".* from "nostr_events" inner join "nostr_tags" as "tag0" on "tag0"."event_id" = "nostr_events"."id" where "kind" in ($18) and "tag0"."name" = $19 and "tag0"."value" in ($20) order by "created_at" desc) as "nostr_events" union all select * from (select "nostr_events".* from "nostr_events" where "kind" in ($21, $22, $23) and "pubkey" in ($24, $25, $26)) as "nostr_events" union all select * from (select "nostr_events".* from "nostr_events" inner join "nostr_tags" as "tag0" on "tag0"."event_id" = "nostr_events"."id" where "kind" in ($27) and "tag0"."name" = $28 and "tag0"."value" in ($29, $30, $31) order by "created_at" desc) as "nostr_events" union all select * from (select "nostr_events".* from "nostr_events" where "kind" in ($32, $33, $34) and "pubkey" in ($35)) as "nostr_events" union all select * from (select "nostr_events".* from "nostr_events" inner join "nostr_tags" as "tag0" on "tag0"."event_id" = "nostr_events"."id" where "kind" in ($36) and "tag0"."name" = $37 and "tag0"."value" in ($38) order by "created_at" desc) as "nostr_events" union all select * from (select "nostr_events".* from "nostr_events" where "kind" in ($39, $40, $41) and "pubkey" in ($42, $43)) as "nostr_events" union all select * from (select "nostr_events".* from "nostr_events" inner join "nostr_tags" as "tag0" on "tag0"."event_id" = "nostr_events"."id" where "kind" in ($44) and "tag0"."name" = $45 and "tag0"."value" in ($46, $47) order by "created_at" desc) as "nostr_events" union all select * from (select "nostr_events".* from "nostr_events" where "kind" in ($48, $49, $50) and "pubkey" in ($51)) as "nostr_events" union all select * from (select "nostr_events".* from "nostr_events" inner join "nostr_tags" as "tag0" on "tag0"."event_id" = "nostr_events"."id" where "kind" in ($52) and "tag0"."name" = $53 and "tag0"."value" in ($54) order by "created_at" desc) as "nostr_events" limit $55 

 Looks like some of the 'order by' statements are pre-union. You should only sort after the unions. 

 I will try that and benchmark it. Thank you for the suggestion! 

 I think it would only work if each filter doesn't have a limit. 

 Ah right. 

 Since this is postgres, can you do an EXPLAIN on that query, or preferably any similarly slow but not-so-hairy query? 

 Also, do you have an multi-column index on (kind, pubkey) as a pair?  It may sound superfluous but it is faster than having two separate indexes (which you still need however).  Because under the hood the data is laid out in such an index like this:

  kind1,author1 -> offsets of events
  kind1,author2 -> offsets of events 
  kind1,author3 -> offsets of events
  kind2, author1 -> offsets of events
  ...

and it can jump to the matching kind and author in one fell swoop without doing a hashjoin (for example). 

 I do. I ran into the same thing. Here's my schema:

https://image.nostr.build/87e21d98e38cfc97f96f04fc0abb7688958fc273ab6d7c61f4bdd0655d7f6f40.png 

 I have a thorough benchmark too:



https://image.nostr.build/9d62cc358e9d862a3dd78e10ee3a221dc89a672a6ac33536efcf032bbd1e27b9.png 

 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);