Whoever made this goes straight to jail. 

 I am also getting a ton like this:

REQ [{"since":1718038930,"until":1718038990}]

fuck you scrapers. Give me your tired, your poor, Your huddled masses yearning to breathe free 

 Yeah, since and until aren't us :)  

 Reject those, specially if the delta is too big. 

 Listen to Cameri 

 Putting a timeout seems to be helping a lot because it's a sweeping fix. That combined with rate-limiting ws messages covers the basics. But I think I need to also punish slow queries similar to rate-limiting, so that if you spend too much query time over a period you get blocked for a short period. 

 In chorus, every connection is IP blocked for 1 second after disconnection.  That is to prevent rapid reconneciton loops.  That block last longer on bad behavior. 

  https://video.nostr.build/39b9f55d04aa222154729775c00660d1a658eb52fcf939fade6ec34c19ef1870.mp4  

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

 This looks like Amethyst. When then user scrolls it adds and removes authors to the sub. But is the issue that there is no event in your DB for those authors? Otherwise, it would have a `since` based on the previous EOSE. 

Do you return EOSE when nothing matches the filter? 

 I do return EOSE. But now if the query times out, I just return CLOSE. 

 I'm questioning if multiple filters in a REQ was ever a good idea. I treat a single REQ as one database query. I could break them up and treat them like separate requests (but so could you). I'm not sure whose responsibility that is. If I do, I will need to rate-limit the number per interval of filters instead of the number of websocket messages. 

 Let me try to get to the same filter. Amethyst is supposed to group those into bigger filters. But I am wondering if there is a bug when since is not present in these filters. 

What's the relay url to test this out?  

 wss://gleasonator.dev/relay 

 Yep, this one. Thank you Mr. Patrick! 🍀  

 Ok I am getting `error: something went wrong` on that filter. Is that the timeout error?  

 There is something weird going on. Your relay doesn't like the kind 1984 with only the ptag filter. 

This simple one takes forever. 
```
[
   {
      "kinds":[
         1984
      ],
      "#p":[
         "99bb5591c9116600f845107d31f9b59e2f7c7e09a1ff802e84f1d43da557ca64",
         "8867bed93e89c93d0d8ac98b2443c5554799edb9190346946b12e03f13664450"
      ]
   }
]``` 

 Actually, just one p-tag runs into the same error: 


``` 
[ "REQ", "aa2dd",
   {
      "#p":[
         "8867bed93e89c93d0d8ac98b2443c5554799edb9190346946b12e03f13664450"
      ]
   }
]```

```
[
  "CLOSED",
  "aa2dd",
  "error: something went wrong"
]
```
 

 I bet there is a record between this since and until that you cannot reassemble to send back: 

[ "REQ", "aa2dd",
   {
      "since": 1719870040,
      "until": 1719870100,
      "#p":[
         "8867bed93e89c93d0d8ac98b2443c5554799edb9190346946b12e03f13664450"
      ]
   }
] 

 I increased the timeout a lot and it still gets cancelled, yikes. I can't get into postgres to "explain analyze" from this hotel wifi. Will try to see later. Thanks for your help. 

 Hi Vitor, is this just a sample request or there is indeed at least 1 event that matches this tags and kinds? I have a relay called "wss://slime.church/relay" and it's the same code as "wss://gleasonator.dev/relay" (both run ditto).

However, I couldn't reproduce this issue with slime.church, the request is fast and the response is empty. 

 I don't know if there is an event. But the filter bug is real. It doesn't work even if I ask through Websocket testing sites. 

 Got it, I just don't understand why this doesn't happen with wss://slime.church/relay, perhaps there's a specific configuration in wss://gleasonator.dev/relay that is messing things up, I'll take a look, thanks 

 It's the amount of data that's the problem. Because with ORDER BY, it has to go through everything to figure out which is the most recent before it can pull even 1 result.