It appears I spoke too soon. Presumably once the stats completed updating, I now get the same exception:
java.lang.IndexOutOfBoundsException: end index (174) must not be less than start index (226) at com.google.common.base.Preconditions.checkPositionIndexes(Preconditions.java:388) at com.google.common.collect.ImmutableList.subList(ImmutableList.java:362) at com.google.common.collect.ImmutableList.subList(ImmutableList.java:62) at org.apache.phoenix.filter.SkipScanFilter.intersect(SkipScanFilter.java:291) at org.apache.phoenix.filter.SkipScanFilter.intersect(SkipScanFilter.java:177) at org.apache.phoenix.compile.ScanRanges.intersectScan(ScanRanges.java:316) at org.apache.phoenix.iterate.BaseResultIterators.getParallelScans(BaseResultIterators.java:464) at org.apache.phoenix.iterate.BaseResultIterators.getParallelScans(BaseResultIterators.java:394) at org.apache.phoenix.iterate.BaseResultIterators.<init>(BaseResultIterators.java:184) at org.apache.phoenix.iterate.ParallelIterators.<init>(ParallelIterators.java:54) at org.apache.phoenix.execute.AggregatePlan.newIterator(AggregatePlan.java:173) at org.apache.phoenix.execute.BaseQueryPlan.iterator(BaseQueryPlan.java:227) at org.apache.phoenix.execute.BaseQueryPlan.iterator(BaseQueryPlan.java:154) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:226) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:217) at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:216) at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1057) at sqlline.SqlLine$Commands.execute(SqlLine.java:3673) at sqlline.SqlLine$Commands.sql(SqlLine.java:3584) at sqlline.SqlLine.dispatch(SqlLine.java:821) at sqlline.SqlLine.begin(SqlLine.java:699) at sqlline.SqlLine.mainWithInputRedirection(SqlLine.java:441) at sqlline.SqlLine.main(SqlLine.java:424) Still null for the guideposts though, both with PName.EMPTY_NAME and null for the PTableKey constructor. It certainly appears to be stats related. -Gary On Fri, Feb 27, 2015 at 4:06 PM, Gary Schulte <[email protected]> wrote: > I have the query timeout set too low, but I believe the stats update > completed as I see related rows in the stats table. > > Both skip and in-list queries run fine - no exceptions. Still null for > the guideposts though - is it likely this is due to the timeout in the > stats update? > > -Gary > > On Fri, Feb 27, 2015 at 12:30 PM, James Taylor <[email protected]> > wrote: > >> See inline. Thanks for your help on this one, Gary. It'd be good to >> get to the bottom of it so it doesn't bite you again. >> >> On Fri, Feb 27, 2015 at 11:13 AM, Gary Schulte >> <[email protected]> wrote: >> > James, >> > >> > When I simply added the skip scan hint, I got the same exception (even >> with >> > device_type criteria removed) but the indexes in the exception changed. >> > Interesting - I wouldn't have expected adding a skip scan hint would >> have >> > altered the plan, since it was already doing a skip scan. >> >> The hint tells Phoenix to keep using PK columns in the skip scan, >> rather than stopping at the first PK column that isn't being filtered >> on. We don't do this by default, because if the cardinality was very >> high, we wouldn't want to do this. Since our stats don't yet capture >> cardinality, we can't yet automatically do this. >> >> > >> > 1: current region boundaries, linked so as not to clutter the list with >> hex >> > : http://goo.gl/hFSzYJ >> > >> > 2: table stats/guideposts, it looks like there are/were none. The >> output >> > from the guidepost loop was : >> > >> > null >> > >> > (this was prior to deleting from system.stats) >> > >> > >> > 3: deleting system.stats appears to have resolved the exception for >> both the >> > explicit varchar inlist and the skip_scan hint. Skip scanning the >> reduced >> > index space yields much faster results, ~5 seconds as opposed to 27. >> >> Glad to hear it's faster with the hint forcing the skip scan across >> all your columns. >> >> I can't explain why deleting the stats resolved the issue, though, as >> I would have expected (2) to have returned the stats. FYI, it looks >> like the bug is in the code that intersects the guideposts with the >> region boundaries. >> >> > >> > Should I expect to rebuild stats often or is this more of an error case? >> >> Stats are rebuilt automatically when a major compaction occurs and are >> updated as splits happen. They can also be manually updated by running >> the following command: >> >> UPDATE STATISTICS PERF.BIG_OLAP_DOC >> >> For more info on stats, see >> http://phoenix.apache.org/update_statistics.html >> >> If you run this command, does the problem start to reoccur? If so, >> would you mind adding this command before running the loop to collect >> the guideposts and let me know if you see that stats output? >> >> > >> > Thanks again. >> > >> > >> > >> > On Thu, Feb 26, 2015 at 5:55 PM, James Taylor <[email protected]> >> > wrote: >> >> >> >> Gary, >> >> I'm not able to repro the issue - I filed PHOENIX-1690 to track it and >> >> attached my test case there. It looks related to the particular state >> >> the table is in wrt its region boundaries and current statistics, so >> >> I'll need the following additional information to try to help me repro >> >> this: >> >> >> >> 1) What are the current region boundaries of your table? You can get >> >> this programmatically through code like this: >> >> >> >> Connection conn = DriverManager.getConnection(getUrl(), props); >> >> List<HRegionLocation> splits = >> >> >> >> >> conn.unwrap(PhoenixConnection.class).getQueryServices().getAllTableRegions(Bytes.toBytes("PERF.BIG_OLAP_DOC")); >> >> for (HRegionLocation split : splits) { >> >> >> >> >> System.out.println(Bytes.toStringBinary(split.getRegionInfo().getEndKey())); >> >> } >> >> >> >> 2) What are the current stats for the table. You can get this by >> >> programmatically through code like this: >> >> >> >> PTable table = >> >> conn.unwrap(PhoenixConnection.class).getMetaDataCache().getTable(new >> >> PTableKey(null, "PERF.BIG_OLAP_DOC")); >> >> for (GuidePostsInfo info : >> >> table.getTableStats().getGuidePosts().values()) { >> >> for (byte[] gp : info.getGuidePosts()) { >> >> System.out.println(Bytes.toStringBinary(gp)); >> >> } >> >> } >> >> >> >> 3) If you can try after removing all rows from the SYSTEM.STATS table >> >> and let me know if the problem still occurs, that'd be helpful too. >> >> You can just do the following from sqlline: DELETE FROM SYSTEM.STATS >> >> and then exit sqlline, start it again, and rerun the original query. >> >> >> >> Thanks, >> >> James >> >> >> >> On Thu, Feb 26, 2015 at 10:52 AM, James Taylor <[email protected] >> > >> >> wrote: >> >> > Gary, >> >> > One possible workaround. Can you try adding the SKIP_SCAN hint to >> your >> >> > query (instead of the AND device_type in >> >> > ('MOBILE','DESKTOP','OTHER','TABLET')), like this? >> >> > >> >> > SELECT /*+ SKIP_SCAN */ count(1) cnt, >> >> > ... >> >> > >> >> > Thanks, >> >> > James >> >> > >> >> > On Wed, Feb 25, 2015 at 10:16 AM, James Taylor < >> [email protected]> >> >> > wrote: >> >> >> Sounds like a bug. I'll try to repro on my end. Thanks for the >> details, >> >> >> Gary. >> >> >> >> >> >> James >> >> >> >> >> >> On Tue, Feb 24, 2015 at 1:49 PM, Gary Schulte >> >> >> <[email protected]> wrote: >> >> >>> On Tue, Feb 24, 2015 at 12:29 AM, James Taylor >> >> >>> <[email protected]> >> >> >>> wrote: >> >> >>>> >> >> >>>> Based on your query plan, the skip scan is being done solely >> based on >> >> >>>> your >> >> >>>> salt bucket while the rest of the filtering is being done by a >> >> >>>> filter, which >> >> >>>> means that you're not filtering based on the leading part of your >> >> >>>> primary >> >> >>>> key. We'll know more once you post your schema, but if NETWORK, >> >> >>>> KEYWORD_ID >> >> >>>> and CUSTOMER_ID formed your primary key constraint, then the skip >> >> >>>> scan would >> >> >>>> work well. >> >> >>>> >> >> >>> >> >> >>> Thanks for your response James. Sorry for the slow reply - I had >> >> >>> difficulty >> >> >>> finding the exact set of test queries I was using for timings. >> >> >>> >> >> >>> The relevant portion of the olap doc schema is: >> >> >>> >> >> >>> create table PERF.BIG_OLAP_DOC ( >> >> >>> client_id integer not null >> >> >>> ,customer_id integer >> >> >>> ,time_id integer not null >> >> >>> ,conversion_type_id integer not null >> >> >>> ,device_type varchar(16) >> >> >>> ,keyword_id bigint not null >> >> >>> ,creative_id bigint not null >> >> >>> ,placement_id bigint not null >> >> >>> ,product_target_id bigint not null >> >> >>> ,network varchar(7) >> >> >>> ,impressions decimal(18, 4) >> >> >>> ,publisher_clicks decimal(18, 4) >> >> >>> ,publisher_cost decimal(18, 4) >> >> >>> ,conversions decimal(18, 4) >> >> >>> ,revenue decimal(18, 4) >> >> >>> >> >> >>> [ ...additional metric and dimensional colums ... ] >> >> >>> >> >> >>> constraint perf_fact_pk primary key (client_id, time_id, >> >> >>> conversion_type_id, device_type, keyword_id, creative_id, >> >> >>> placement_id, >> >> >>> product_target_id))SALT_BUCKETS=10; >> >> >>> >> >> >>> >> >> >>> I am evaluating a 'stitch' case where results from an external >> system >> >> >>> are >> >> >>> injected either via table or (as in this case) an in-list. An >> example >> >> >>> of >> >> >>> one of these test agg queries I am using is: >> >> >>> >> >> >>> SELECT count(1) cnt, >> >> >>> coalesce(SUM(impressions), 0.0) AS "impressions", >> >> >>> coalesce(SUM(publisher_clicks), 0.0) AS "pub_clicks", >> >> >>> coalesce(SUM(publisher_cost), 0.0) AS "pub_cost", >> >> >>> coalesce(SUM(conversions), 0.0) AS "conversions", >> >> >>> coalesce(SUM(revenue), 0.0) AS "revenue" >> >> >>> FROM perf.big_olap_doc >> >> >>> WHERE time_id between 3000 and 3700 >> >> >>> AND network in ('SEARCH') >> >> >>> AND conversion_type_id = 1 >> >> >>> AND client_id = 10724 >> >> >>> -- AND device_type in ('MOBILE','DESKTOP','OTHER','TABLET') >> >> >>> AND keyword_id in ( >> >> >>> 613214369, 613217307, 613247509, 613248897, 613250382, 613250387, >> >> >>> 613252322, >> >> >>> 613260252, 613261753, 613261754, 613261759, >> >> >>> 613261770, 613261873, 613261884, 613261885, 613261888, 613261889, >> >> >>> 613261892, >> >> >>> 613261897, 613261913, 613261919, 613261927, >> >> >>> 614496021, 843606367, 843606967, 843607021, 843607033, 843607089, >> >> >>> 1038731600, 1038731672, 1038731673, 1038731675, >> >> >>> 1038731684, 1038731693, 1046990487, 1046990488, 1046990499, >> >> >>> 1046990505, >> >> >>> 1046990506, 1049724722, 1051109548, 1051311275, >> >> >>> 1051311904, 1060574377, 1060574395, 1060574506, 1060574562, >> >> >>> 1115915938, >> >> >>> 1115915939, 1115915941, 1116310571, 1367495544, >> >> >>> 1367495545, 1367497297, 1367497298, 1367497299, 1367497300, >> >> >>> 1367497303, >> >> >>> 1367497313, 1367497813, 1367497816, 1367497818, >> >> >>> 1367497821, 1367497822, 1367497823, 1624976423, 1624976451, >> >> >>> 1624976457, >> >> >>> 3275636061, 3275640505, 3275645765, 3275645807, >> >> >>> 3275649138, 3275651456, 3275651460, 3275651478, 3275651479, >> >> >>> 3275654566, >> >> >>> 3275654568, 3275654570, 3275654575, 3275659612, >> >> >>> 3275659616, 3275659620, 3275668880, 3275669693, 3275675627, >> >> >>> 3275675634, >> >> >>> 3275677479, 3275677504, 3275678855, 3275679524, >> >> >>> 3275679532, 3275680014, 3275682307, 3275682308, 3275682309, >> >> >>> 3275682310, >> >> >>> 3275682420, 3275682423, 3275682436, 3275682448, >> >> >>> 3275682460, 3275682462, 3275682474, 3275684831, 3275688903, >> >> >>> 3275694023, >> >> >>> 3275694025, 3275694027, 3275695054, 3275695056, >> >> >>> 3275695062, 3275699512, 3275699514, 3275699518, 3275701682, >> >> >>> 3275701683, >> >> >>> 3275701685, 3275701688, 3275703633, 3275703634, >> >> >>> 3275703635, 3275703636, 3275703638, 3275703639, 3275704860, >> >> >>> 3275704861, >> >> >>> 3275764577, 3275797149, 3275798566, 3275798567, >> >> >>> 3275798568, 3275798592, 3275931147, 3275942728, 3275945337, >> >> >>> 3275945338, >> >> >>> 3275945339, 3275945340, 3275945342, 3275945344, >> >> >>> 3275946319, 3275946322, 3275946324, 3275946643, 3275949495, >> >> >>> 3275949498, >> >> >>> 3275949500, 3275950250, 3275955128, 3275955129, >> >> >>> 3275955130, 3427017435, 3427017450, 3438304254, 3438304257, >> >> >>> 3447068169, >> >> >>> 3505227849, 3505227890, 3505556908, 3506351285, >> >> >>> 3506351389, 3506351398, 3506351468, 3510037138, 3510038610, >> >> >>> 3545590644, >> >> >>> 3545594378, 3545595073, 3545595318, 3545595506, >> >> >>> 3545597841, 3545598818, 3545599658, 3545599663, 3545601215, >> >> >>> 3556080898, >> >> >>> 3556080980, 3556080999, 3556081323, 3565122663, >> >> >>> 3565122679, 3565122801, 3565122858, 3565122908, 3565122929, >> >> >>> 3565122952, >> >> >>> 3565122984, 3565123028, 3565123047, 3565123048, >> >> >>> 3565123203, 3565123230, 3949988054, 3949988056, 3949988070, >> >> >>> 3972992248, >> >> >>> 3972992252, 3972992254, 3972992257, 3972992263, >> >> >>> 3972992267, 3972992268, 3972992269, 3972992270, 3972992274, >> >> >>> 3972992275, >> >> >>> 3972992277, 3972992281, 3972992293, 3972992298, >> >> >>> 3972992299, 3972992305, 3972992307, 3972992313, 3972992316, >> >> >>> 3972992322, >> >> >>> 3972992338, 3978471261, 3978471272, 4266318185, >> >> >>> 4298107404, 4308853119, 4308853123, 4308853500, 4451174646, >> >> >>> 4451174656, >> >> >>> 4451174701, 4569827278, 4569827284, 4569827287, >> >> >>> 4569827379, 4569827523, 4569827524, 4896589676, 4979049725, >> >> >>> 5054587609, >> >> >>> 5136433884, 5362640372, 5393109964, 5393405364, >> >> >>> 5393405365, 5393405620, 5393405625, 5393405675, 5393405677, >> >> >>> 5393405858, >> >> >>> 5393405970) >> >> >>> >> >> >>> >> >> >>> Reading your interpretation of the skip scan, I see that the plan >> is >> >> >>> indicating it is only using the salt and the first three columns of >> >> >>> the >> >> >>> index, client_id, and time_id and conversion_type. I hadn't >> >> >>> considered the >> >> >>> salt - that bit of detail in the plan makes more sense to me now. >> It >> >> >>> looks >> >> >>> now like the lackluster performance for higher cardinality >> >> >>> aggregations is >> >> >>> related to scanning a much larger portion of the key space. For >> >> >>> aggregations where I am not relying on filtering, I am seeing much >> >> >>> better >> >> >>> performance. >> >> >>> >> >> >>> So to tune this particular stitch case / skip scan, it looks like I >> >> >>> need to >> >> >>> get the 4th index column into the criteria. There are only four >> >> >>> distinct >> >> >>> values in the fourth index column (these can/should probably be >> >> >>> something >> >> >>> other than varchar, but this is what I have loaded currently). In >> >> >>> order to >> >> >>> use the keyword_id portion of the index I tried explicitly >> specifying >> >> >>> all >> >> >>> device_types via in-list (the commented portion of the query >> above), >> >> >>> but I >> >> >>> get a peculiar error: >> >> >>> >> >> >>> java.lang.IndexOutOfBoundsException: end index (1) must not be less >> >> >>> than >> >> >>> start index (2) >> >> >>> at >> >> >>> >> >> >>> >> com.google.common.base.Preconditions.checkPositionIndexes(Preconditions.java:388) >> >> >>> at >> >> >>> >> com.google.common.collect.ImmutableList.subList(ImmutableList.java:362) >> >> >>> at >> >> >>> >> com.google.common.collect.ImmutableList.subList(ImmutableList.java:62) >> >> >>> at >> >> >>> >> >> >>> >> org.apache.phoenix.filter.SkipScanFilter.intersect(SkipScanFilter.java:291) >> >> >>> at >> >> >>> >> >> >>> >> org.apache.phoenix.filter.SkipScanFilter.intersect(SkipScanFilter.java:177) >> >> >>> at >> >> >>> >> org.apache.phoenix.compile.ScanRanges.intersectScan(ScanRanges.java:316) >> >> >>> at >> >> >>> >> >> >>> >> org.apache.phoenix.iterate.BaseResultIterators.getParallelScans(BaseResultIterators.java:464) >> >> >>> at >> >> >>> >> >> >>> >> org.apache.phoenix.iterate.BaseResultIterators.getParallelScans(BaseResultIterators.java:394) >> >> >>> at >> >> >>> >> >> >>> >> org.apache.phoenix.iterate.BaseResultIterators.<init>(BaseResultIterators.java:184) >> >> >>> at >> >> >>> >> >> >>> >> org.apache.phoenix.iterate.ParallelIterators.<init>(ParallelIterators.java:54) >> >> >>> at >> >> >>> >> >> >>> >> org.apache.phoenix.execute.AggregatePlan.newIterator(AggregatePlan.java:173) >> >> >>> at >> >> >>> >> org.apache.phoenix.execute.BaseQueryPlan.iterator(BaseQueryPlan.java:227) >> >> >>> at >> >> >>> >> org.apache.phoenix.execute.BaseQueryPlan.iterator(BaseQueryPlan.java:154) >> >> >>> at >> >> >>> >> >> >>> >> org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:226) >> >> >>> at >> >> >>> >> >> >>> >> org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:217) >> >> >>> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) >> >> >>> at >> >> >>> >> >> >>> >> org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:216) >> >> >>> at >> >> >>> >> >> >>> >> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1057) >> >> >>> at sqlline.SqlLine$Commands.execute(SqlLine.java:3673) >> >> >>> at sqlline.SqlLine$Commands.sql(SqlLine.java:3584) >> >> >>> at sqlline.SqlLine.dispatch(SqlLine.java:821) >> >> >>> at sqlline.SqlLine.begin(SqlLine.java:699) >> >> >>> at sqlline.SqlLine.mainWithInputRedirection(SqlLine.java:441) >> >> >>> at sqlline.SqlLine.main(SqlLine.java:424) >> >> >>> >> >> >>> >> >> >>> I thought perhaps I was hitting an upper limit on the number of >> >> >>> elements in >> >> >>> an in-list for a skip scan, and so tried removing the 250 element >> >> >>> keyword >> >> >>> in-list entirely and leaving only the device_type in-list, but I >> still >> >> >>> get >> >> >>> the same error. It happens immediately, even for an explain, so I >> >> >>> presume >> >> >>> this is a query parsing problem. Is there a bug or limitation of >> skip >> >> >>> scans >> >> >>> and/or sub lists involving varchar? >> >> >>> >> >> >>> Thx >> >> >>> >> >> >>> >> >> >>> >> > >> > >> > >
