[IGNITE-22112] [SQL] TPC-H q9 query with sc=0.1 takes long time

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: sql
• Labels: ignite-3 ignite3_performance
• Assignee: Unassigned
• Reporter: Nikita Sivkov
• Created: 25/Apr/24 11:13
• Updated: 25/Apr/24 11:21

Description

Benchmark: https://github.com/cmu-db/benchbase/tree/main/src/main/java/com/oltpbenchmark/benchmarks/tpch 

Setup

• 1 server node
• TPC-H with scale factor = 0.1

Steps

1. Start an Ignite node
2. Run benchbase with -s 1 --create=true --load=true --execute=false to preload data
3. Observe via the benchbase log that the data was successfully loaded
4. Run benchbase with -s 1 --create=false --load=false --execute=true to run the benchmark

Expected result

The benchmark finishes after warmup + duration time in about the same time as other requests.

Actual result

The Q9 query has significantly longer execution time. For example, it may take 10 minutes, while the rest queries take ~1-15 seconds.

SELECT
   nation,
   o_year,
   SUM(amount) AS sum_profit
FROM
   (
      SELECT
         n_name AS nation,
         EXTRACT(YEAR
      FROM
         o_orderdate) AS o_year,
         l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
      FROM
         part,
         supplier,
         lineitem,
         partsupp,
         orders,
         nation
      WHERE
         s_suppkey = l_suppkey
         AND ps_suppkey = l_suppkey
         AND ps_partkey = l_partkey
         AND p_partkey = l_partkey
         AND o_orderkey = l_orderkey
         AND s_nationkey = n_nationkey
         AND p_name LIKE ?
   )
   AS profit
GROUP BY
   nation,
   o_year
ORDER BY
   nation,
   o_year DESC

Issue Links

• is related to: IGNITE-22111 [SQL] TPC-H q21 query hangs with sc=0.1
• relates to: IGNITE-20880 Some TPC-H queries hang
• relates to: IGNITE-22109 [SQL] TPC-H q4 query hangs with sc=0.1
• relates to: IGNITE-22110 [SQL] TPC-H q16 query hangs with sc=0.1