Optimizing PostgreSQL queries

• Get the original query execution plan. I do this using DBeaver https://dbeaver.com/docs/wiki/Query-Execution-Plan/

Alternatively you can run

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)

followed by the original query. This will return a JSON that you can use in the next step. If the query plan is too big, you want to disable the pager so that you can copy it directly from the console:

\pset pager off

• Paste the original query and the execution plan in https://tatiyants.com/pev/#/plans/new
• Analyze the resulting chart looking for bottlenecks, indexes that are not being used, etc.
• Improve the query based
• Get the execution plan of the new query and past it (with the new query) in a new tab of https://tatiyants.com/pev/#/plans/new
• Compare the 2 charts to visualize the improvements

If you have a better approach please share in the comments