Quite a few of our projects are using PostgreSQL now and one in particular is running up against "slow query" issues ... this is because the database has no index so any query that does JOIN with multiple tables will (almost by definition) take ages to compute. ⏳ 😢
Tasks/Todo:
Following the documentation-driven-learning approach, undertake the following:
[ ] measure query performance for our "slow" query and take a screenshot of time/resources required to return it. (it's essential to benchmark the existing state before embarking on our quest to lower query time!)
[ ] research what an indexis:
[ ] what are indices? (thankfully our friends at Heroku have done some of the work explaining this: https://devcenter.heroku.com/articles/postgresql-indexes but we still need a section in our readme covering the topic and relevant links to any other learning resources...)
[ ] how do I apply one to an existing table (or set of tables)?
[ ] apply an index to your "slowest" and/or "biggest" table and
[ ] documenthow much query time is improved/lowered (with a screenshot)
The Problem ...
Quite a few of our projects are using PostgreSQL now and one in particular is running up against "slow query" issues ... this is because the database has no
indexso any query that doesJOINwith multiple tables will (almost by definition) take ages to compute. ⏳ 😢Tasks/Todo:
Following the
documentation-driven-learningapproach, undertake the following:indexis:sectionin our readme covering the topic and relevant links to any other learning resources...)Further Reading (please add moar!!)
ANALYZEto optimize PostgreSQL queries: https://wiki.postgresql.org/wiki/ANALYZEindex: https://www.tutorialspoint.com/postgresql/postgresql_indexes.htm