Re: query causes connection termination

Only complementing
I use postgresql version 10.
However the postgresql.conf file has standard settings.
My server is a 2.8 GHz Xeon (4 core) and SSDs disc.

2017-11-22 21:12 GMT-03:00 Neto pr <netoprbr9(at)gmail(dot)com>:

> Dear all,
> when executing a query, it causes the database to close the connection.
> See the error reported by the SQL TOOL DBEAVER tool:
>
> ----- -------- DBEAVER SQL tool---------------------------------
> An I / O error occurred while sending to the backend.
> java.io.EOFException:
> ------------------------------------------------------------
> ---------------
>
> I tried to execute the query in PSQL but the same thing happens. The query
> is the 19 of the TPC-H Benchmark.
>
> ---------------PSQL Cliente Sql --------------------------
> tpch40gnorssd=# EXPLAIN (ANALYZE) select sum(l_extendedprice* (1 -
> l_discount)) as revenue
> tpch40gnorssd-# from lineitem, part
>
> tpch40gnorssd-# where (
>
> tpch40gnorssd(# part.p_partkey = lineitem.l_partkey
>
> tpch40gnorssd(# and part.p_brand = 'Brand#54'
>
> tpch40gnorssd(# and part.p_container in ('SM CASE', 'SM
> BOX', 'SM PACK', 'SM PKG')
> tpch40gnorssd(# and lineitem.l_quantity >= 4 and
> lineitem.l_quantity <= 4 + 10
> tpch40gnorssd(# and part.p_size between 1 and 5
>
> tpch40gnorssd(# and lineitem.l_shipmode in ('AIR', 'AIR
> REG')
> tpch40gnorssd(# and lineitem.l_shipinstruct = 'DELIVER IN
> PERSON'
> tpch40gnorssd(# )
>
> tpch40gnorssd-# or
>
> tpch40gnorssd-# (
>
> tpch40gnorssd(# part.p_partkey = lineitem.l_partkey
>
> tpch40gnorssd(# and part.p_brand = 'Brand#51'
>
> tpch40gnorssd(# and part.p_container in ('MED BAG', 'MED
> BOX', 'MED PKG', 'MED PACK')
> tpch40gnorssd(# and lineitem.l_quantity >= 11 and
> lineitem.l_quantity <= 11 + 10
> tpch40gnorssd(# and part.p_size between 1 and 10
>
> tpch40gnorssd(# and lineitem.l_shipmode in ('AIR', 'AIR
> REG')
> tpch40gnorssd(# and lineitem.l_shipinstruct = 'DELIVER IN
> PERSON'
> tpch40gnorssd(# )
>
> tpch40gnorssd-# or
>
> tpch40gnorssd-# (
>
> tpch40gnorssd(# part.p_partkey = lineitem.l_partkey
>
> tpch40gnorssd(# and part.p_brand = 'Brand#21'
>
> tpch40gnorssd(# and part.p_container in ('LG CASE', 'LG
> BOX', 'LG PACK', 'LG PKG')
> tpch40gnorssd(# and lineitem.l_quantity >= 28 and
> lineitem.l_quantity <= 28 + 10
> tpch40gnorssd(# and part.p_size between 1 and 15
>
> tpch40gnorssd(# and lineitem.l_shipmode in ('AIR', 'AIR
> REG')
>
> tpch40gnorssd(# and lineitem.l_shipinstruct = 'DELIVER IN
> PERSON'
>
> tpch40gnorssd(# );
>
>
>
> server closed the connection unexpectedly
>
>
> This probably means the server terminated abnormally
>
>
> before or while processing the request.
>
>
> The connection to the server was lost. Attempting reset: Failed.
>
>
> !>
>
>
> !>
> ------------------------------------------------------------------------
>
> However, when executing an Explain query, no error is reported.
>
>
> ------------- EXPLAIN ONLY ------------------------
>
> Finalize Aggregate (cost=280394.81..280394.82 rows=1 width=32)
> -> Gather (cost=280394.59..280394.80 rows=2 width=32)
> Workers Planned: 2
> -> Partial Aggregate (cost=279394.59..279394.60 rows=1 width=32)
> -> Nested Loop (cost=29935.44..279381.95 rows=1685
> width=12)
> -> Parallel Bitmap Heap Scan on part
> (cost=29934.87..48103.87 rows=7853 width=30)
> Recheck Cond: (((p_brand = 'Brand#54'::bpchar)
> AND (p_size <= 5) AND (p_size >= 1) AND (p_container = ANY ('{"SM CASE","SM
> BOX","SM PACK","SM PKG"}'::bpchar[]))) OR ((p_brand =
> 'Brand#51'::bpchar) AND (p_size <= 10) AND (p_size >= 1) AND (p_container
> = ANY ('{"MED BAG","MED BOX","MED PKG","MED PACK"}'::bpchar[]))) OR
> ((p_brand = 'Brand#21'::bpchar) AND (p_size <= 15) AND (p_si
> ze >= 1) AND (p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG
> PKG"}'::bpchar[]))))
> -> BitmapOr (cost=29934.87..29934.87
> rows=18861 width=0)
> -> BitmapAnd (cost=9559.76..9559.76
> rows=3140 width=0)
> -> Bitmap Index Scan on
> idx_p_brand_p_size (cost=0.00..508.37 rows=31035 width=0)
> Index Cond: ((p_brand =
> 'Brand#54'::bpchar) AND (p_size <= 5) AND (p_size >= 1))
> -> Bitmap Index Scan on
> idx_p_containerpart000 (cost=0.00..9041.72 rows=809333 width=0)
> Index Cond: (p_container =
> ANY ('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bpchar[]))
> -> BitmapAnd (cost=9837.67..9837.67
> rows=6022 width=0)
> -> Bitmap Index Scan on
> idx_p_brand_p_size (cost=0.00..997.27 rows=60947 width=0)
> Index Cond: ((p_brand =
> 'Brand#51'::bpchar) AND (p_size <= 10) AND (p_size >= 1))
> -> Bitmap Index Scan on
> idx_p_containerpart000 (cost=0.00..8830.73 rows=790400 width=0)
> Index Cond: (p_container =
> ANY ('{"MED BAG","MED BOX","MED PKG","MED PACK"}'::bpchar[]))
> -> BitmapAnd (cost=10536.93..10536.93
> rows=9700 width=0)
> -> Bitmap Index Scan on
> idx_p_brand_p_size (cost=0.00..1586.52 rows=96967 width=0)
> Index Cond: ((p_brand =
> 'Brand#21'::bpchar) AND (p_size <= 15) AND (p_size >= 1))
> -> Bitmap Index Scan on
> idx_p_containerpart000 (cost=0.00..8940.74 rows=800267 width=0)
> Index Cond: (p_container =
> ANY ('{"LG CASE","LG BOX","LG PACK","LG PKG"}'::bpchar[]))
> -> Index Scan using idx_l_partkeylineitem000 on
> lineitem (cost=0.57..29.44 rows=1 width=25)
> Index Cond: (l_partkey = part.p_partkey)
> Filter: ((l_shipmode = ANY ('{AIR,"AIR
> REG"}'::bpchar[])) AND (l_shipinstruct = 'DELIVER IN PERSON'::bpchar) AND
> (((l_quantity >= '4'::numeric) AND (l_quantity <= '14'::numeric
> )) OR ((l_quantity >= '11'::numeric) AND (l_quantity <= '21'::numeric)) OR
> ((l_quantity >= '28'::numeric) AND (l_quantity <= '38'::numeric))) AND
> (((part.p_brand = 'Brand#54'::bpchar) AND (part.p_contain
> er = ANY ('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bpchar[])) AND
> (l_quantity >= '4'::numeric) AND (l_quantity <= '14'::numeric) AND
> (part.p_size <= 5)) OR ((part.p_brand = 'Brand#51'::bpchar) AND (par
> t.p_container = ANY ('{"MED BAG","MED BOX","MED PKG","MED
> PACK"}'::bpchar[])) AND (l_quantity >= '11'::numeric) AND (l_quantity <=
> '21'::numeric) AND (part.p_size <= 10)) OR ((part.p_brand = 'Brand#21'::
> bpchar) AND (part.p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG
> PKG"}'::bpchar[])) AND (l_quantity >= '28'::numeric) AND (l_quantity <=
> '38'::numeric) AND (part.p_size <= 15))))
> (26 rows)
> --------------------------------------------------------
>
> I checked it and the DBMS has a lot of space yet. I also have no
> concurrent connections, because the environment is development.
> Anyone have any idea why a query can cause the database to close the
> connection ?
>
> Best Regards
> Neto
>
>