Thanks for the tip Vicky, here's the plan w/ all atributes:
: jdbc:drill:> explain plan including all attributes for
. . . . . . . > select c_status from `position` where dir0='F113';
+------------+------------+
| text | json |
+------------+------------+
| 00-00 Screen : rowType = RecordType(ANY clsc_status): rowcount =
3.2975685645E8, cumulative cost = {5.419004340995E9 rows,
1.7510089081495E10 cpu, 0.0 io, 1.3506840840192E12 network, 0.0 memory}, id
= 11669
00-01 UnionExchange : rowType = RecordType(ANY clsc_status): rowcount
= 3.2975685645E8, cumulative cost = {5.386028655349999E9 rows,
1.747711339585E10 cpu, 0.0 io, 1.3506840840192E12 network, 0.0 memory}, id
= 11668
01-01 Project(clsc_status=[$1]) : rowType = RecordType(ANY
clsc_status): rowcount = 3.2975685645E8, cumulative cost = {5.0562717989E9
rows, 1.483905854425E10 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 11667
01-02 SelectionVectorRemover : rowType = RecordType(ANY dir0, ANY
clsc_status): rowcount = 3.2975685645E8, cumulative cost = {4.72651494245E9
rows, 1.483905854025E10 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 11666
01-03 Filter(condition=[=($0, 'F113')]) : rowType =
RecordType(ANY dir0, ANY clsc_status): rowcount = 3.2975685645E8,
cumulative cost = {4.396758086E9 rows, 1.45093016838E10 cpu, 0.0 io, 0.0
network, 0.0 memory}, id = 11665
01-04 Scan(groupscan=[ParquetGroupScan
[entries=[ReadEntryWithPath
[path=maprfs:/user/pcurtis/dtcc/position/GT06;WSB4/2014-03-26/214c279334946e65-7e32c56eed93cbc1_1051246080_data.0.parq],
ReadEntryWithPath
[path=maprfs:/user/pcurtis/dtcc/position/GT06;WSB4/2014-04-02/214c279334946e65-7e32c56eed93cbc1_192659286_data.0.parq],
ReadEntryWithPath
[path=maprfs:/user/pcurtis/dtcc/position/GT06;WSB4/2014-04-01/214c279334946e65-7e32c56eed93cbc2_371458216_data.0.parq],
ReadEntryWithPath
[path=maprfs:/user/pcurtis/dtcc/position/GT06;WSB4/2014-06-21/894b9942f8411e85-7d163997afdf2587_940200931_data.0.parq],
ReadEntryWithPath [
... (this continues on for awhile)..
On Thu, Apr 16, 2015 at 7:11 PM, Vicky Markman <[email protected]>
wrote:
> Andy,
>
> Could you please attach "explain plan including all attributes" for this
> query ?
> I'm not able to reproduce this behavior with the latest build. What version
> of drill are you running with ?
>
> 0: jdbc:drill:schema=dfs> explain plan including all attributes for select
> a1 from `position` where dir0='F113' and dir1='2014-03-27';
>
> +------------+------------+
>
> | text | json |
>
> +------------+------------+
>
> | 00-00 Screen : rowType = RecordType(ANY a1): rowcount = 10.0,
> cumulative cost = {31.0 rows, 47.0 cpu, 0.0 io, 0.0 network, 0.0 memory},
> id = 57712
>
> 00-01 Project(a1=[$2]) : rowType = RecordType(ANY a1): rowcount =
> 10.0, cumulative cost = {30.0 rows, 46.0 cpu, 0.0 io, 0.0 network, 0.0
> memory}, id = 57711
>
> 00-02 Project(dir0=[$0], dir1=[$2], a1=[$1]) : rowType =
> RecordType(ANY dir0, ANY dir1, ANY a1): rowcount = 10.0, cumulative cost =
> {20.0 rows, 42.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 57710
>
> 00-03 Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
> [path=maprfs:/test/position/F113/2014-03-27/0_0_0.parquet]],
> selectionRoot=/test/position, numFiles=1, columns=[`dir0`, `dir1`, `a1`]]])
> : rowType = RecordType(ANY dir0, ANY a1, ANY dir1): rowcount = 10.0,
> cumulative cost = {10.0 rows, 30.0 cpu, 0.0 io, 0.0 network, 0.0 memory},
> id = 57709
>
> | {
>
> "head" : {
>
> "version" : 1,
>
> "generator" : {
>
> "type" : "ExplainHandler",
>
> "info" : ""
>
> },
>
> "type" : "APACHE_DRILL_PHYSICAL",
>
> "options" : [ ],
>
> "queue" : 0,
>
> "resultMode" : "EXEC"
>
> },
>
> "graph" : [ {
>
> "pop" : "parquet-scan",
>
> "@id" : 3,
>
> "entries" : [ {
>
> "path" : "maprfs:/test/position/F113/2014-03-27/0_0_0.parquet"
>
> } ],
>
>
> Thanks,
> Vicky.
>
> On Thu, Apr 16, 2015 at 12:21 PM, Andy Pernsteiner <
> [email protected]> wrote:
>
> > I have a dataset with the following structure:
> >
> > /clsc_ocode/yyyy-mm-dd/file.parquet
> >
> > Where clsc_ocode might be something like:
> >
> > AAAA
> > BBBB
> > CCCC
> > etc
> >
> > What we're trying to do is run the following query, and have it only scan
> > the relevant dirs/files:
> >
> > select c_status from `position` where dir0='F113' and dir1='2014-03-27';
> >
> >
> > However, the explain plan tells me that its scanning everything (see
> > below). Any clues on how I can get drill to only scan the 'correct' dirs
> > and files?
> >
> >
> >
> > 0: jdbc:drill:> explain plan for select c_status from `position` where
> > dir0='F113' and dir1='2014-03-27';
> > +------------+------------+
> > | text | json |
> > +------------+------------+
> > | 00-00 Screen
> > 00-01 UnionExchange
> > 01-01 Project(c_status=[$2])
> > 01-02 SelectionVectorRemover
> > 01-03 Filter(condition=[AND(=($0, 'F113'), =($1,
> > '2014-03-27'))])
> > 01-04 Project(dir0=[$0], dir1=[$2], c_status=[$1])
> > 01-05 Scan(groupscan=[ParquetGroupScan
> > [entries=[ReadEntryWithPath [path=maprfs:/user/myuser/project/position/
> >
> >
> *GT06;WSB4*/2014-03-26/214c279334946e65-7e32c56eed93cbc1_1051246080_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-04-02/214c279334946e65-7e32c56eed93cbc1_192659286_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-04-01/214c279334946e65-7e32c56eed93cbc2_371458216_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-06-21/894b9942f8411e85-7d163997afdf2587_940200931_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-07-29/214c279334946e65-7e32c56eed93cbc2_664248475_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-06-19/214c279334946e65-7e32c56eed93cbc0_2053035507_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-06-30/214c279334946e65-7e32c56eed93cbbf_1281606632_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-03-05/214c279334946e65-7e32c56eed93cbc0_1369385312_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-09-01/d4dab6bbcfb75d9-641cc2a57b756486_1553652051_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-06-20/214c279334946e65-7e32c56eed93cbc0_328577881_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-06-27/214c279334946e65-7e32c56eed93cbbf_753269315_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-03-31/214c279334946e65-7e32c56eed93cbc1_1467857798_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-04-03/214c279334946e65-7e32c56eed93cbc0_274342802_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-09-02/d940124da9265b62-db78d733b4e41a9d_1017833885_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-07-03/214c279334946e65-7e32c56eed93cbc2_37298498_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-07-15/214c279334946e65-7e32c56eed93cbbf_1237344643_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-06-26/214c279334946e65-7e32c56eed93cbc2_1847884103_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/GT06;WSB4/2014-03-27/214c279334946e65-7e32c56eed93cbc0_9210719_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/TD06/2014-07-02/214c279334946e65-7e32c56eed93cbc0_1371477905_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/TD06/2014-06-21/894b9942f8411e85-7d163997afdf2587_69864010_data.1.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/TD06/2014-06-21/894b9942f8411e85-7d163997afdf2587_69864010_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/TD06/2014-07-29/214c279334946e65-7e32c56eed93cbc1_680866327_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/TD06/2014-06-19/214c279334946e65-7e32c56eed93cbc1_488271693_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/TD06/2014-06-30/214c279334946e65-7e32c56eed93cbc2_452230455_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/TD06/2014-09-01/d4dab6bbcfb75d9-641cc2a57b756486_1114217993_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/TD06/2014-09-01/d4dab6bbcfb75d9-641cc2a57b756486_1114217993_data.1.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/TD06/2014-06-20/214c279334946e65-7e32c56eed93cbc1_604208402_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/TD06/2014-06-27/214c279334946e65-7e32c56eed93cbc2_35452641_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/TD06/2014-09-02/d940124da9265b62-db78d733b4e41a9d_121272728_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/TD06/2014-09-02/d940124da9265b62-db78d733b4e41a9d_121272728_data.1.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/TD06/2014-07-03/214c279334946e65-7e32c56eed93cbc1_594862257_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/TD06/2014-07-15/214c279334946e65-7e32c56eed93cbc0_37656589_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/TD06/2014-06-26/214c279334946e65-7e32c56eed93cbbf_956289869_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/CB52/2014-06-21/894b9942f8411e85-7d163997afdf2585_873498190_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/CB52/2014-06-30/214c279334946e65-7e32c56eed93cbc0_1914683943_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/CB52/2014-09-01/d4dab6bbcfb75d9-641cc2a57b756484_762992808_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/CB52/2014-06-27/214c279334946e65-7e32c56eed93cbc0_1098724507_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/CB52/2014-09-02/d940124da9265b62-db78d733b4e41a9b_1313631650_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/CB52/2014-07-03/214c279334946e65-7e32c56eed93cbbf_121420287_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/CB52/2014-07-15/214c279334946e65-7e32c56eed93cbc2_136444256_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-03-25/214c279334946e65-7e32c56eed93cbc0_1650113784_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-03-26/214c279334946e65-7e32c56eed93cbbf_1340747001_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-04-02/214c279334946e65-7e32c56eed93cbc2_1364839309_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-03-24/214c279334946e65-7e32c56eed93cbc1_750510284_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-04-01/214c279334946e65-7e32c56eed93cbc1_536235491_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-06-21/894b9942f8411e85-7d163997afdf2585_726763609_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-03-04/214c279334946e65-7e32c56eed93cbbf_942238716_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-03-05/214c279334946e65-7e32c56eed93cbc2_506292193_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-09-01/d4dab6bbcfb75d9-641cc2a57b756484_567483995_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-03-31/214c279334946e65-7e32c56eed93cbbf_615989837_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-04-03/214c279334946e65-7e32c56eed93cbbf_616726188_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-09-02/d940124da9265b62-db78d733b4e41a9b_220071911_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/PE10;PF10/2014-03-27/214c279334946e65-7e32c56eed93cbc2_278620626_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/CB23/2014-06-21/894b9942f8411e85-7d163997afdf2585_1824939453_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/CB23/2014-06-30/214c279334946e65-7e32c56eed93cbc2_1226007134_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/CB23/2014-09-01/d4dab6bbcfb75d9-641cc2a57b756484_366513003_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/CB23/2014-06-27/214c279334946e65-7e32c56eed93cbc2_820371902_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/CB23/2014-09-02/d940124da9265b62-db78d733b4e41a9b_1199732126_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/CB23/2014-07-03/214c279334946e65-7e32c56eed93cbc2_1965630551_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/CB23/2014-07-15/214c279334946e65-7e32c56eed93cbbf_115161432_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/F122/2014-03-25/214c279334946e65-7e32c56eed93cbc2_324676563_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/F122/2014-03-26/214c279334946e65-7e32c56eed93cbc1_1975946170_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/F122/2014-04-02/214c279334946e65-7e32c56eed93cbc2_2035552145_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/F122/2014-03-24/214c279334946e65-7e32c56eed93cbbf_941712586_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/F122/2014-04-01/214c279334946e65-7e32c56eed93cbc1_1237069829_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/F122/2014-06-21/894b9942f8411e85-7d163997afdf2587_1708712130_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/F122/2014-07-29/214c279334946e65-7e32c56eed93cbc2_354733156_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/F122/2014-06-30/214c279334946e65-7e32c56eed93cbbf_532030629_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/F122/2014-09-01/d4dab6bbcfb75d9-641cc2a57b756486_1274519202_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/F122/2014-06-20/214c279334946e65-7e32c56eed93cbc0_600238501_data.0.parq],
> > ReadEntryWithPath
> >
> >
> [path=maprfs:/user/myuser/project/position/F122/2014-06-27/214c279334946e65-7e32c56eed93cbbf_1427614849_data.0.parq],
> > ReadEntryWithPath [path=maprfs:/user/myuser/project/ |
> >
> >
> >
> >
> >
> >
> > --
> > Andy Pernsteiner
> >
> >
> > Manager, Field Enablement
> > ph: 206.228.0737
> >
> > www.mapr.com
> >
> > Now Available - Free Hadoop On-Demand Training
> > <
> >
> http://www.mapr.com/training?utm_source=Email&utm_medium=Signature&utm_campaign=Free%20available
> > >
> >
>
--
Andy Pernsteiner
Manager, Field Enablement
ph: 206.228.0737
www.mapr.com
Now Available - Free Hadoop On-Demand Training
<http://www.mapr.com/training?utm_source=Email&utm_medium=Signature&utm_campaign=Free%20available>
