[IGNITE-22111] [SQL] TPC-H q21 query hangs with sc=0.1

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Not A Problem
• Component/s: sql
• Labels: ignite-3 ignite3_performance
• Assignee: Unassigned
• Reporter: Nikita Sivkov
• Created: 25/Apr/24 11:05
• Updated: 07/May/24 08:55
• Resolved: 07/May/24 08:55

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

Actual result

The benchmark hangs for hours on Q21 query:

 

SELECT
   s_name,
   COUNT(*) AS numwait
FROM
   supplier,
   lineitem l1,
   orders,
   nation
WHERE
   s_suppkey = l1.l_suppkey
   AND o_orderkey = l1.l_orderkey
   AND o_orderstatus = 'F'
   AND l1.l_receiptdate > l1.l_commitdate
   AND EXISTS
   (
      SELECT
         *
      FROM
         lineitem l2
      WHERE
         l2.l_orderkey = l1.l_orderkey
         AND l2.l_suppkey <> l1.l_suppkey
   )
   AND NOT EXISTS
   (
      SELECT
         *
      FROM
         lineitem l3
      WHERE
         l3.l_orderkey = l1.l_orderkey
         AND l3.l_suppkey <> l1.l_suppkey
         AND l3.l_receiptdate > l3.l_commitdate
   )
   AND s_nationkey = n_nationkey
   AND n_name = ?
GROUP BY
   s_name
ORDER BY
   numwait DESC,
   s_name LIMIT 100

Issue Links

• relates to: IGNITE-22112 [SQL] TPC-H q9 query with sc=0.1 takes long time
• 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