Re: query causes connection termination

Another fact is that when executing the query without the command EXPLAIN
ANALYZE, the result is usually returned after a few minutes.
I do not understand, because when using the EXPLAIN ANALYZE command the
dbms closes the connection.
Anyone have any tips on why this occurs?

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

> 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
>>
>>
>