How to compute cost of slow query in PostgreSQL?

The cost of a slow query in PostgreSQL can be estimated by the Query Planner, which uses statistics about the data in the tables being queried to make predictions about the query execution time. The cost of a query is expressed in arbitrary units and can be used to compare the relative cost of different queries.

To compute the cost of a slow query in PostgreSQL, you can use the EXPLAIN statement, which provides a detailed analysis of the query plan, including the estimated cost of each operation in the plan. The estimated cost of the query is the sum of the costs of all the operations in the plan.

Here’s an example of how to use the EXPLAIN statement to compute the cost of a slow query:

This will output a detailed explanation of the query plan, including the cost of each operation, the total cost of the query, and the actual runtime of the query.

Keep in mind that the estimated cost of a query may not always accurately reflect the actual runtime, as the Query Planner’s predictions are based on the statistics available at the time of the query, and these statistics may not always reflect the current state of the data. To get more accurate cost estimates, you may need to regularly update the statistics in your PostgreSQL database.

The EXPLAIN statement in PostgreSQL has several options that you can use to control the level of detail in the output and to obtain additional information about the query plan. Here are some of the most commonly used options:

  1. ANALYZE: Causes the query to be executed and the actual row counts, time, and other statistics to be included in the output.
  2. COSTS: Includes the estimated cost of each operation in the output.
  3. BUFFERS: Shows information about the use of disk buffers by the query, including the number of disk pages read and written.
  4. VERBOSE: Provides additional detail about each operation in the query plan, including the actual join methods used and the exact row counts.
  5. FORMAT: Specifies the format of the output, either text (the default) or json.
  6. ANALYZE True: Causes the query to be executed and the actual row counts, time, and other statistics to be included in the output, and includes the estimated cost of each operation in the output.

Here’s an example of using the EXPLAIN statement with the ANALYZE and COSTS options:

This will output a detailed explanation of the query plan, including the cost of each operation, the total cost of the query, and the actual runtime of the query.

About Shiv Iyer 157 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.