On Wed, Jun 10, 2015 at 9:57 PM, Kouhei Kaigai <kai...@ak.jp.nec.com> wrote: > 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 alloc request 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.24 rows=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.21 rows=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.20 rows=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.20 rows=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 Scan on 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_idx on 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_part1 on > 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)
curious: what was work_mem set to? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
