What else is to be implemented?

Ok well that suprises me a lot. When it comes to further performance optimisations the only thing throwing a curveball is the LIMIT on some of the subqueries, otherwise this could easily become one or two queries (reducing the overall performance overhead of the UNION, which runs as an individual query, creates a temporary table, has to be sorted (in some cases), and then remerged.

An example of what I mean is

SELECT *
FROM (
SELECT *
FROM nostr_events
WHERE kind IN ($5, $6, $7)
AND pubkey IN ($8, $9)
) AS nostr_events

UNION ALL

SELECT *
FROM (
SELECT *
FROM nostr_events
WHERE kind IN ($14, $15, $16)
AND pubkey IN ($17)
) AS nostr_events

UNION ALL

SELECT *
FROM (
SELECT *
FROM nostr_events
WHERE kind IN ($21, $22, $23)
AND pubkey IN ($24, $25, $26)
) AS nostr_events;

Becomes

SELECT nostr_events.*
FROM nostr_events
WHERE
(kind IN ($5, $6, $7) AND pubkey IN ($8, $9))
OR (kind IN ($14, $15, $16) AND pubkey IN ($17))
OR (kind IN ($21, $22, $23) AND pubkey IN ($24, $25, $26))


OR

SELECT *
FROM (
SELECT *
FROM nostr_events
WHERE kind IN ($1, $2)
AND pubkey IN ($3)
LIMIT $4
) AS nostr_events

UNION ALL

SELECT *
FROM (
SELECT *
FROM nostr_events
WHERE kind IN ($5, $6, $7)
AND pubkey IN ($8, $9)
) AS nostr_events

can easily become

SELECT *
FROM nostr_events
WHERE (kind IN ($1, $2) AND pubkey IN ($3))
OR (kind IN ($5, $6, $7) AND pubkey IN ($8, $9))
LIMIT $4

if that limit is not required.

Infact all 6 of those queries could be merged into one, but it would likely return a slightly different result set due to the exclusion of LIMIT, but a lot of the other queries do not have a limit and would be easier to do than others

Please check my SQL btw, it may not return the correct result set, or it may just be straight up incorrect, it is 1.30am here.

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

I thought the reason that a client tag was added was for tracking where issues could be coming from eg poor interoperability with other clients by breaking the NOSTR schema

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)

Would be awesome to see this in both Notedeck and Gossip!

apples and oranges my friend

Alternative to Twitter, not a twitter clone

Well, that is certainly a point of view. Not a good one though. I was suspicious that you may have thought like this however, hence why I used tax in my example

Youre going to have to let us know what client you are using so we can get to the bottom of this, and 20MB/s the internet speed dedicated to this activity, its not too helpful in diagnosing this, you could download 1MB @ 1000MB/s, it just would just be instant.

I tolerate high tax rates to afford free (hospitalised) healthcare, or taking care of the sick or disabled. One could argue that is good, and one could argue it may cause stress on others. There’s one example for you of something that is good and requires tolerance, please put a little more thought into your next posts!

Huh? AFB has been around for many many years before Covid, don’t trust the ‘government scientists’ if that’s what you please, but at least do your own research before talking nonsense