From user-return-7998-apmail-phoenix-user-archive=phoenix.apache.org@phoenix.apache.org Wed May 9 18:52:17 2018 Return-Path: X-Original-To: apmail-phoenix-user-archive@minotaur.apache.org Delivered-To: apmail-phoenix-user-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 83D4B186CF for ; Wed, 9 May 2018 18:52:17 +0000 (UTC) Received: (qmail 35137 invoked by uid 500); 9 May 2018 18:52:17 -0000 Delivered-To: apmail-phoenix-user-archive@phoenix.apache.org Received: (qmail 35074 invoked by uid 500); 9 May 2018 18:52:17 -0000 Mailing-List: contact user-help@phoenix.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@phoenix.apache.org Delivered-To: mailing list user@phoenix.apache.org Received: (qmail 35064 invoked by uid 99); 9 May 2018 18:52:17 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd2-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 09 May 2018 18:52:17 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd2-us-west.apache.org (ASF Mail Server at spamd2-us-west.apache.org) with ESMTP id 894E51A373A for ; Wed, 9 May 2018 18:52:16 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd2-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 1.89 X-Spam-Level: * X-Spam-Status: No, score=1.89 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=2, RCVD_IN_DNSWL_NONE=-0.0001, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, SPF_PASS=-0.001, T_KAM_HTML_FONT_INVALID=0.01, URIBL_BLOCKED=0.001] autolearn=disabled Authentication-Results: spamd2-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-lw-eu.apache.org ([10.40.0.8]) by localhost (spamd2-us-west.apache.org [10.40.0.9]) (amavisd-new, port 10024) with ESMTP id 06j1_tbi_usF for ; Wed, 9 May 2018 18:52:14 +0000 (UTC) Received: from mail-wm0-f43.google.com (mail-wm0-f43.google.com [74.125.82.43]) by mx1-lw-eu.apache.org (ASF Mail Server at mx1-lw-eu.apache.org) with ESMTPS id 2297E5F24C for ; Wed, 9 May 2018 18:52:14 +0000 (UTC) Received: by mail-wm0-f43.google.com with SMTP id l1-v6so203590wmb.2 for ; Wed, 09 May 2018 11:52:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=phitVltcHoKMhXEXdNELOlyJdsLJoayVT34dcFy/1rc=; b=R5pGtJlk8SNjpzD4tMKADQ+X7qcaFxGnAWcitbabphSxhj3W7BlnuNaJ/8xDR0m1ku NpVwuTT45IFp1gD+y6qTHALuB5EbNSmf2k8qF3pXcEXROEiCOV8ExNC397tqx5dWCKAS n/2DIWVj5/SPGhq/h3KWZKNXDi4dG+dAQZ/DDYbRID5XEJBQ2FniUwC+Btp7ujucPiI0 sca08lz8RMNDxmetYW1RrB+F94vSXx/zU/Ig3noeiyMpMl6gb/6d1aRMRTzr0A3RmLFx TLc6wA+rGLA7JJlORhN14nxQiE0DQDfpLH/1wwpPTX06/aVPc+hiO44m96UdIGsXXT9y qmJA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=phitVltcHoKMhXEXdNELOlyJdsLJoayVT34dcFy/1rc=; b=ahxcG9TOr+d+KkMsZDSQXuCp0kQ5FRebT8XzOuBNVKDjNkPhnAeujbHryQTWQTTkZ+ n3dB+jDM8p1qGu/CFM8n38IYb5DlXFTOjKetPkiJdNo4V+ScCNyKcDD2Kl15zb6H8gMa gPbMwuHGvpKmIHIwQQUJNr5HqWoWinF5uSx5Nd9anVj+QIkjh8FRBmVMvNHWXo5mTSLL jhHZ2M1cykEUb9YKSm1tYpu86mkgdJv4d5K1fte+Kh/zZ9/tq85/GYBfJAMXP2Qc3+JW pui50WEAxt/rfqVS0dlNx2yw/NLx3oW4fLyQQQcKwSP5dg/P0TTBfNilt3bNhQf7Pk9i e2EQ== X-Gm-Message-State: ALKqPwe/emlsmSG2ofiqi4liei+yuzh25d2C0rlVuVdGp7VKO1PBlUjM x30XdqSE7st2CFDIDsRl8dVINH+uu9UIyjigJlI= X-Google-Smtp-Source: AB8JxZqqFkIbWOVE3HAeODJhy6wDc7n710YrPBHzxlqEb3Xc0vZKIuwPRQyGouUIQ+MUtE0+ft7OWR23G6Bh4LmcgT8= X-Received: by 2002:a1c:3282:: with SMTP id y124-v6mr6742171wmy.33.1525891926647; Wed, 09 May 2018 11:52:06 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.166.84 with HTTP; Wed, 9 May 2018 11:51:26 -0700 (PDT) In-Reply-To: References: From: Maryann Xue Date: Wed, 9 May 2018 11:51:26 -0700 Message-ID: Subject: Re: SORT_MERGE_JOIN on non-leading key: server-side sorting To: user@phoenix.apache.org Cc: Gerald Sangudi Content-Type: multipart/alternative; boundary="00000000000040e763056bca66ca" --00000000000040e763056bca66ca Content-Type: text/plain; charset="UTF-8" Hi Gerald, I have verified against latest Phoenix code that this problem has been fixed. I have also checked Phoenix 4.13 release tags. Looks like all versions of 4.13 packages now include that fix. Would you mind getting the latest Phoenix-4.13 package and testing it again? Thank you! Thanks, Maryann On Tue, May 8, 2018 at 12:24 PM, Gerald Sangudi wrote: > Hello, > > I'm running Phoenix 4.13 on AWS EMR and getting the following EXPLAIN plan: > > Table: > > *CREATE TABLE salted ( keyA BIGINT NOT NULL, keyB BIGINT NOT > NULL, val SMALLINT, CONSTRAINT pk PRIMARY KEY (keyA, > keyB))SALT_BUCKETS = 64;* > EXPLAIN: > > *EXPLAINSELECT /*+ USE_SORT_MERGE_JOIN */COUNT(*) cFROM salted t1 JOIN > salted t2ON (t1.keyB = t2.keyB)WHERE t1.keyA = 10AND t2.keyA = 20;* > > *+------------------------------------------------------------------------------+-----------------+-+| > PLAN | EST_BYTES_READ | > |+------------------------------------------------------------------------------+-----------------+-+| > SORT-MERGE-JOIN (INNER) TABLES > | null | || CLIENT > 64-CHUNK PARALLEL 64-WAY RANGE SCAN OVER SALTED [0,10] - [63,10] | null | > || SERVER FILTER BY FIRST KEY ONLY > | null | || CLIENT MERGE SORT > | null | || AND > (SKIP MERGE) | > null | || CLIENT 64-CHUNK PARALLEL 64-WAY RANGE SCAN OVER SALTED [0,20] > - [63,20] | null | || SERVER FILTER BY FIRST KEY ONLY > | null | || SERVER SORTED BY > [T2.KEYB] | null | || CLIENT > MERGE SORT | null | > || CLIENT AGGREGATE INTO SINGLE ROW > | null | > |+------------------------------------------------------------------------------+-----------------+-+* > > In the EXPLAIN output, I see "SERVER SORTED BY [T2.KEYB]". Is this sort > necessary? For both JOIN terms T1 and T2, the value of keyA, the leading > part of the primary key, is fixed. Furthermore, there is no corresponding > sort of T1.KEYB. > > When I EXPLAIN the same query on a non-salted table, neither T1.KEYB nor > T2.KEYB is re-sorted. I'm hoping the sort is unnecessary. If so, is there > an open ticket? I would be happy to file a ticket and to contribute to a > fix. I would appreciate any guidance. > > Thanks, > Gerald > > --00000000000040e763056bca66ca Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Gerald,

I have verified against late= st Phoenix code that this problem has been fixed. I have also checked Phoen= ix 4.13 release tags. Looks like all versions of 4.13 packages now include = that fix. Would you mind getting the latest Phoenix-4.13 package and testin= g it again? Thank you!


Thanks,
Maryann

On Tue, May 8, 2018 at 12:24 PM, Gerald Sangudi &= lt;gsangudi@23and= me.com> wrote:
Hello,

I'm running Phoe= nix 4.13 on AWS EMR and getting the following EXPLAIN plan:

=
= Table:

CREATE TABLE salted (

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0keyA BIGINT NOT NULL,

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0keyB BIGINT NOT NULL,<= /span>

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0val SMALLINT,<= /span>

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0CONSTRAINT pk PRIMARY= KEY (keyA, keyB)

)

SALT_BU= CKETS =3D 64;


EXPLAIN:

EXPLAIN

SE= LECT /*+ USE_SORT_MERGE_JOIN */

COUNT(*) c

FROM salted t1 JOIN salted t2

ON (t1.keyB =3D t2.keyB)

WHERE t1.keyA =3D 10

AND t2.keyA =3D 20;


+------------------= ------------------------------------------------------------+----= -------------+-+

| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0PLAN =C2=A0=C2=A0| EST_BYTES_READ | |=

+-----------------= -------------------------------------------------------------+---= --------------+-+

| SORT-MERGE-JOIN (INNER) TABLES =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0| null | |

| =C2=A0=C2=A0=C2=A0=C2=A0CLIENT 64-CHUNK PARALLE= L 64-WAY RANGE SCAN OVER SALTED [0,10] - [63,10] =C2=A0| null | = |

| =C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0SERVER FILTER BY FIRST KEY ONLY =C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| null = | |

| = =C2=A0=C2=A0=C2=A0=C2=A0CLIENT MERGE SORT =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0| null | |

| AND (SKIP MERGE) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0| null | |

| =C2=A0=C2=A0=C2=A0=C2=A0CLIENT 64-CHUNK PARALLE= L 64-WAY RANGE SCAN OVER SALTED [0,20] - [63,20] =C2=A0| null | = |

| =C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0SERVER FILTER BY FIRST KEY ONLY =C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| null = | |

| = =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0SERVER SORTED BY [T2.KEYB] =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| null= | |

| = =C2=A0=C2=A0=C2=A0=C2=A0CLIENT MERGE SORT =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0| null | |

| CLIENT AGGREGATE INTO SINGLE ROW =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0| null | |

+------------------------------------------------------= ------------------------+-----------------+-+



In the EXPLAIN output, I see "= SERVER SORTED BY [T2.KEYB]". Is this sort necessary? For both JOIN ter= ms T1 and T2, the value of keyA, the leading part of the primary key, is fi= xed. Furthermore, there is no corresponding sort of T1.KEYB.

When I EXPLAIN the same query on a non-salted table, neither T1.KEYB nor T= 2.KEYB is re-sorted. I'm hoping the sort is unnecessary. If so, is ther= e an open ticket? I would be happy to file a ticket and to contribute to a = fix. I would appreciate any guidance.

Thanks,
Gerald

--00000000000040e763056bca66ca--