Analyzing PostgreSQL Query Performance

To evaluate the performance of a PostgreSQL query, we can utilize the EXPLAIN command. In pgAdmin, upon opening a query window, you will find two buttons located on the top menu bar.

Core Comparison

	EXPLAIN	EXPLAIN ANALYZE
Execution	Does not run the query.	Executes the query completely.
Speed	Instantaneous (takes milliseconds).	Takes as long as the query itself to run.
Data Safety	100% safe for all commands.	Modifies data if used on INSERT/UPDATE/DELETE.
Metrics	Theoretical estimates (cost, rows, width).	Real runtime stats (actual time, actual rows, memory).

Because EXPLAIN ANALYZE actually runs the query, it is important to enclose the entire script with START TRANSACTION and ROLLBACK commands when analyzing DML statements. If you do not take this step, you might unintentionally modify the table data while analyzing the query. To avoid such unintended changes, always use these transaction commands during your analysis.

To analyze your query in the pgAdmin query window, select the START TRANSACTION, EXPLAIN ANALYZE, and the specific query, then hit F5 to run it, such as:

After that, you can highlight the complete output, copy it, and insert it into a text file.

Finally, you should highlight a ROLLBACK command, then execute it.

Be aware that executing the full set of commands (START TRANSACTION, EXPLAIN, your SQL query, ROLLBACK) together will lead to the ROLLBACK canceling the EXPLAIN output, which means you won't be able to access the information you need.

Additionally, you can retrieve the BUFFERS, COST, and TIMING data from the real execution plan by running EXPLAIN (ANALYZE, BUFFERS, TIMING, COSTS).

ANALYZE BUFFERS is the gold standard for query optimization because it reveals the exact I/O footprint of a query. While ANALYZE executes the query to show actual runtime and row counts, BUFFERS shows exactly how many data blocks (pages) Postgres had to read, write, Postgres handles data in blocks, where 1 block = 8 KiB by default.