Determining The Query Optimizer In Use

When GPORCA is enabled, you can determine if HAWQ is using GPORCA or is falling back to the legacy query optimizer.

These are two ways to determine which query optimizer HAWQ used to execute the query:

  • Examine EXPLAIN query plan output for the query. (Your output may include other settings.)

    • When GPORCA generates the query plan, the GPORCA version is displayed near the end of the query plan . For example.

      1.  Settings:  optimizer=on
      2.  Optimizer status:  PQO version 1.627

      When HAWQ falls back to the legacy optimizer to generate the plan, legacy query optimizer is displayed near the end of the query plan. For example.

      1.  Settings:  optimizer=on
      2.  Optimizer status: legacy query optimizer

      When the server configuration parameter OPTIMIZER is off, the following lines are displayed near the end of a query plan.

      1.  Settings:  optimizer=off
      2.  Optimizer status: legacy query optimizer

    • These plan items appear only in the EXPLAIN plan output generated by GPORCA. The items are not supported in a legacy optimizer query plan.

      • Assert operator
      • Sequence operator
      • DynamicIndexScan
      • DynamicTableScan
      • Table Scan
    • When a query against a partitioned table is generated by GPORCA, the EXPLAIN plan displays only the number of partitions that are being eliminated is listed. The scanned partitions are not shown. The EXPLAIN plan generated by the legacy optimizer lists the scanned partitions.

  • View the log messages in the HAWQ log file.

    The log file contains messages that indicate which query optimizer was used. In the log file message, the [OPT] flag appears when GPORCA attempts to optimize a query. If HAWQ falls back to the legacy optimizer, an error message is added to the log file, indicating the unsupported feature. Also, in the message, the label Planner produced plan: appears before the query when HAWQ falls back to the legacy optimizer.

    Note: You can configure HAWQ to display log messages on the psql command line by setting the HAWQ server configuration parameter client_min_messages to LOG. See Server Configuration Parameter Reference for information about the parameter.

Example

This example shows the differences for a query that is run against partitioned tables when GPORCA is enabled.

This CREATE TABLE statement creates a table with single level partitions:

  1. CREATE TABLE sales (trans_id int, date date, 
  2.     amount decimal(9,2), region text)
  3.    DISTRIBUTED BY (trans_id)
  4.    PARTITION BY RANGE (date)
  5.       (START (date '2011­01­01') 
  6.        INCLUSIVE END (date '2012­01­01') 
  7.        EXCLUSIVE EVERY (INTERVAL '1 month'),
  8.    DEFAULT PARTITION outlying_dates);

This query against the table is supported by GPORCA and does not generate errors in the log file:

  1. SELECT * FROM sales;

The EXPLAIN plan output lists only the number of selected partitions.

  1.  ->  Partition Selector for sales (dynamic scan id: 1)  (cost=10.00..100.00 rows=50 width=4)
  2.        Partitions selected:  13 (out of 13)

Output from the log file indicates that GPORCA attempted to optimize the query:

  1. 2015-05-06 15:00:53.293451 PDT,"gpadmin","test",p2809,th297883424,"[local]",
  2.   ,2015-05-06 14:59:21 PDT,1120,con6,cmd1,seg-1,,dx3,x1120,sx1,"LOG","00000"
  3.   ,"statement: explain select * from sales
  4. ;",,,,,,"explain select * from sales
  5. ;",0,,"postgres.c",1566,
  7. 2015-05-06 15:00:54.258412 PDT,"gpadmin","test",p2809,th297883424,"[local]",
  8.   ,2015-05-06 14:59:21 PDT,1120,con6,cmd1,seg-1,,dx3,x1120,sx1,"LOG","00000","
  9. [OPT]: Using default search strategy",,,,,,"explain select * from sales
  10. ;",0,,"COptTasks.cpp",677,

The following cube query is not supported by GPORCA.

  1. SELECT count(*) FROM foo GROUP BY cube(a,b);

The following EXPLAIN plan output includes the message “Feature not supported by GPORCA.”

  1. postgres=# EXPLAIN SELECT count(*) FROM foo GROUP BY cube(a,b);
  1. LOG:  statement: explain select count(*) from foo group by cube(a,b);
  2. LOG:  2016-04-14 16:26:15:487935 PDT,THD000,NOTICE,"Feature not supported by the GPORCA: Cube",
  3. LOG:  Planner produced plan :0
  4.                                                         QUERY PLAN
  5. ---------------------------------------------------------------------------------------------------------------------------
  6.  Gather Motion 3:1  (slice3; segments: 3)  (cost=9643.62..19400.26 rows=40897 width=28)
  7.    ->  Append  (cost=9643.62..19400.26 rows=13633 width=28)
  8.          ->  HashAggregate  (cost=9643.62..9993.39 rows=9328 width=28)
  9.                Group By: "rollup".unnamed_attr_2, "rollup".unnamed_attr_1, "rollup"."grouping", "rollup"."group_id"
  10.                ->  Subquery Scan "rollup"  (cost=8018.50..9589.81 rows=1435 width=28)
  11.                      ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=8018.50..9546.76 rows=1435 width=28)
  12.                            Hash Key: "rollup".unnamed_attr_2, "rollup".unnamed_attr_1, "grouping", group_id()
  13.                            ->  GroupAggregate  (cost=8018.50..9460.66 rows=1435 width=28)
  14.                                  Group By: "rollup"."grouping", "rollup"."group_id"
  15.                                  ->  Subquery Scan "rollup"  (cost=8018.50..9326.13 rows=2153 width=28)
  16.                                        ->  GroupAggregate  (cost=8018.50..9261.56 rows=2153 width=28)
  17.                                              Group By: "rollup".unnamed_attr_2, "rollup"."grouping", "rollup"."group_id"
  18.                                              ->  Subquery Scan "rollup"  (cost=8018.50..9073.22 rows=2870 width=28)
  19.                                                    ->  GroupAggregate  (cost=8018.50..8987.12 rows=2870 width=28)
  20.                                                          Group By: public.foo.b, public.foo.a
  21.                                                          ->  Sort  (cost=8018.50..8233.75 rows=28700 width=8)
  22.                                                                Sort Key: public.foo.b, public.foo.a
  23.                                                                ->  Seq Scan on foo  (cost=0.00..961.00 rows=28700 width=8)
  24.          ->  HashAggregate  (cost=9116.27..9277.71 rows=4305 width=28)
  25.                Group By: "rollup".unnamed_attr_1, "rollup".unnamed_attr_2, "rollup"."grouping", "rollup"."group_id"
  26.                ->  Subquery Scan "rollup"  (cost=8018.50..9062.46 rows=1435 width=28)
  27.                      ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=8018.50..9019.41 rows=1435 width=28)
  28.                            Hash Key: public.foo.a, public.foo.b, "grouping", group_id()
  29.                            ->  GroupAggregate  (cost=8018.50..8933.31 rows=1435 width=28)
  30.                                  Group By: public.foo.a
  31.                                  ->  Sort  (cost=8018.50..8233.75 rows=28700 width=8)
  32.                                        Sort Key: public.foo.a
  33.                                        ->  Seq Scan on foo  (cost=0.00..961.00 rows=28700 width=8)
  34.  Settings:  optimizer=on
  35.  Optimizer status: legacy query optimizer
  36. (30 rows)

Since this query is not supported by GPORCA, HAWQ falls back to the legacy optimizer.