SELECT * FROM posts WHERE post_id = 232 AND comment_id = 65 AND owner = 'Bob' Ĭould be optimized by indexing with CREATE INDEX ON posts (post_id, comment_id, owner) CREATE INDEX CONCURRENTLY ON reports (report_id) Ĭomposite indices are useful when you want to optimize querying on multiple non-conditional columns.
It's almost always a good idea to create indices concurrently to prevent synchronous locking on a table during migrations. The caveat is that indexing slows down writes to the table. If you are programmatically ordering, sorting, or grouping by a column, you should probably have an index on it. EXPLAIN (format json) SELECT * FROM requests įor JSONB columns, use jsonb_pretty(column_name) to pretty print your data instead of a giant compressed text blob. Use \x to toggle extended display, which presents query results in a cleaner tabulated format.ĮXPLAIN (along with similar commands) accepts formatting arguments. Use \watch at the end of a query in order to automatically re-run the query every few seconds - great for monitoring while making changes elsewhere in your application architecture. If you have a long query to write and rewrite, you can use \e to edit your query in an editor.
See More out of psql for some interesting configurations. It behaves just like ~/.bashrc or ~/.vimrc, sourced at psql launch. ~/.psqlrc can be edited to persist any behavior or configuration settings you want between psql sessions. Psql is a fully-fledged CLI client for Postgres, but most people are unaware of its many advanced features.
0 kommentar(er)
