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 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! 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 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 – a Fediverse instance for & by the Chaos community