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)

Follow

ln's postgres optimization tip 

put "EXPLAIN ANALYZE" in front of your query, paste output to 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

· · Web · 2 · 8 · 35

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!

ln's postgres optimization tip #2 cont 

also have a look at the index types! postgresql.org/docs/current/in 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 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!

Sign in to participate in the conversation
chaos.social

chaos.social – a Fediverse instance for & by the Chaos community