Nate on Nostr: Unless Im reading this wrong (which I could very well be) the performance would ...
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)
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)