HAWQ returns different output for same sql when optimizer parameter is changed (ON/OFF)

Question

Came across a strange behavior while working on GreenPlum HAWQ 1.1.3. Wanted to execute a simple exists query but was getting the wrong result.

gpadmin=# select version();
      version                                                                              
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.15 (Greenplum Database 4.2.0 build 1) (HAWQ 1.1.3.0 build 4609) on x86_64-unknown-linux-gnu, compiled by GCCgcc (GCC) 4.4.2 compiled on Oct 27 2013 20:
53:09
(1 row)

gpadmin=# Set optimizer=off;
SET
gpadmin=# with temp as (
          Select 1 one, 2 two 
          union all 
          Select 3,4
          ),
          temp1 as (
          Select 3 three
          )
          Select * from temp 
          where exists (Select * from temp1 where three=one);


one | two 
-----+-----
(0 rows)

gpadmin=#

So when the optimizer parameter for HAWQ is set to OFF, the query returns 0 rows which ideally should return the record which exists in temp1 and temp data set i.e 3,4.

So I tried the same query but by setting the parameter to ON

gpadmin=# Set optimizer=on;
SET
gpadmin=# 
          with temp as (
          Select 1 one, 2 two 
          union all 
          Select 3,4
          ),
          temp1 as (
          Select 3 three
          ) 
          Select * from temp
          where exists (Select * from temp1 where three=one);


one | two 
-----+-----
   3 |   4
(1 row)

Here the query returns the expected result.

Guess Greenplum engineers should come up with a solution to this erroneous behavior of their optimizer.

Further when the above query is executed on Greenplum DCA 4.2 the database crashes and restarts. I do not understand how can one release such immature/non tested product to the world. These are just some simple basic queries which should have been at least tested(alpha testing) prior to their roll out to the market. On top of this, when this concern was raised with their engineer team, the support guy from their side demanded the DDL for the tables in the above query. I guess the support person failed to understand that this query does not contain any table, it just contains a temporary data set created using a with clause.

score 1 · Answer 1 · answered Mar 04 '15 at 10:27

"optimizer" is not about deeper query optimization. It enables the completely new query optimizer called ORCA, while in disabled state the Postgres optimizer used is called planner
This issue should be submitted to Pivotal Support and thus solved by engineers
It is a corner case caused by the union all and selects on master, it works well with tables

Here's the code:

create table test (one int, two int);
insert into test values (1, 2), (3, 4);
create table test2 (three int);
insert into test2 values (3);

Select * from test 
where exists (Select * from test2 where three=one);

score 1 · Answer 2 · answered Jun 30 '16 at 08:38

I slightly rephrased your query by renaming temp to t1, and renaming temp1 to t2, so that the problem can be explained more clearly.

Your question can be answered in two folds:

1) I suppose you are using an old version of hawq, in which the query works with optimizer on while it does not work with optimizer off. In latest apache hawq, it actually does not work with optimizer on/off. The reason is that it is now falling back to planner when optimizer is on.

2) The failure of the query is due to an bug about query executor.

To be specific, it is correct that t2 in the CTE (common table expression) clause is treated as subquery scan and then materialized.

However, it generate no tuple when t2 is evaluated. Thus, the join of t1 with t2 generate no tuple.

We can see this in query execution statistics using explain analyze while running the query with optimizer = off.

   ->  Materialize  (cost=0.00..0.01 rows=1 width=0)
         Rows out:  0 rows with 0.167 ms to end of 3 scans, start offset by 0.265 ms.
         ->  Limit  (cost=0.00..0.00 rows=1 width=0)
               Rows out:  0 rows with 0.003 ms to end, start offset by 0.257 ms.
               ->  Subquery Scan t2  (cost=0.00..0.01 rows=1 width=0)
                     Rows out:  0 rows with 0.002 ms to end, start offset by 0.258 ms.
                     ->  Result  (cost=0.00..0.01 rows=1 width=0)
                           One-Time Filter: 3 = $0
                           Rows out:  0 rows with 0.001 ms to end, start offset by 0.258 ms.

We have HAWQ-884 tracking this issue and you may refer to it for update and detail.

PS: here is the details about the query execution statistics:

1) hawq 2.0 with optimizer off (planner)

show optimizer;
 optimizer
-----------
 off
(1 row)

WITH t1 AS ( SELECT 1 c1, 2 c2 UNION ALL SELECT 3 c1, 4 c2 ),
     t2 AS ( SELECT 3 c3 )
SELECT * FROM t1
WHERE EXISTS (SELECT * FROM t2 WHERE c3=c1);
 c1 | c2
----+----
(0 rows)

EXPLAIN ANALYZE
WITH t1 AS ( SELECT 1 c1, 2 c2 UNION ALL SELECT 3 c1, 4 c2 ),
     t2 AS ( SELECT 3 c3 )
SELECT * FROM t1
WHERE EXISTS (SELECT * FROM t2 WHERE c3=c1);
                                                                            QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.05..0.29 rows=72 width=8)
   Rows out:  Avg 0.0 rows x 0 workers.  Max/Last(/) 0/0 rows with 0.237/0.237 ms to end.
   ->  Limit  (cost=0.00..0.00 rows=1 width=0)
         Rows out:  Avg 0.0 rows x 0 workers.  Max/Last(/) 0/0 rows with 0.003/0.003 ms to end.
         ->  Subquery Scan t2  (cost=0.00..0.01 rows=6 width=0)
               Rows out:  Avg 0.0 rows x 0 workers.  Max/Last(/) 0/0 rows with 0.002/0.002 ms to end.
               ->  Result  (cost=0.00..0.01 rows=1 width=0)
                     One-Time Filter: 3 = $0
                     Rows out:  Avg 0.0 rows x 0 workers.  Max/Last(/) 0/0 rows with 0/0 ms to end.
   ->  Materialize  (cost=0.05..0.17 rows=12 width=8)
         Rows out:  Avg 1.0 rows x 1 workers.  Max/Last(/) 1/1 rows with 0.129/0.129 ms to end, start offset by 0.135/0.135 ms.
         ->  Append  (cost=0.00..0.04 rows=2 width=0)
               Rows out:  Avg 2.0 rows x 1 workers.  Max/Last(/) 2/2 rows with 0.002/0.002 ms to first row, 0.004/0.004 ms to end, start offset by 0.255/0.255 ms.
               ->  Result  (cost=0.00..0.01 rows=1 width=0)
                     Rows out:  Avg 1.0 rows x 1 workers.  Max/Last(/) 1/1 rows with 0.002/0.002 ms to end, start offset by 0.255/0.255 ms.
               ->  Result  (cost=0.00..0.01 rows=1 width=0)
                     Rows out:  Avg 1.0 rows x 1 workers.  Max/Last(/) 1/1 rows with 0/0 ms to end, start offset by 0.261/0.261 ms.
 Slice statistics:
   (slice0)    Executor memory: 61K bytes.
 Statement statistics:
   Memory used: 128000K bytes
 Settings:  default_hash_table_bucket_number=6; optimizer=off
 Optimizer status: legacy query optimizer
 Data locality statistics:
   no data locality information in this query
 Total runtime: 0.372 ms
(26 rows)

2) hawq 2.0 with optimizer on (orca)

show optimizer;
 optimizer
-----------
 on
(1 row)

WITH t1 AS ( SELECT 1 c1, 2 c2 UNION ALL SELECT 3 c1, 4 c2 ),
     t2 AS ( SELECT 3 c3 )
SELECT * FROM t1
WHERE EXISTS (SELECT * FROM t2 WHERE c3=c1);
 c1 | c2
----+----
(0 rows)

EXPLAIN ANALYZE
WITH t1 AS ( SELECT 1 c1, 2 c2 UNION ALL SELECT 3 c1, 4 c2 ),
     t2 AS ( SELECT 3 c3 )
SELECT * FROM t1
WHERE EXISTS (SELECT * FROM t2 WHERE c3=c1);
                                                                            QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.05..0.29 rows=72 width=8)
   Rows out:  Avg 0.0 rows x 0 workers.  Max/Last(/) 0/0 rows with 0.273/0.273 ms to end.
   ->  Limit  (cost=0.00..0.00 rows=1 width=0)
         Rows out:  Avg 0.0 rows x 0 workers.  Max/Last(/) 0/0 rows with 0.003/0.003 ms to end.
         ->  Subquery Scan t2  (cost=0.00..0.01 rows=6 width=0)
               Rows out:  Avg 0.0 rows x 0 workers.  Max/Last(/) 0/0 rows with 0.002/0.002 ms to end.
               ->  Result  (cost=0.00..0.01 rows=1 width=0)
                     One-Time Filter: 3 = $0
                     Rows out:  Avg 0.0 rows x 0 workers.  Max/Last(/) 0/0 rows with 0.001/0.001 ms to end.
   ->  Materialize  (cost=0.05..0.17 rows=12 width=8)
         Rows out:  Avg 1.0 rows x 1 workers.  Max/Last(/) 1/1 rows with 0.151/0.151 ms to end, start offset by 0.189/0.189 ms.
         ->  Append  (cost=0.00..0.04 rows=2 width=0)
               Rows out:  Avg 2.0 rows x 1 workers.  Max/Last(/) 2/2 rows with 0.003/0.003 ms to first row, 0.004/0.004 ms to end, start offset by 0.327/0.327 ms.
               ->  Result  (cost=0.00..0.01 rows=1 width=0)
                     Rows out:  Avg 1.0 rows x 1 workers.  Max/Last(/) 1/1 rows with 0.002/0.002 ms to end, start offset by 0.327/0.327 ms.
               ->  Result  (cost=0.00..0.01 rows=1 width=0)
                     Rows out:  Avg 1.0 rows x 1 workers.  Max/Last(/) 1/1 rows with 0/0 ms to end, start offset by 0.337/0.337 ms.
 Slice statistics:
   (slice0)    Executor memory: 61K bytes.
 Statement statistics:
   Memory used: 128000K bytes
 Settings:  default_hash_table_bucket_number=6
 Optimizer status: legacy query optimizer
 Data locality statistics:
   no data locality information in this query
 Total runtime: 0.468 ms
(26 rows)

3) hawq 1.x with optimizer off (planner)

show optimizer;
 optimizer
-----------
 off
(1 row)

WITH t1 AS ( SELECT 1 c1, 2 c2 UNION ALL SELECT 3 c1, 4 c2 ),
     t2 AS ( SELECT 3 c3 )
SELECT * FROM t1
WHERE EXISTS (SELECT * FROM t2 WHERE c3=c1);
 c1 | c2
----+----
(0 rows)

EXPLAIN ANALYZE
WITH t1 AS ( SELECT 1 c1, 2 c2 UNION ALL SELECT 3 c1, 4 c2 ),
     t2 AS ( SELECT 3 c3 )
SELECT * FROM t1
WHERE EXISTS (SELECT * FROM t2 WHERE c3=c1);
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..0.08 rows=4 width=8)
   Rows out:  0 rows with 0.220 ms to end, start offset by 0.093 ms.
   ->  Append  (cost=0.00..0.04 rows=2 width=0)
         Rows out:  2 rows with 0.001 ms to first row, 0.003 ms to end, start offset by 0.262 ms.
         ->  Result  (cost=0.00..0.01 rows=1 width=0)
               Rows out:  1 rows with 0.001 ms to end, start offset by 0.262 ms.
         ->  Result  (cost=0.00..0.01 rows=1 width=0)
               Rows out:  1 rows with 0.001 ms to end, start offset by 0.264 ms.
   ->  Materialize  (cost=0.00..0.01 rows=1 width=0)
         Rows out:  0 rows with 0.167 ms to end of 3 scans, start offset by 0.265 ms.
         ->  Limit  (cost=0.00..0.00 rows=1 width=0)
               Rows out:  0 rows with 0.003 ms to end, start offset by 0.257 ms.
               ->  Subquery Scan t2  (cost=0.00..0.01 rows=1 width=0)
                     Rows out:  0 rows with 0.002 ms to end, start offset by 0.258 ms.
                     ->  Result  (cost=0.00..0.01 rows=1 width=0)
                           One-Time Filter: 3 = $0
                           Rows out:  0 rows with 0.001 ms to end, start offset by 0.258 ms.
 Slice statistics:
   (slice0)    Executor memory: 61K bytes.
 Statement statistics:
   Memory used: 128000K bytes
 Settings:  optimizer=off
 Optimizer status: legacy query optimizer
 Total runtime: 0.315 ms
(24 rows)

4) hawq 1.x with optimizer on (orca)

show optimizer;
 optimizer
-----------
 on
(1 row)

WITH t1 AS ( SELECT 1 c1, 2 c2 UNION ALL SELECT 3 c1, 4 c2 ),
     t2 AS ( SELECT 3 c3 )
SELECT * FROM t1
WHERE EXISTS (SELECT * FROM t2 WHERE c3=c1);
 c1 | c2
----+----
  3 |  4
(1 row)

EXPLAIN ANALYZE
WITH t1 AS ( SELECT 1 c1, 2 c2 UNION ALL SELECT 3 c1, 4 c2 ),
     t2 AS ( SELECT 3 c3 )
SELECT * FROM t1
WHERE EXISTS (SELECT * FROM t2 WHERE c3=c1);
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Hash EXISTS Join  (cost=0.00..0.00 rows=2 width=8)
   Hash Cond: "outer".c1 = "inner".c3
   Rows out:  1 rows with 0.835 ms to first row, 2.373 ms to end, start offset by 0.194 ms.
   Executor memory:  1K bytes.
   Work_mem used:  1K bytes. Workfile: (0 spilling, 0 reused)
   Hash chain length 1.0 avg, 1 max, using 1 of 524341 buckets.
   ->  Append  (cost=0.00..0.00 rows=2 width=8)
         Rows out:  2 rows with 0.002 ms to first row, 0.004 ms to end, start offset by 1.023 ms.
         ->  Result  (cost=0.00..0.00 rows=1 width=8)
               Rows out:  1 rows with 0.001 ms to first row, 0.002 ms to end, start offset by 1.023 ms.
               ->  Result  (cost=0.00..0.00 rows=1 width=1)
                     Rows out:  1 rows with 0 ms to end, start offset by 1.024 ms.
         ->  Result  (cost=0.00..0.00 rows=1 width=8)
               Rows out:  1 rows with 0.001 ms to end, start offset by 1.026 ms.
               ->  Result  (cost=0.00..0.00 rows=1 width=1)
                     Rows out:  1 rows with 0 ms to end, start offset by 1.026 ms.
   ->  Hash  (cost=0.00..0.00 rows=1 width=4)
         Rows in:  1 rows with 0.010 ms to end, start offset by 1.013 ms.
         ->  Result  (cost=0.00..0.00 rows=1 width=4)
               Rows out:  1 rows with 0.006 ms to first row, 0.007 ms to end, start offset by 1.013 ms.
               ->  Result  (cost=0.00..0.00 rows=1 width=1)
                     Rows out:  1 rows with 0.001 ms to end, start offset by 1.014 ms.
 Slice statistics:
   (slice0)    Executor memory: 8270K bytes.  Work_mem: 1K bytes max.
 Statement statistics:
   Memory used: 128000K bytes
 Optimizer status: PQO version 1.591
 Total runtime: 2.572 ms
(28 rows)

HAWQ returns different output for same sql when optimizer parameter is changed (ON/OFF)

2 Answers2