5 ms·
Tangential to this topic: If I have a Django + PG query that takes 1 second and I want to deeply inspect the breakdown of that entire second, where might I beg
by Waterluvian 5y ago
Tangential to this topic:
If I have a Django + PG query that takes 1 second and I want to deeply inspect the breakdown of that entire second, where might I begin reading to learn what tools to use and how?
- nerdbaggy 5y agoDjango has built in explain support which can guide you on the right track https://docs.djangoproject.com/en/3.2/ref/models/querysets/#django.db.models.query.QuerySet.explain https://docs.djangoproject.com/en/3.2/ref/models/querysets/#...
- etxm 5y agoI’d start w ‘EXPLAIN query’, if you arent familiar with the output there, you can put it on PEV and get a visualization. https://tatiyants.com/pev/#/plans https://tatiyants.com/pev/#/plans
- snissn 5y agoagree!! this page is so helpful
- fabian2k 5y agoEXPLAIN ANALYZE in Postgres will give you the query plan, learning to understand that output is very useful to figure out why a query is slow. If the query isn’t slow, you can look into Django, but the DB is often a good first guess in these cases.
- purerandomness 5y agoI recommend the book "SQL Performance Explained" by Markus Winand: https://sql-performance-explained.com/ https://sql-performance-explained.com/ It covers all major databases and is a good start to dive into database interna and how to interpret output from query analyzers. Other than that, I highly recommend joining the mailing list and IRC (#postgresql on libera.chat). Lots of valuable tricks being shared there by people with decades of experience.
- epberry 5y agoThis will get you started but is by no means a full guide on query optimization, https://arctype.com/blog/postgresql-query-plan-anatomy/ https://arctype.com/blog/postgresql-query-plan-anatomy/. There's also a fair number of django posts on this blog.
- emilsedgh 5y agoEXPLAIN (ANALYZE, BUFFERS) <YOUR QUERY> Take the result of this and paste it into https://explain.depesz.com/ https://explain.depesz.com/ which will make it human readable. Understanding this is sometimes very easy, but if you want to understand what they _really_ mean, you can read depesz.com
- jakebasile 5y agoWow, how have I never heard of this tool?! Thanks a lot for the link!
- pgaddict 5y agoBe careful as it actually runs the query, so if it's a DELETE/INSERT/UPDATE it'll change the data. So run it in BEGIN/ROLLBACK block.
- diminish 5y agoI use it frequently - but I wish there was a tool which went into the semantics somewhat.
- bredren 5y agoJust in case someone’s reading this and isn’t also aware: Django Debug Toolbar offers somewhat interactive exploration of queries. It can also be used with Django Rest Framework via the browsable api. May be parent is looking for deeper insight than this but it is useful to do quick visual query inspection.
- blondin 5y agothis. django debug toolbar (or similar) should be the first thing you go to because these tools understand the django ORM well. the other thing that comes to mind is enabling query timing in your django shell. i believe you might need an extension for this. then you can look at the postgres itself. but i would keep it at the django layer at first because it might reveal something about the ORM.
- Izkata 5y agoIf you only have the Django queryset and not the SQL, you can generate pseudo-sql using "print(queryset.query)". Note that this isn't valid SQL, just an approximation, because Django doesn't generate a single SQL string, but uses the underlying library's parameterization. So you'll have to fiddle with quotes and such to get SQL you can run the EXPLAIN on that's mentioned in the other replies.
- cosmosgenius 5y agoAll django query have a .explain with it. Which is similar to runnning an explain in the DB, but less detailed.