Configure parallel query

How it works

When your instance uses compute nodes with 4 or more cores, parallel query is enabled by default for individual table queries. The system selects the degree of parallelism automatically based on three factors: the number of concurrent queries, the compute node specifications, and the SQL statement being executed. Under high workload, the system disables parallel query to preserve overall throughput.

Parallel query applies to single-table queries only.

Requirements

Parallel query is supported on instances that meet all of the following conditions:

To update the minor engine version, see Update the minor engine version.

Example

The following example uses an instance with these specifications:

Import 10 GB of test data into the database. The following shows the single-table query results before and after parallel query.

• Before parallel query

  
  postgres=# select
  l_returnflag,
  l_linestatus,
  sum(l_quantity) as sum_qty,
  sum(l_extendedprice) as sum_base_price,
  sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
  sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
  avg(l_quantity) as avg_qty,
  avg(l_extendedprice) as avg_price,
  avg(l_discount) as avg_disc,
  count(*) as count_order
  from
  lineitem
  where
  l_shipdate <= date '1998-12-01' - interval '90' day
  group by
  l_returnflag,
  l_linestatus
  order by
  l_returnflag,
  l_linestatus;
   l_returnflag | l_linestatus |  sum_qty   | sum_base_price  |  sum_disc_price     |     sum_charge       |       avg_qty        |     avg_price       |        avg_disc          | count_order
  --------------+--------------+-----------+-----------------+---------------------+----------------------+----------------------+---------------------+--------------------------+------------
   A            | F            | 377518399 | 566065727797.25 | 537759104278.0656   | 559276670892.116819  | 25.5009751030070973  | 38237.151008958546  | 0.05000065745402432046   3 |  14804077
   N            | F            |   9851614 |  14767438399.17 |  14028805792.2114   |  14590490998.366737  | 25.5224483028409474  | 38257.810660081140  | 0.04997336773765667183   0 |    385998
   N            | O            | 743124873 | 1114302286901.88| 1058580922144.9638  | 1100937000170.591854 | 25.4980758706893147  | 38233.902923481810  | 0.05000081182131310603   3 |  29144351
   R            | F            | 377732830 | 566431054976.00 |  538110922664.7677  |  559634780885.086257 | 25.5083847896801383  | 38251.219273559761  | 0.04999967923140874204   5 |  14808183
  (4 rows)
  
  Time: 17456.066 ms
  

• After parallel query

  
  postgres=# select
  l_returnflag,
  l_linestatus,
  sum(l_quantity) as sum_qty,
  sum(l_extendedprice) as sum_base_price,
  sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
  sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
  avg(l_quantity) as avg_qty,
  avg(l_extendedprice) as avg_price,
  avg(l_discount) as avg_disc,
  count(*) as count_order
  from
  lineitem
  where
  l_shipdate <= date '1998-12-01' - interval '90' day
  group by
  l_returnflag,
  l_linestatus
  order by
  l_returnflag,
  l_linestatus;
   l_returnflag | l_linestatus | sum_qty   | sum_base_price   | sum_disc_price     | sum_charge          | avg_qty             | avg_price          | avg_disc                | count_order
  --------------+--------------+-----------+------------------+--------------------+---------------------+--------------------+--------------------+------------------------+-------------
   A            | F            | 377518399 | 566065727797.25  | 537759104278.0656  | 559276670892.116819 | 25.5009751030070973 | 38237.151008958546 | 0.050000657454024320463 |    14804077
   N            | F            |   9851614 | 14767438399.17   | 14028805792.2114   | 14590490998.366737  | 25.5224483028409474 | 38257.810660081140 | 0.049973367737656567180 |      385998
   N            | O            | 743124873 | 1114302286901.88 | 1058580922144.9638 | 1100937000170.591854 | 25.4980758706893147 | 38233.902923481810 | 0.05000081182113130603 |    29144351
   R            | F            | 377732830 | 566431054976.00  |  538110922664.7677 |  559634780885.086257 | 25.5083847896801383 | 38251.219273559761 | 0.04999679231408742045 |    14808183
  (4 rows)
  
  Time: 9407.291 ms