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)
Nate / Nate Levin
npub1am…gc0mj
2024-07-02 05:08:58
in reply to nevent1q…039w
Author Public Key
npub1amtt4ht2vwuxc4lydp6tkd0w6a2kzstmnxltwpzwc20c2pgp7uzq2gc0mjPublished at
2024-07-02 05:08:58Event JSON
{
"id": "6e4801ae6df977b1b1f421c7dc79ea65d444d717887a6a331d9777f49f972984",
"pubkey": "eed6badd6a63b86c57e46874bb35eed75561417b99beb7044ec29f850501f704",
"created_at": 1719896938,
"kind": 1,
"tags": [
[
"p",
"0461fcbecc4c3374439932d6b8f11269ccdb7cc973ad7a50ae362db135a474dd"
],
[
"p",
"460c25e682fda7832b52d1f22d3d22b3176d972f60dcdc3212ed8c92ef85065c"
],
[
"e",
"ac7d4f14106c6e558a79c4685b26a5dec43a39dfec046e2b43aadb6b534be829",
"wss://nostr.fmt.wiz.biz/",
"root"
],
[
"e",
"ffde0d15fff800fefeb02ad9064612f4038d593a7e290480e44c0b68174e1ace",
"wss://gleasonator.dev/relay",
"reply"
]
],
"content": "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?\n\nAnother 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) \n\nId also suggest if you make the EXISTS change that you create a composite index on (event_id, name, value)\n\nThis 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\n\nIf 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)",
"sig": "560ae99c3ce8456e2ff9547c52211673fdf789bcf2ad1407425cf731ccc3ea8ac30711daa9aaf809d096493b639a42a5744771d7592c89dafa881aafdf19c7ad"
}