postgres
two new indexes, query time reduced from >7s to 70ms. my work here is done.
(first new index reduced to 90ms, second a bit more still to 70ms)
ln's postgres optimization tip
put "EXPLAIN ANALYZE" in front of your query, paste output to https://explain.depesz.com/ and look for values in the exclusive column which look way too high. then check in the node column what it is doing and maybe add an appropriate index.
*poof* yer a postgres witch now :3
ln's postgres optimization tip #2 cont
also have a look at the index types! https://www.postgresql.org/docs/current/indexes-types.html there's cool stuff in there and maybe something fits your usecase better? e.g. i used a hash index for value comparisons because it saves a whole lot of storage space. and some of the row were really long, comparing an int vs. a kb of text is also a nice speedup.
ln's postgres optimization tip
@ln there's also something similar built into pgadmin that also can visualize the plan as a graph. I thought it was also available as a standalone thing but I couldn't find it :/
ln's postgres optimization tip
@ln https://explain.dalibo.com/ also looks pretty neat, but I haven't used it before
ln's postgres optimization tip
@uberardy oh that one is amazing, thanks! i like that the depesz one is rather simple and shows me at a glance what i need, but this is a great addition!
ln's postgres optimization tip #2
if you're joining on more than one field, consider a compound index!
e.g. CREATE INDEX query_go_brrrr ON dat_table USING BTREE (column_a, column_b)
and suddenly your database doesn't have to do two index lookups but only one for a query!