Ruilong Huo created HAWQ-348: -------------------------------- Summary: Optimizer (ORCA/Planner) should not preprocess (table) functions at planning phase Key: HAWQ-348 URL: https://issues.apache.org/jira/browse/HAWQ-348 Project: Apache HAWQ Issue Type: Bug Components: Optimizer Reporter: Ruilong Huo Assignee: Amr El-Helw
Optimizer (ORCA/Planner) currently preprocesses (table) functions (either in target list or from clause) at planning phase. This introduces: 1. Much lower performance since the result of the (table) function is motioned to QD from QEs after the preprocessing, and it is further processed there at QD, especially the result is large. In this case, QD does heavy workload and becomes the bottleneck. It shows about 20x performance difference in below example. 2. Much memory overhead at QD since it needs to hold the result of the (table) function. This is risky since the result might be unpredictably large. Here are the steps to reproduce this issue, as well as some initial analysis: Step 1: Prepare schema and data {noformat} CREATE TABLE t (id INT); CREATE TABLE INSERT INTO t SELECT generate_series(1, 100000000); INSERT 0 100000000 CREATE OR REPLACE FUNCTION get_t() RETURNS SETOF t LANGUAGE SQL AS 'SELECT * FROM t' STABLE; CREATE FUNCTION {noformat} 2. With optimizer = OFF (Planner) {noformat} SET optimizer='OFF'; SET select sum(id) from t; sum ------------------ 5000000050000000 (1 row) Time: 8801.577 ms select sum(id) from get_t(); sum ------------------ 5000000050000000 (1 row) Time: 189992.273 ms EXPLAIN SELECT sum(id) FROM get_t(); QUERY PLAN -------------------------------------------------------------------- Aggregate (cost=32.50..32.51 rows=1 width=8) -> Function Scan on get_t (cost=0.00..12.50 rows=8000 width=4) Settings: default_segment_num=8; optimizer=off Optimizer status: legacy query optimizer (4 rows) {noformat} 3. With optimizer = ON (ORCA) {noformat} SET optimizer='ON'; SET select sum(id) from t; sum ------------------ 5000000050000000 (1 row) Time: 10103.436 ms select sum(id) from get_t(); sum ------------------ 5000000050000000 (1 row) Time: 195551.740 ms EXPLAIN SELECT sum(id) FROM get_t(); QUERY PLAN -------------------------------------------------------------------- Aggregate (cost=32.50..32.51 rows=1 width=8) -> Function Scan on get_t (cost=0.00..12.50 rows=8000 width=4) Settings: default_segment_num=8 Optimizer status: legacy query optimizer (4 rows) {noformat} -- This message was sent by Atlassian JIRA (v6.3.4#6332)