Tuning Operators

Background

A query goes through many steps to produce its final result. Often, the whole query slows down because one step takes too long. This slow step is called a bottleneck. To fix this, you can run the EXPLAIN ANALYZE or PERFORMANCE command to find the bottleneck.

In the example below, the Hashagg operator takes up 66% of the total time: (51016 – 13535)/56476 ≈ 66%. So, the Hashagg operator is the bottleneck. Start by optimizing this operator to improve performance.

Operator Tuning Example

Example 1: When scanning a base table with SeqScan, filtering large amounts of data, like in point queries or range scans, can be slow. Create an index on the condition column and use IndexScan to speed up the process.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

 explain (analyze on, costs off) select * from store_sales where ss_sold_date_sk = 2450944;
 id |             operation          |       A-time        | A-rows | Peak Memory  | A-width
----+--------------------------------+---------------------+--------+--------------+---------
  1 | ->  Streaming (type: GATHER)   | 3666.020            |   3360 | 195KB        |
  2 |    ->  Seq Scan on store_sales | [3594.611,3594.611] |   3360 | [34KB, 34KB] |

 Predicate Information (identified by plan id) 
-----------------------------------------------
   2 --Seq Scan on store_sales
         Filter: (ss_sold_date_sk = 2450944)
         Rows Removed by Filter: 4968936

1
2
3
4
5
6
7

 create index idx on store_sales_row(ss_sold_date_sk);
CREATE INDEX
 explain (analyze on, costs off) select * from store_sales_row where ss_sold_date_sk = 2450944;
 id |                   operation                    |     A-time      | A-rows | Peak Memory  | A-width
----+------------------------------------------------+-----------------+--------+--------------+----------
  1 | ->  Streaming (type: GATHER)                   | 81.524          |   3360 | 195KB        |
  2 |    ->  Index Scan using idx on store_sales_row | [13.352,13.352] |   3360 | [34KB, 34KB] |

For instance, a full table scan returned 3,360 records but took 3.6 seconds. After indexing the ss_sold_date_sk column, the scan time dropped to 13 milliseconds.

Example 2: If NestLoop is chosen to join two tables and the row count is high, the join can be slow. In one case, NestLoop took 181 seconds. By setting enable_mergejoin and enable_nestloop to off and letting the optimizer choose HashJoin, the join time was reduced to over 200 milliseconds.

Example 3: HashAgg usually performs better. For large result sets, if Sort and GroupAgg are used, set enable_sort to off. HashAgg takes much longer time than Sort and GroupAgg used together.