phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gary Schulte <gschu...@marinsoftware.com>
Subject Re: high cardinality aggregation query performance
Date Mon, 02 Mar 2015 20:53:59 GMT
I patched using the original (not simplified) patch against 4.3.1 and it
appears to have fixed the issue.  I updated stats and waited for it to
complete and can no longer reproduce the problem.  I will give the new
patch a whirl also just for grins.

Thanks,

Gary

On Sat, Feb 28, 2015 at 7:57 PM, James Taylor <jamestaylor@apache.org>
wrote:

> Gary,
> I've got a patch available on PHOENIX-1690 that fixes the issue for my
> tests. Would you mind giving it a whirl?
> Thanks,
> James
>
> On Fri, Feb 27, 2015 at 6:40 PM, James Taylor <jamestaylor@apache.org>
> wrote:
> > Thanks, Gary. That should be enough for me to repro (though it's a lot
> > of data!).
> >
> > I've always had to hack up the hbase shell script for remote debugging
> > and then it seems to work.
> >
> > On Fri, Feb 27, 2015 at 6:37 PM, Gary Schulte
> > <gschulte@marinsoftware.com> wrote:
> >> 509 guideposts according to system.stats, getting the table via runtime
> >> seems to work, guide posts, here: http://goo.gl/jvcFec
> >>
> >>
> >> As an aside, I am having issues getting a connection to phoenix/hbase
> >> remotely (so I can debug from my IDE).  I have all the ports open that I
> >> think would play a part - am I missing anything?
> >> 2181,49255,60000,60010,60020,60030,8080,8085,9090, and 9095.
> Connections
> >> from remote just hang and I never get an error or a stack trace.
> >>
> >> Thx
> >>
> >> -Gary
> >>
> >>
> >> On Fri, Feb 27, 2015 at 5:53 PM, James Taylor <jamestaylor@apache.org>
> >> wrote:
> >>>
> >>> Try this code snippet to see if we can force the stats to be send over:
> >>>
> >>> conn.unwrap(PhoenixConnection.class).getQueryServices().clearCache();
> >>> PTable table = PhoenixRuntime.getTable(conn, "PERF.BIG_OLAP_DOC");
> >>> for (GuidePostsInfo info :
> table.getTableStats().getGuidePosts().values())
> >>> {
> >>>     for (byte[] gp : info.getGuidePosts()) {
> >>>         System.out.println(Bytes.toStringBinary(gp));
> >>>     }
> >>> }
> >>>
> >>> Also, try this query and let me know what it says:
> >>>     SELECT sum(GUIDE_POSTS_COUNT)
> >>>     FROM SYSTEM.STATS
> >>>     WHERE PHYSICAL_NAME = "PERF.BIG_OLAP_DOC";
> >>>
> >>> The UPDATE STATISTICS command timing out on the client prevented the
> >>> client-side to pull over the new stats until it was complete on the
> >>> server-side (that's why you only saw it later).
> >>>
> >>> Thanks,
> >>> James
> >>>
> >>> On Fri, Feb 27, 2015 at 5:42 PM, Gary Schulte
> >>> <gschulte@marinsoftware.com> wrote:
> >>> > 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
> >>> > <gschulte@marinsoftware.com>
> >>> > 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 <
> jamestaylor@apache.org>
> >>> >> 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
> >>> >>> <gschulte@marinsoftware.com> 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
> >>> >>> > <jamestaylor@apache.org>
> >>> >>> > 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
> >>> >>> >> <jamestaylor@apache.org>
> >>> >>> >> 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
> >>> >>> >> > <jamestaylor@apache.org>
> >>> >>> >> > 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
> >>> >>> >> >> <gschulte@marinsoftware.com> wrote:
> >>> >>> >> >>> On Tue, Feb 24, 2015 at 12:29 AM, James
Taylor
> >>> >>> >> >>> <jamestaylor@apache.org>
> >>> >>> >> >>> 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
> >>> >>> >> >>>
> >>> >>> >> >>>
> >>> >>> >> >>>
> >>> >>> >
> >>> >>> >
> >>> >>
> >>> >>
> >>> >
> >>
> >>
>

Mime
View raw message