What is Nostr?
Nate / Nate Levin
npub1amt…c0mj
2024-07-02 05:08:58
in reply to nevent1q…039w

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)
Author Public Key
npub1amtt4ht2vwuxc4lydp6tkd0w6a2kzstmnxltwpzwc20c2pgp7uzq2gc0mj