From dcefa329002f5c7518299f3ce71c4628ade1492d Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Thu, 4 May 2017 22:09:56 +0200 Subject: [PATCH] Resolve failure due to changing cost in xl_join regression suite The regression failures were caused by slightly fluctuating cost estimates. In general it's not a good idea to rely on costs being constant in regression tests, so use EXPLAIN (COSTS OFF) instead. This stabilizes the output, and resolves the regression failure. Note: The cost estimates probably change due to stale oldestXmin reported by one of the nodes, so that analyze does not see some of the recent changes. That is an independent issue, however, and it does not really change that relying on cost estimates being constant is not a good practice. --- src/test/regress/expected/xl_join.out | 29 ++++++++++++++------------- src/test/regress/sql/xl_join.sql | 3 ++- 2 files changed, 17 insertions(+), 15 deletions(-) diff --git a/src/test/regress/expected/xl_join.out b/src/test/regress/expected/xl_join.out index 9c1cd3ac10..463e1baa4c 100644 --- a/src/test/regress/expected/xl_join.out +++ b/src/test/regress/expected/xl_join.out @@ -4,28 +4,29 @@ CREATE TABLE xl_join_t3 (val1 int, val2 int); INSERT INTO xl_join_t1 VALUES (1,10),(2,20); INSERT INTO xl_join_t2 VALUES (3,30),(4,40); INSERT INTO xl_join_t3 VALUES (5,50),(6,60); -EXPLAIN SELECT * FROM xl_join_t1 +EXPLAIN (COSTS OFF) +SELECT * FROM xl_join_t1 INNER JOIN xl_join_t2 ON xl_join_t1.val1 = xl_join_t2.val2 INNER JOIN xl_join_t3 ON xl_join_t1.val1 = xl_join_t3.val1; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- - Remote Subquery Scan on all (datanode_1,datanode_2) (cost=575.52..13678.66 rows=288579 width=24) - -> Merge Join (cost=475.52..5209.87 rows=288579 width=24) + QUERY PLAN +----------------------------------------------------------------------------- + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Merge Join Merge Cond: (xl_join_t3.val1 = xl_join_t1.val1) - -> Sort (cost=158.51..164.16 rows=2260 width=8) + -> Sort Sort Key: xl_join_t3.val1 - -> Seq Scan on xl_join_t3 (cost=0.00..32.60 rows=2260 width=8) - -> Materialize (cost=317.01..775.23 rows=25538 width=16) - -> Merge Join (cost=317.01..711.38 rows=25538 width=16) + -> Seq Scan on xl_join_t3 + -> Materialize + -> Merge Join Merge Cond: (xl_join_t2.val2 = xl_join_t1.val1) - -> Remote Subquery Scan on all (datanode_1,datanode_2) (cost=258.51..293.54 rows=2260 width=8) + -> Remote Subquery Scan on all (datanode_1,datanode_2) Distribute results by H: val2 - -> Sort (cost=158.51..164.16 rows=2260 width=8) + -> Sort Sort Key: xl_join_t2.val2 - -> Seq Scan on xl_join_t2 (cost=0.00..32.60 rows=2260 width=8) - -> Sort (cost=158.51..164.16 rows=2260 width=8) + -> Seq Scan on xl_join_t2 + -> Sort Sort Key: xl_join_t1.val1 - -> Seq Scan on xl_join_t1 (cost=0.00..32.60 rows=2260 width=8) + -> Seq Scan on xl_join_t1 (17 rows) SELECT * FROM xl_join_t1 diff --git a/src/test/regress/sql/xl_join.sql b/src/test/regress/sql/xl_join.sql index ed2829daff..1b63d20a07 100644 --- a/src/test/regress/sql/xl_join.sql +++ b/src/test/regress/sql/xl_join.sql @@ -7,7 +7,8 @@ INSERT INTO xl_join_t1 VALUES (1,10),(2,20); INSERT INTO xl_join_t2 VALUES (3,30),(4,40); INSERT INTO xl_join_t3 VALUES (5,50),(6,60); -EXPLAIN SELECT * FROM xl_join_t1 +EXPLAIN (COSTS OFF) +SELECT * FROM xl_join_t1 INNER JOIN xl_join_t2 ON xl_join_t1.val1 = xl_join_t2.val2 INNER JOIN xl_join_t3 ON xl_join_t1.val1 = xl_join_t3.val1; -- 2.39.5