Nate on Nostr: Again, I could very well be reading this wrong, but the joins dont look necassary to ...
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 *
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