Details
Major Description
I ran this query after setting profile "timings".
The query takes approximately 100 seconds but with the "timings" setting, the query did not finish even after a few hours.
Please try a similar query (4 way join) generating a large number of tuples. If your setup does not show up this hang, I will provide my environment.
Here is the query and the plan.
|
|
\redirect time5.txt;
|
\set -optimized-logical-plan "true";
|
\set -plan-format "string";
|
\set -profile "timings";
|
select 1537, count(1) from R join S on R.u1K /*+ hashjoin build (R) */ /*+ productivity R 4096.0 */ = S.u1K join T on S.u64 /*+ productivity S 65536.0 */ = T.u64 join U on T.u256 /*+ hashjoin build (T) */ /*+ productivity T 16384.0 */ = U.u256 where R.rand /*+ selectivity 0.00048828125 */ <= 16384 and S.rand /*+ selectivity 0.00048828125 */ <= 16384 and T.rand /*+ selectivity 0.00048828125 */ <= 16384 and U.rand /*+ selectivity 0.00048828125 */ <= 8192;
|
|
|
|
|
The hints are probably not necessary.
|
|
|
This query will ultimately produce 2B rows. approx.
|
|
|
select 1537, count(1) from R join S on R.u1K /*+ hashjoin build (R) */ /*+ productivity R 4096.0 */ = S.u1K join T on S.u64 /*+ productivity S 65536.0 */ = T.u64 join U on T.u256 /*+ hashjoin build (T) */ /*+ productivity T 16384.0 */ = U.u256 where R.rand /*+ selectivity 0.00048828125 */ <= 16384 and S.rand /*+ selectivity 0.00048828125 */ <= 16384 and T.rand /*+ selectivity 0.00048828125 */ <= 16384 and U.rand /*+ selectivity 0.00048828125 */ <= 8192
|
{
|
"requestID": "570e374d-46ec-4841-89f1-4ae809e84689",
|
"signature": {
|
"*": "*"
|
},
|
"results": [ {
|
"$1" : 1537,
|
"$2" : 2143873688
|
} ]
|
,
|
"plans":{"optimizedLogicalPlan": "distribute result [$$84]
|
-- DISTRIBUTE_RESULT |UNPARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |UNPARTITIONED|
|
project ([$$84])
|
-- STREAM_PROJECT |UNPARTITIONED|
|
assign [$$84] <- [{\"$1\": 1537, \"$2\": $$99}]
|
-- ASSIGN |UNPARTITIONED|
|
aggregate [$$99] <- [agg-sql-sum($$104)]
|
-- AGGREGATE |UNPARTITIONED|
|
exchange
|
-- RANDOM_MERGE_EXCHANGE |PARTITIONED|
|
aggregate [$$104] <- [agg-sql-count(1)]
|
-- AGGREGATE |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
join (eq($$93, $$94))
|
-- HYBRID_HASH_JOIN [$$94][$$93] |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$94] |PARTITIONED|
|
project ([$$94])
|
-- STREAM_PROJECT |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
join (eq($$95, $$96))
|
-- HYBRID_HASH_JOIN [$$96][$$95] |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
project ([$$96])
|
-- STREAM_PROJECT |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
join (eq($$97, $$98))
|
-- HYBRID_HASH_JOIN [$$98][$$97] |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$98] |PARTITIONED|
|
project ([$$98])
|
-- STREAM_PROJECT |PARTITIONED|
|
select (le($$U.getField(\"rand\"), 8192))
|
-- STREAM_SELECT |PARTITIONED|
|
assign [$$98] <- [$$U.getField(\"u256\")]
|
-- ASSIGN |PARTITIONED|
|
project ([$$U])
|
-- STREAM_PROJECT |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
data-scan []<-[$$91, $$U, $$92] <- Default.U
|
-- DATASOURCE_SCAN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
empty-tuple-source
|
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$97] |PARTITIONED|
|
project ([$$97, $$96])
|
-- STREAM_PROJECT |PARTITIONED|
|
select (le($$T.getField(\"rand\"), 16384))
|
-- STREAM_SELECT |PARTITIONED|
|
assign [$$97, $$96] <- [$$T.getField(\"u256\"), $$T.getField(\"u64\")]
|
-- ASSIGN |PARTITIONED|
|
project ([$$T])
|
-- STREAM_PROJECT |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
data-scan []<-[$$89, $$T, $$90] <- Default.T
|
-- DATASOURCE_SCAN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
empty-tuple-source
|
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
|
exchange
|
-- BROADCAST_EXCHANGE |PARTITIONED|
|
project ([$$95, $$94])
|
-- STREAM_PROJECT |PARTITIONED|
|
select (le($$S.getField(\"rand\"), 16384))
|
-- STREAM_SELECT |PARTITIONED|
|
assign [$$95, $$94] <- [$$S.getField(\"u64\"), $$S.getField(\"u1K\")]
|
-- ASSIGN |PARTITIONED|
|
project ([$$S])
|
-- STREAM_PROJECT |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
data-scan []<-[$$87, $$S, $$88] <- Default.S
|
-- DATASOURCE_SCAN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
empty-tuple-source
|
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$93] |PARTITIONED|
|
project ([$$93])
|
-- STREAM_PROJECT |PARTITIONED|
|
select (le($$R.getField(\"rand\"), 16384))
|
-- STREAM_SELECT |PARTITIONED|
|
assign [$$93] <- [$$R.getField(\"u1K\")]
|
-- ASSIGN |PARTITIONED|
|
project ([$$R])
|
-- STREAM_PROJECT |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
data-scan []<-[$$85, $$R, $$86] <- Default.R
|
-- DATASOURCE_SCAN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
empty-tuple-source
|
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
|
"},
|
"status": "success",
|
"metrics": {
|
"elapsedTime": "180.384285075s",
|
"executionTime": "180.381232548s",
|
"resultCount": 1,
|
"resultSize": 39,
|
"processedObjects": 16777216,
|
"warningCount": 2
|
}
|
}
|
|