DBT-3 with SF=20 got failed - Mailing list pgsql-hackers

Hello,

I got the following error during DBT-3 benchmark with SF=20.
 psql:query21.sql:50: ERROR:  invalid memory alloc request size 1073741824 psql:query21.sql:50: ERROR:  invalid memory
allocrequest size 1073741824 

It looks to me Hash node tries to 1GB area using palloc0(), but it exceeds
the limitation of none-huge interface.

(gdb) bt
#0  0x00007f669d29a989 in raise () from /lib64/libc.so.6
#1  0x00007f669d29c098 in abort () from /lib64/libc.so.6
#2  0x000000000090ccfd in ExceptionalCondition (conditionName=0xb18130 "!(((Size) (size) <= ((Size) 0x3fffffff)))",
errorType=0xb17efd"FailedAssertion", fileName=0xb17e40 "mcxt.c", lineNumber=856) at assert.c:54 
#3  0x000000000093ad53 in palloc0 (size=1073741824) at mcxt.c:856
#4  0x0000000000673045 in ExecHashTableCreate (node=0x7f669de951f0, hashOperators=0x24dbf90, keepNulls=0 '\000') at
nodeHash.c:391
#5  0x00000000006752e1 in ExecHashJoin (node=0x24d74e0) at nodeHashjoin.c:169
#6  0x000000000065abf4 in ExecProcNode (node=0x24d74e0) at execProcnode.c:477
#7  0x0000000000681026 in ExecNestLoop (node=0x24d6668) at nodeNestloop.c:123
#8  0x000000000065abca in ExecProcNode (node=0x24d6668) at execProcnode.c:469
#9  0x0000000000681026 in ExecNestLoop (node=0x24d61f8) at nodeNestloop.c:123
#10 0x000000000065abca in ExecProcNode (node=0x24d61f8) at execProcnode.c:469
#11 0x0000000000681026 in ExecNestLoop (node=0x24d5478) at nodeNestloop.c:123
#12 0x000000000065abca in ExecProcNode (node=0x24d5478) at execProcnode.c:469
#13 0x0000000000681026 in ExecNestLoop (node=0x24d51d0) at nodeNestloop.c:123
#14 0x000000000065abca in ExecProcNode (node=0x24d51d0) at execProcnode.c:469

The attached patch replaces this palloc0() by MemoryContextAllocHuge() + memset().
Indeed, this hash table is constructed towards the relation with nrows=119994544,
so, it is not strange even if hash-slot itself is larger than 1GB.

Another allocation request potentially reset of expand hash-slot may also needs
to be "Huge" version of memory allocation, I think.

Thanks,

Below is the query itself and EXPLAIN result.
--------------------------------------------------------------------
dbt3c=# EXPLAIN VERBOSE
dbt3c-# select
dbt3c-#         s_name,
dbt3c-#         count(*) as numwait
dbt3c-# from
dbt3c-#         supplier,
dbt3c-#         lineitem l1,
dbt3c-#         orders,
dbt3c-#         nation
dbt3c-# where
dbt3c-#         s_suppkey = l1.l_suppkey
dbt3c-#         and o_orderkey = l1.l_orderkey
dbt3c-#         and o_orderstatus = 'F'
dbt3c-#         and l1.l_receiptdate > l1.l_commitdate
dbt3c-#         and exists (
dbt3c(#                 select
dbt3c(#                         *
dbt3c(#                 from
dbt3c(#                         lineitem l2
dbt3c(#                 where
dbt3c(#                         l2.l_orderkey = l1.l_orderkey
dbt3c(#                         and l2.l_suppkey <> l1.l_suppkey
dbt3c(#         )
dbt3c-#         and not exists (
dbt3c(#                 select
dbt3c(#                         *
dbt3c(#                 from
dbt3c(#                         lineitem l3
dbt3c(#                 where
dbt3c(#                         l3.l_orderkey = l1.l_orderkey
dbt3c(#                         and l3.l_suppkey <> l1.l_suppkey
dbt3c(#                         and l3.l_receiptdate > l3.l_commitdate
dbt3c(#         )
dbt3c-#         and s_nationkey = n_nationkey
dbt3c-#         and n_name = 'UNITED KINGDOM'
dbt3c-# group by
dbt3c-#         s_name
dbt3c-# order by
dbt3c-#         numwait desc,
dbt3c-#         s_name
dbt3c-# LIMIT 100;
   QUERY PLAN


--------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------
------------------Limit  (cost=6792765.24..6792765.24 rows=1 width=26)  Output: supplier.s_name, (count(*))  ->  Sort
(cost=6792765.24..6792765.24rows=1 width=26)        Output: supplier.s_name, (count(*))        Sort Key: (count(*))
DESC,supplier.s_name        ->  HashAggregate  (cost=6792765.22..6792765.23 rows=1 width=26)              Output:
supplier.s_name,count(*)              Group Key: supplier.s_name              ->  Nested Loop Anti Join
(cost=4831094.94..6792765.21rows=1 width=26)                    Output: supplier.s_name                    ->  Nested
Loop (cost=4831094.37..6792737.52 rows=1 width=34)                          Output: supplier.s_name, l1.l_suppkey,
l1.l_orderkey                         Join Filter: (supplier.s_nationkey = nation.n_nationkey)
-> Nested Loop  (cost=4831094.37..6792736.19 rows=1 width=38)                                Output: supplier.s_name,
supplier.s_nationkey,l1.l_suppkey, l1.l_orderkey                                ->  Nested Loop
(cost=4831093.81..6792728.20rows=1 width=42)                                      Output: supplier.s_name,
supplier.s_nationkey,l1.l_suppkey, l1.l_orderkey, l2.l_orderkey                                      Join Filter:
(l1.l_suppkey= supplier.s_suppkey)                                      ->  Hash Semi Join
(cost=4831093.81..6783870.20rows=1 width=12)                                            Output: l1.l_suppkey,
l1.l_orderkey,l2.l_orderkey                                            Hash Cond: (l1.l_orderkey = l2.l_orderkey)
                                    Join Filter: (l2.l_suppkey <> l1.l_suppkey)
  ->  Index Scan using lineitem_l_orderkey_idx_part1 on public.lineitem l1  (cost=0.57..1847781.73 rows 
=39998181 width=8)                                                  Output: l1.l_orderkey, l1.l_partkey, l1.l_suppkey,
l1.l_linenumber,l1.l_quantity, l1.l_extende 
dprice, l1.l_discount, l1.l_tax, l1.l_returnflag, l1.l_linestatus, l1.l_shipdate, l1.l_commitdate, l1.l_receiptdate,
l1.l_shipinstruct,l1.l_shipm 
ode, l1.l_comment                                            ->  Hash  (cost=3331161.44..3331161.44 rows=119994544
width=8)                                                 Output: l2.l_orderkey, l2.l_suppkey
                     ->  Seq Scan on public.lineitem l2  (cost=0.00..3331161.44 rows=119994544 width=8)
                                      Output: l2.l_orderkey, l2.l_suppkey                                      ->  Seq
Scanon public.supplier  (cost=0.00..6358.00 rows=200000 width=34)                                            Output:
supplier.s_suppkey,supplier.s_name, supplier.s_address, supplier.s_nationkey, supplier.s_pho 
ne, supplier.s_acctbal, supplier.s_comment                                ->  Index Scan using
orders_o_orderkey_o_orderdate_idxon public.orders  (cost=0.56..7.98 rows=1 width=4)
Output: orders.o_orderkey, orders.o_custkey, orders.o_orderstatus, orders.o_totalprice,
orders.o_orderdate,orders.o_orderpriority,orders.o_clerk, orders.o_shippriority, orders.o_comment
              Index Cond: (orders.o_orderkey = l1.l_orderkey)                                      Filter:
(orders.o_orderstatus= 'F'::bpchar)                          ->  Seq Scan on public.nation  (cost=0.00..1.31 rows=1
width=4)                               Output: nation.n_nationkey, nation.n_name, nation.n_regionkey, nation.n_comment
                             Filter: (nation.n_name = 'UNITED KINGDOM'::bpchar)                    ->  Index Scan using
lineitem_l_orderkey_idx_part1on public.lineitem l3  (cost=0.57..13.69 rows=89 width=8)                          Output:
l3.l_orderkey,l3.l_partkey, l3.l_suppkey, l3.l_linenumber, l3.l_quantity, l3.l_extendedprice, l3.l_discount, l 
3.l_tax, l3.l_returnflag, l3.l_linestatus, l3.l_shipdate, l3.l_commitdate, l3.l_receiptdate, l3.l_shipinstruct,
l3.l_shipmode,l3.l_comment                          Index Cond: (l3.l_orderkey = l1.l_orderkey)
Filter: (l3.l_suppkey <> l1.l_suppkey) 
(41 rows)

--
NEC Business Creation Division / PG-Strom Project
KaiGai Kohei <kaigai@ak.jp.nec.com>