Nate on Nostr: Ok well that suprises me a lot. When it comes to further performance optimisations ...

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.