Details
Description
Steps to reproduce -
- Get the analytics service config using following command -
curl --location --request GET 'http://10.112.230.102:8095/analytics/config/service' --header 'Authorization: Basic QWRtaW5pc3RyYXRvcjpwYXNzd29yZA==' --header 'Content-Type: application/x-www-form-urlencoded' |
|
Response -
|
{"activeMemoryGlobalBudget":67108864,"activeStopTimeout":3600,"activeSuspendTimeout":3600,"analyticsBroadcastDcpStateMutationCount":100000,"analyticsHttpRequestQueueSize":256,"analyticsHttpThreadCount":16,"authorizationCacheTtlSeconds":5,"azureRequestTimeout":120,"bindAddress":null,"bindToHost":false,"clusterConnectRetries":5,"collectDcpStateFromNodesTimeout":600,"compilerArrayindex":true,"compilerCbo":true,"compilerFramesize":32768,"compilerGroupmemory":33554432,"compilerJoinmemory":33554432,"compilerParallelism":0,"compilerQueryplanshape":"leftdeep","compilerSortParallel":false,"compilerSortmemory":33554432,"compilerWindowmemory":33554432,"coresMultiplier":3,"dcpBufferAckWatermark":20,"dcpChannelReconnectRemoteIdleSeconds":120,"dcpConnectionBufferSize":10485760,"dcpIdleQueueFlushThresholdMillis":5000,"dcpNoopInterval":120,"deadlockWatchdogHaltDelaySeconds":120,"deadlockWatchdogPollSeconds":300,"jobHistorySize":10,"jobQueueCapacity":4096,"jvmArgs":null,"logLevel":"DEBUG","maxWebRequestSize":209715200,"netBufferCount":1,"netThreadCount":1,"nodeDeltaRecovery":true,"rebalancePullDatasetSizeFrequency":5,"remoteLinkConnectTimeoutSeconds":60,"remoteLinkRefreshAuthSeconds":120,"remoteLinkSocketTimeoutSeconds":60,"remoteLinkValidationMaxRetries":0,"replicaInactivityTimeoutSeconds":300,"replicaRecoveryWaitIntervalSeconds":720,"replicaReportIntervalSeconds":5,"replicationTimeout":120,"requestsArchiveSize":50,"resultSweepThreshold":60000,"resultTtl":86400000,"storageBuffercacheMaxopenfiles":2147483647,"storageBuffercachePagesize":131072,"storageCompressionBlock":"snappy","storageDiskForceBytes":16777216,"storageMaxConcurrentFlushesPerPartition":2,"storageMaxConcurrentMergesPerPartition":2,"storageMaxScheduledMergesPerPartition":8,"storageMemorycomponentFlushThreshold":0.9,"storageMemorycomponentMaxScheduledFlushes":0,"storageMemorycomponentNumcomponents":2,"storageMemorycomponentPagesize":131072,"storageWriteRateLimit":0,"threaddumpFrequencySeconds":300,"threaddumpLogLevel":"DEBUG","traceCategories":[],"txnDatasetCheckpointInterval":3600} |
2. On analytics query workbench, paste the below query in query editor and click on explain -
select airportname, airlinename, hotelname, landmark
|
from (select ar.airportname, al.name as airlinename, ht.name as hotelname, ld.name as landmark
|
from `travel-sample`.inventory.airport ar, `travel-sample`.inventory.airline al, `travel-sample`.inventory.hotel ht, `travel-sample`.inventory.landmark ld
|
where ar.country = al.country
|
and ar.city = ht.city
|
and ht.city = ld.city) as x limit 10; |
3. Following is observed as a part of query plan -
"optimizer-estimates": { |
"cardinality": 9646.85, |
"op-cost": 0, |
"total-cost": 46957.72 |
}
|
4. Now set compilerCbo to false using analytics service config administrator API -
curl --location --request PUT 'http://10.112.230.102:8095/analytics/config/service' --header 'Authorization: Basic QWRtaW5pc3RyYXRvcjpwYXNzd29yZA==' --header 'Content-Type: application/x-www-form-urlencoded' --data-urlencode 'compilerCbo=false' |
5. repeat step 1 to verify whether compilerCbo is set to false.
6. repeat step 2.
7. cardinality and total cost is still observed as a part of query plan.
Attachments
Issue Links
- blocks
-
MB-48874 Cost-based Query Optimization for Analytics (v1)
- Closed