EXPLAIN (ANALYZE, BUFFERS) <YOUR QUERY>

Take the result of this and paste it into 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

I use it frequently - but I wish there was a tool which went into the semantics somewhat.

I have not tried it but PgMustard is stating that they make the query plans more explainable and hint for the problems in your query.

What do you mean by "went into the semantics"?

Wow, how have I never heard of this tool?! Thanks a lot for the link!

Be 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.

I recommend the book "SQL Performance Explained" by Markus Winand: 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.

Did freenode get renamed?

Not renamed, but a number of Freenode channels and admins moved to libera.chat recently due to non-technical IRC drama / politics over "ownership".

EXPLAIN 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.

I’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

agree!! this page is so helpful

Django has built in explain support which can guide you on the right track https://docs.djangoproject.com/en/3.2/ref/models/querysets/#...

Just 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.

this.

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.

If 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.

This will get you started but is by no means a full guide on query optimization, https://arctype.com/blog/postgresql-query-plan-anatomy/. There's also a fair number of django posts on this blog.

All django query have a .explain with it. Which is similar to runnning an explain in the DB, but less detailed.