Hi：
I have two questions to ask.
There is already a custom plugin. Query sql:
Select * from indexr.face_image_mess where device_id =
'3E04846B-6B69-1A4D-0569-ED0813853348' and org_id in (11,12) order by
short_time desc limit 4 offset 9;
plan:
{
"head" : {
"version" : 1,
"generator" : {
"type" : "DefaultSqlHandler",
"info" : ""
},
"type" : "APACHE_DRILL_PHYSICAL",
"options" : [ {
"kind" : "LONG",
"accessibleScopes" : "ALL",
"name" : "planner.width.max_per_node",
"num_val" : 1,
"scope" : "SESSION"
} ],
"queue" : 0,
"hasResourcePlan" : false,
"resultMode" : "EXEC"
},
"graph" : [ {
"pop" : "indexr-scan",
"@id" : 7,
"userName" : "conn",
"indexrScanSpec" : {
"tableName" : "face_image_mess",
"rsFilter" : {
"type" : "and",
"children" : [ {
"type" : "equal",
"attr" : {
"name" : "device_id",
"type" : "VARCHAR"
},
"numValue" : 0,
"strValue" : "3E04846B-6B69-1A4D-0569-ED0813853348",
"type" : "equal"
}, {
"type" : "or",
"children" : [ {
"type" : "equal",
"attr" : {
"name" : "org_id",
"type" : "VARCHAR"
},
"numValue" : 0,
"strValue" : "11",
"type" : "equal"
}, {
"type" : "equal",
"attr" : {
"name" : "org_id",
"type" : "VARCHAR"
},
"numValue" : 0,
"strValue" : "12",
"type" : "equal"
} ],
"type" : "or"
} ],
"type" : "and"
}
},
"storage" : {
"type" : "indexr",
"enabled" : true
},
"columns" : [ "`**`" ],
"limitScanRows" : 9223372036854775807,
"scanId" : "dc889ee0-6675-42bb-b7a5-2bc413d6372d",
"cost" : 0.0
}, {
"pop" : "top-n",
"@id" : 6,
"child" : 7,
"orderings" : [ {
"order" : "DESC",
"expr" : "`short_time`",
"nullDirection" : "FIRST"
} ],
"reverse" : false,
"limit" : 13,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 1.0
}, {
"pop" : "selection-vector-remover",
"@id" : 5,
"child" : 6,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 1.0
}, {
"pop" : "limit",
"@id" : 4,
"child" : 5,
"first" : 9,
"last" : 13,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 13.0
}, {
"pop" : "limit",
"@id" : 3,
"child" : 4,
"first" : 9,
"last" : 13,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 13.0
}, {
"pop" : "selection-vector-remover",
"@id" : 2,
"child" : 3,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 13.0
}, {
"pop" : "project",
"@id" : 1,
"exprs" : [ {
"ref" : "`id`",
"expr" : "`id`"
}
.....
.....
.....
],
"child" : 2,
"outputProj" : true,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 13.0
}, {
"pop" : "screen",
"@id" : 0,
"child" : 1,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 13.0
} ]
}
The version of drill-1.13.0 is used.
1. Is order by pushdown supported?
Does the drill execution plan push down to the plugin when it
supports getting the order by field 'short_time' and the select field (all
fields)?
This table (face_image_mess) has 70 fields, one data has 1KB; this
query hits 200000000; currently can't get the order by field and select
field pushed down to the plugin, only scan all the field values of the hit;
the calculation of the drill engine is 200000000 * 1KB = 190.7GB.
Custom plugin I designed a Long type rowid, which points to the physical
address.
If the plugin can get the pushed by order field and the select field to
filter out the fields that do not need to participate in the calculation,
only four fields that actually participate in the calculation are 'rowid',
'short_time', 'device_id', 'org_id', then Minimize the calculation of the
drill engine. The actual calculation is 190.7GB * 4 ÷ 70 = 10.9GB, which
greatly improves the query performance. After the calculation is completed,
the topN detailed records are obtained according to the rowid.
2. Is limit pushdown supported?
The current limit 4 offset 9 is actually the data that satisfies the
where condition and then the limit 4 offset 9; whether to support the limit
4 offset 9 push to the plugin, each drillbit node plugin handles the topN
return, only jump to the last one Does the page need all the data for
'where conditions'?
