Tips for PostgreSQL Query Optimization: EXPLAIN ANALYZE

Learn performance tuning techniques to improve database operations

With many people working from home because of the coronavirus pandemic, it can be a little challenging to get help from a colleague remotely. Sure, there’s Slack and all manner of collaboration tools, but it’s not quite the same as walking up to someone’s cubicle and getting a second pair of eyes to look at a problem, not to mention that our co-workers might be busy trying to juggle deadlines and unruly kids at home. When it comes to dealing with poor database and query performance, it’s a daunting task to venture into the dark cavern of query planning and optimization, but fear not! EXPLAIN is our friend in those dark and lonely places.

We recently received a request from one of our customers, concerned about a slow query on one of their JSON columns. They were seeing a slow performance in their development environments and were understandably worried about the impact they’d see if they went to production with poor query performance. We got right to work to help them out, and our first stone to turn over was to have them send us their EXPLAIN ANALYZE output for the query, which yielded:

postgres=# explain SELECT * FROM org where 'aa'::text IN (SELECT jsonb_array_elements(info -> 'dept') ->> 'name');

              QUERY PLAN

-------------------------------------------------------------------------

Seq Scan on org (cost=0.00..719572.55 rows=249996 width=1169)

   Filter: (SubPlan 1)

   SubPlan 1

   -> Result (cost=0.00..2.27 rows=100 width=32)

      -> ProjectSet (cost=0.00..0.52 rows=100 width=32)

         -> Result (cost=0.00..0.01 rows=1 width=0)

They knew they had created an index and were curious why it wasn’t used. Our next data point to gather was information about the index itself, and it turned out they had created their index like so:

CREATE INDEX idx_org_dept ON org ((info -> 'dept'::text) ->> 'name'::text));

Their query was wrapping info -> 'dept' in a function called jsonb_array_elements(), which led the query optimizer to think it shouldn’t use the index. The fix was simple; we got the customer back on their way after a quick adjustment to their query. Once the customer changed their query to the following, the index started getting scanned:

postgres=# SELECT * FROM org where 'aa'::text IN (info -> 'dept' ->> 'name');

postgres=# explain SELECT * FROM organization where 'aa'::text IN (info -> 'dept' ->> 'name'); 

         QUERY PLAN

----------------------------------------------------------------------------------------------

  Index Scan using idx_org_dept on org (cost=0.42..8.44 rows=1 width=1169)

      Index Cond: ('aa'::text = ((info -> 'dept'::text) ->> 'name'::text))

(2 rows)

Using EXPLAIN in troubleshooting can, therefore, be invaluable.

What is EXPLAIN?

EXPLAIN is a keyword that gets prepended to a query to show a user how the query optimizer plans to execute this query. Depending on the complexity of the query, it will show the join strategy, method of extracting data from tables, estimated rows involved in executing the query, and other useful information. Used with ANALYZE, EXPLAIN will also show the time spent executing the query, sorts, and merges that couldn’t be done in-memory, and more. This information is invaluable when identifying query performance bottlenecks and opportunities and helps us understand what information the query optimizer uses to make its decisions for us.

PostgreSQL Query Optimization: A Cost-Based Approach

All data on the disk is the same to the query optimizer. Determining the fastest way to reach a particular piece of data requires estimating the time it takes to do a full table scan, a merge of two tables, and other operations to get data back to the user. PostgreSQL accomplishes this by assigning costs to each execution task, and these values are derived from the postgresql.conf file (see parameters ending in *_cost or beginning with enable_*). When a query is sent to the database, the query optimizer calculates the cumulative costs for different execution strategies and selects the most optimal plan (which may not necessarily be the one with the lowest cost).

bash $ pgbench -i && psql

...>

postgres=# EXPLAIN SELECT * FROM pgbench_accounts a JOIN pgbench_branches b ON (a.bid=b.bid) WHERE a.aid  100000;

               QUERY PLAN

--------------------------------------------------------------------------------

   Nested Loop (cost=0.00..4141.00 rows=99999 width=461)

     Join Filter: (a.bid = b.bid)

     -> Seq Scan on pgbench_branches b (cost=0.00..1.01 rows=1 width=364)

     -> Seq Scan on pgbench_accounts a (cost=0.00..2890.00 rows=99999 width=97)

       Filter: (aid  100000) (5 rows)

The seq scan on pgbench_accounts costs 2890 to execute the task. Where does this value come from? If we look at some settings and do the calculations, we find:

cost = ( #blocks * seq_page_cost ) + ( #records * cpu_tuple_cost ) + ( #records * cpu_filter_cost )



postgres=# select pg_relation_size('pgbench_accounts');

  pg_relation_size

------------------

     13434880



block_size    = 8192    (8kB, typical OS)

#blocks    = 1640 (relation_size / block_size)

#records    = 100000

seq_page_cost    = 1 (default)

cpu_tuple_cost    = 0.01 (default)

cpu_filter_cost    = 0.0025 (default)



cost = ( 1640 * 1 ) + ( 100000 * 0.01 ) + ( 100000 * 0.0025 ) = 2890

The costs are directly based on internal statistics with which the query optimizer can work.

A Note About Statistics in PostgreSQL Query Optimization

The query optimizer calculates costs based on statistics stored in pg_statistic. However, this data is not presented in a human-readable format, so it’s not useful for direct examination. For better visibility into the table and row statistics, try looking at pg_stats.

Suppose any of these internal statistics are off (i.e., a bloated table or too many joins that cause the genetic query optimizer to kick in). In that case, a sub-optimal plan may be selected, leading to poor query performance. Having bad statistics isn’t necessarily a problem; they aren’t always updated in real-time, and much of it depends on PostgreSQL’s internal maintenance. So, database maintenance must be conducted regularly, which means frequent VACUUM-ing and ANALYZE-ing.

Without good statistics, you could end up with something like this:

postgres=# EXPLAIN SELECT * FROM pgbench_history WHERE aid  100;

         QUERY PLAN

----------------------------------------------------------------------

Seq Scan on pgbench_history (cost=0.00..2346.00 rows=35360 width=50)

   Filter: (aid  100)

The database went through a fair amount of activity, and the statistics are inaccurate. With an ANALYZE (not VACUUM ANALYZE or EXPLAIN ANALYZE, just ANALYZE), the statistics are fixed, and the query optimizer now chooses an index scan:

postgres=# EXPLAIN SELECT * FROM pgbench_history WHERE aid  100;

        QUERY PLAN

---------------------------------------------------------------------

 Index Scan using foo on pgbench_history (cost=0.42..579.09 rows=153 width=50)

   Index Cond: (aid  100)

How Does EXPLAIN ANALYZE Help Performance Tuning?

When an EXPLAIN is prepended to a query, the query plan gets printed, but the query is not run. As a result, we can't ascertain whether the statistics stored in the database are accurate, nor can we determine if certain operations necessitated costly I/O instead of executing entirely in memory. When used with ANALYZE, the query is run, and the query plan and some under-the-hood activity are printed out.

If we look at the first query above and run EXPLAIN ANALYZE instead of EXPLAIN, we get:

postgres=# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts a JOIN pgbench_branches b ON (a.bid=b.bid) WHERE a.aid  100000;

         QUERY PLAN

-------------------------------------------------------------------------------------------------------------

Nested Loop (cost=0.00..4141.00 rows=99999 width=461) (actual time=0.039..56.582 rows=99999 loops=1)

Join Filter: (a.bid = b.bid)

-> Seq Scan on pgbench_branches b (cost=0.00..1.01 rows=1 width=364) (actual time=0.025..0.026 rows=1 loops=1)

-> Seq Scan on pgbench_accounts a (cost=0.00..2890.00 rows=99999 width=97) (actual time=0.008..25.752 rows=99999 loops=1)

    Filter: (aid  100000)

    Rows Removed by Filter: 1

Planning Time: 0.306 ms

Execution Time: 61.031 ms

(8 rows)

There’s more information: actual time and rows, as well as planning and execution times. If we add BUFFERS, like EXPLAIN (ANALYZE, BUFFERS), we’ll get cache hit/miss statistics in the output:

postgres=# EXPLAIN (BUFFERS, ANALYZE) SELECT * FROM pgbench_accounts a JOIN pgbench_branches b ON (a.bid=b.bid) WHERE a.aid  100000;

      QUERY PLAN

------------------------------------------------------------------------------------

 Nested Loop (cost=0.00..4141.00 rows=99999 width=461) (actual time=0.039..56.582 rows=99999 loops=1)

  Join Filter: (a.bid = b.bid)

  Buffers: shared hit=3 read=1638

  -> Seq Scan on pgbench_branches b (cost=0.00..1.01 rows=1 width=364) (actual time=0.025..0.026 rows=1 loops=1)

   Buffers: shared hit=1

  -> Seq Scan on pgbench_accounts a (cost=0.00..2890.00 rows=99999 width=97) (actual time=0.008..25.752 rows=99999 loops=1)

    Filter: (aid  100000)

    Rows Removed by Filter: 1

    Buffers: shared hit=2 read=1638

Planning Time: 0.306 ms

Execution Time: 61.031 ms

(8 rows) 

EXPLAIN can be a valuable tool for people looking to understand their database performance behaviors.

A Quick Review of Scan Types and Joins for Performance Tuning

Every join type and scan type has its time and place. Some people look for the term “sequential scan” in fear, not considering if it’s worthwhile to access data another way. Take, for example, a table with two rows – it would not make sense to the query optimizer to scan the index and then go back and retrieve data from the disk when it could just scan the table and pull data out without touching the index. In this case, and for most small tables, doing a sequential scan would be more efficient.

To review the join and scan types that PostgreSQL works with:

Scan Types

Sequential scan

• A brute-force retrieval from disk
• Scans the whole table
• Fast for small tables

Index scan

• Scans all/some rows in an index; looks up rows in heap
• Causes random seek, which can be costly for old-school spindle-based disks
• Faster than a sequential scan when extracting a small number of rows for large tables

Index only scan

• Scans all/some rows in the index
• No need to look up rows in the table because the values we want are already stored in the index itself

Bitmap heap scan

• Scans index, building a bitmap of pages to visit
• Looks up only relevant pages in the table for desired rows

Join Types

Nested loops

• Scans for matching rows in the inner table for each row in the outer table
• Fast to start, best for small tables

Merge join

• Zipper operation on _sorted_ data sets
• Good for large tables
• High startup cost if an additional sort is required

Hash join

• Builds hash of inner table values, scans outer table for matches
• Only usable for equality conditions
• High startup cost but fast execution

Again, every scan type and join type has its place. It’s important that the query optimizer has good statistics to work with.

EXPLAIN could help identify things like:

• Inaccurate statistics leading to poor join/scan choices
• Maintenance activity (VACUUM and ANALYZE) not being aggressive enough
• Corrupted indexes requiring a REINDEX
• Index definition vs. query mismatch
• work_mem being set too low, preventing in-memory sorts and joins
• Poor performance due to join order listing when writing a query
• Improper ORM configuration

EXPLAIN is one of the most invaluable tools for PostgreSQL, saving lots of time.