[ https://issues.apache.org/jira/browse/PHOENIX-4508?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16314496#comment-16314496 ]
Flavio Pompermaier commented on PHOENIX-4508: --------------------------------------------- Sorry, EID is not part of the PK because it can be null...the two statements are: {code:sql} CREATE TABLE IF NOT EXISTS MYTABLE ( LOCALID VARCHAR NOT NULL, DSID VARCHAR(255) NOT NULL, EID CHAR(40), ENTITY_TYPE CHAR(40), HAS_CANDIDATES BOOLEAN, MATCHING_REASON VARBINARY, TO_FIX BOOLEAN CONSTRAINT PK_MYTABLE PRIMARY KEY (LOCALID,DSID)) SALT_BUCKETS = 3 {code} {code:sql} CREATE TABLE IF NOT EXISTS PEOPLE ( PERSON_ID VARCHAR NOT NULL, CF_PIVA_VALID BOOLEAN, VALID BOOLEAN, FORMALITA BIGINT, FOTO BIGINT, VEICOLO BIGINT, TARGA VARCHAR, SERIETARGA INTEGER, ID_NASCITA_NAZIONE VARCHAR, ID_NASCITA_IT_PROVINCIA VARCHAR, ID_NASCITA_IT_COMUNE VARCHAR, ID_RES_NAZIONE VARCHAR, ID_RES_IT_PROVINCIA VARCHAR, ID_RES_IT_COMUNE VARCHAR, ID_RES_NAZIONE_LEGALE VARCHAR, ID_RES_IT_PROVINCIA_LEGALE VARCHAR, ID_RES_IT_COMUNE_LEGALE VARCHAR, PARTITAIVA VARCHAR, CODICEFISCALE VARCHAR, COGNOME VARCHAR, NOME VARCHAR, SESSO VARCHAR, NASCITA_NAZIONE VARCHAR, NASCITA_COMUNE VARCHAR, DATA_NASCITA VARCHAR, NASCITA_ESTERA_LUOGO VARCHAR, RES_ITALIANA_FRAZIONE VARCHAR, RES_ITALIANA_CAP VARCHAR, RES_ITALIANA_DUG VARCHAR, RES_ITALIANA_TOPONIMO VARCHAR, RES_ITALIANA_CIVICO VARCHAR, RESIDENZA_ESTERA_CITTA VARCHAR, RESIDENZA_ESTERA_INDIRIZZO VARCHAR, RESIDENZA_ESTERA_ZIPCODE VARCHAR CONSTRAINT PK_TEST_PEOPLE PRIMARY KEY (PERSON_ID)) SALT_BUCKETS = 3 {code} > Wrong query plan generation > --------------------------- > > Key: PHOENIX-4508 > URL: https://issues.apache.org/jira/browse/PHOENIX-4508 > Project: Phoenix > Issue Type: Bug > Affects Versions: 4.13.2 > Reporter: Flavio Pompermaier > Labels: planner, query > > In my Phoenix tables I found that one query ens successfully while another > one, logically equal, does not (unless that I don't apply some tuning to > timeouts). > The 2 queries extract the same data but, while the first query terminates the > second does not. > PS: without the USE_SORT_MERGE_JOIN both queries weren't working > ---- > h2. First query > {code:sql} > SELECT /*+ USE_SORT_MERGE_JOIN */ COUNT(*) > FROM PEOPLE ds JOIN MYTABLE l ON ds.PERSON_ID = l.LOCALID > WHERE l.EID IS NULL AND l.DSID = 'PEOPLE' AND l.HAS_CANDIDATES = FALSE; > {code} > +---------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+ > | PLAN > | EST_BYTES_READ | EST_ROWS_READ | > EST_INFO_TS | > +---------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+ > | SORT-MERGE-JOIN (INNER) TABLES > | 14155777900 | 12077867 | > 1513754378759 | > | CLIENT 42-CHUNK 6168903 ROWS 11324622221 BYTES PARALLEL 3-WAY FULL SCAN > OVER PEOPLE | 14155777900 | 12077867 | > 1513754378759 | > | SERVER FILTER BY FIRST KEY ONLY > | 14155777900 | 12077867 | > 1513754378759 | > | CLIENT MERGE SORT > | 14155777900 | 12077867 | > 1513754378759 | > | AND (SKIP MERGE) > | 14155777900 | 12077867 | > 1513754378759 | > | CLIENT 15-CHUNK 5908964 ROWS 2831155679 BYTES PARALLEL 15-WAY RANGE > SCAN OVER MYTABLE [0] - [2] | 14155777900 | 12077867 | > 1513754378759 | > | SERVER FILTER BY (EID IS NULL AND DSID = 'PEOPLE' AND > HAS_CANDIDATES = false) | 14155777900 | 12077867 > | 1513754378759 | > | SERVER SORTED BY [L.LOCALID] > | 14155777900 | 12077867 | > 1513754378759 | > | CLIENT MERGE SORT > | 14155777900 | 12077867 | > 1513754378759 | > | CLIENT AGGREGATE INTO SINGLE ROW > | 14155777900 | 12077867 | > 1513754378759 | > +---------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+ > 10 rows selected (0.041 seconds) > ---- > h2. Second query > {code:sql} > SELECT /*+ USE_SORT_MERGE_JOIN */ COUNT(*) > FROM (SELECT LOCALID FROM MYTABLE > WHERE EID IS NULL AND DSID = 'PEOPLE' AND HAS_CANDIDATES = FALSE) l JOIN > PEOPLE ds ON ds.PERSON_ID = l.LOCALID; > {code} > +--------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+ > | PLAN > | EST_BYTES_READ | EST_ROWS_READ | > EST_INFO_TS | > +--------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+ > | SORT-MERGE-JOIN (INNER) TABLES > | 14155777900 | 12077867 | > 1513754378759 | > | CLIENT 15-CHUNK 5908964 ROWS 2831155679 BYTES PARALLEL 3-WAY RANGE SCAN > OVER MYTABLE [0] - [2] | 14155777900 | 12077867 | 1513754378759 | > | SERVER FILTER BY (EID IS NULL AND DSID = 'PEOPLE' AND > HAS_CANDIDATES = false) | 14155777900 | 12077867 | > 1513754378759 | > | CLIENT MERGE SORT > | 14155777900 | 12077867 | > 1513754378759 | > | AND (SKIP MERGE) > | 14155777900 | 12077867 | > 1513754378759 | > | CLIENT 42-CHUNK 6168903 ROWS 11324622221 BYTES PARALLEL 42-WAY FULL > SCAN OVER PEOPLE | 14155777900 | 12077867 | > 1513754378759 | > | SERVER FILTER BY FIRST KEY ONLY > | 14155777900 | 12077867 | > 1513754378759 | > | SERVER SORTED BY [DS.PERSON_ID] > | 14155777900 | 12077867 | > 1513754378759 | > | CLIENT MERGE SORT > | 14155777900 | 12077867 | > 1513754378759 | > | CLIENT AGGREGATE INTO SINGLE ROW > | 14155777900 | 12077867 | > 1513754378759 | > +--------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+ -- This message was sent by Atlassian JIRA (v6.4.14#64029)