[IGNITE-22109] [SQL] TPC-H q4 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:02
• Updated: 07/May/24 08:54
• Resolved: 07/May/24 08:54

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 Q4 query:

 

SELECT
   o_orderpriority,
   COUNT(*) AS order_count
FROM
   orders 
WHERE
   o_orderdate >= ?::date
   AND o_orderdate < ?::date + INTERVAL '3' MONTH
   AND EXISTS
   (
      SELECT
         *
      FROM
         lineitem
      WHERE 
         l_orderkey = o_orderkey 
         AND l_commitdate < l_receiptdate 
   )
GROUP BY 
   o_orderpriority
ORDER BY
   o_orderpriority

Issue Links

• is related to: IGNITE-22112 [SQL] TPC-H q9 query with sc=0.1 takes long time
• is related to: IGNITE-22110 [SQL] TPC-H q16 query hangs with sc=0.1
• is related to: IGNITE-22111 [SQL] TPC-H q21 query hangs with sc=0.1
• relates to: IGNITE-20880 Some TPC-H queries hang