[DRILL-7109] Statistics adds external sort, which spills to disk

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.16.0
• Fix Version/s: 1.16.0
• Component/s: Query Planning & Optimization
• Assignee: Gautam Parai
• Reporter: Robert Hou
• Created: 16/Mar/19 00:20
• Updated: 22/Mar/19 23:57
• Resolved: 22/Mar/19 23:54

Description

TPCH query 4 with sf 100 runs many times slower. One issue is that an extra external sort has been added, and both external sorts spill to disk.

Also, the hash join sees 100x more data.

Here is the query:

select
  o.o_orderpriority,
  count(*) as order_count
from
  orders o

where
  o.o_orderdate >= date '1996-10-01'
  and o.o_orderdate < date '1996-10-01' + interval '3' month
  and 
  exists (
    select
      *
    from
      lineitem l
    where
      l.l_orderkey = o.o_orderkey
      and l.l_commitdate < l.l_receiptdate
  )
group by
  o.o_orderpriority
order by
  o.o_orderpriority;

Issue Links

• links to: GitHub Pull Request #1701