If I understand it correctly, you may need to write new storage plugin rules (as similar to that of other storage plugin rules) to support projection pushdown and limit pushdown for your custom storage plugin. This might help in reading only the required fields from the storage.
Thanks, On Tue, Aug 28, 2018 at 10:19 AM yang zhang <[email protected]> wrote: > 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'? >
