5 ms·
Not specific to Postgres but I found that knowing how to write complex and efficient SQL queries can be overlooked but is a very useful skill, and then understa
by tarsinge 9y ago
Not specific to Postgres but I found that knowing how to write complex and efficient SQL queries can be overlooked but is a very useful skill, and then understanding indexes and query plans allows to drastically improve performances and thus reduce scaling needs
Edit: wording
- snuxoll 9y agoUnderstanding your database configuration is important as well. I spent way longer than I'd like to admit trying to improve performance of complex query that would not execute the way I wanted it to no matter what, PostgreSQL absolutely refused to use a calculated index on one table and then do a HASH JOIN to a second, instead devolving into some really hairy execution plan I can't even remember at this point. Turns out, I had done everything right in my index design and query - but due to the sheer volume of data the query planner didn't use the "correct" plan because work_mem was set too low. Always be mindful of how your database is tuned, you could be doing everything right but the query planner will seem to act insane regardless.