Thanks again for the input, Ralph! Pinpointed the problem and filed a JIRA ( https://issues.apache.org/jira/browse/PHOENIX-1533). Will try to work out a solution ASAP.
On Mon, Dec 15, 2014 at 3:38 PM, Perko, Ralph J <[email protected]> wrote: > > I am using version 4.2.2 > > > From: <Perko>, Ralph Perko <[email protected]> > Reply-To: "[email protected]" <[email protected]> > Date: Monday, December 15, 2014 at 12:37 PM > > To: "[email protected]" <[email protected]> > Subject: Re: Query performance question > > DDL is attached – thanks! > > Ralph > > > From: Maryann Xue <[email protected]> > Reply-To: "[email protected]" <[email protected]> > Date: Monday, December 15, 2014 at 12:21 PM > To: "[email protected]" <[email protected]> > Subject: Re: Query performance question > > Hi Ralph, > > Thank you very much for the information! Very helpful for your questions. > The numbers look reasonable as opposed to the query plan. But the only > problem is both file_id and recnum should be used in "DYNAMIC SERVER > FILTER" (which will turn into a skip-scan), but the plan you got there only > used file_id alone. > So could you please attach the DDL of your table and index so that I can > try reproducing the problem? > > > Thanks, > Maryann > > > On Mon, Dec 15, 2014 at 2:46 PM, Perko, Ralph J <[email protected]> > wrote: >> >> My apologies for the delay. I had to switch clusters and use a >> smaller dataset. The discrepancy still exists but the numbers are a little >> different: >> >> I ran the same queries as in the original email (below) >> >> Total records: 581M >> >> Simple query based on secondary index value used in the subselect: >> 1747 recs – 0.256 sec >> >> Subselect query: 177s >> >> Join query: 179s >> >> The answers to your questions are below. >> >> 1. What is the Primary Key definition of your BULK_TABLE? >> >> CONSTRAINT pkey PRIMARY KEY (file_id,recnum) >> >> 2. How many (approximately) distinct "file_id" values are there in the >> BULK_TABLE? (If you don't know for sure, you can just run a query to find >> out). >> >> "select count(distinct(file_id)) from BULK_TABLE” - 7902 - 92s >> >> "select distinct(file_id) from BULK_TABLE” - returns in 25m >> >> 3. How long does it take to run a full-scan query on BULK_TABLE, like >> "select * from BULK_TABLE”? >> >> Results began returning after about 25min >> >> 4. How long does it take to run a full-scan join query on BULK_TABLE, >> like "select * from BULK_TABLE join (select file_id, recnum from >> BULK_TABLE) as SS on BULK_TABLE.file_id = SS.file_id and BULK_TABLE.recnum >> = SS.recnum”? >> >> The full-scan join fails with a MaxServerCacheSizeExceededException - >> server cache set to 1G. >> >> Custom hbase/phoenix settings are attached. >> >> Thanks, >> Ralph >> >> >> From: Maryann Xue <[email protected]> >> Reply-To: "[email protected]" <[email protected]> >> Date: Friday, December 12, 2014 at 8:07 AM >> To: "[email protected]" <[email protected]> >> Subject: Re: Query performance question >> >> Hi Ralph, >> >> Thanks for the question! >> According to the "explain" result you got, the optimization worked >> exactly as expected with this query: >> >> "DYNAMIC SERVER FILTER BY FILE_ID IN (SS.FILE_ID)" means a skip-scan >> instead of a full-scan over BULK_TABLE will be executed at runtime based on >> the values of "file_id" it got from the inner query. >> >> So I need to know a few more things: >> 1. What is the Primary Key definition of your BULK_TABLE? >> 2. How many (approximately) distinct "file_id" values are there in the >> BULK_TABLE? (If you don't know for sure, you can just run a query to find >> out). >> 3. How long does it take to run a full-scan query on BULK_TABLE, like >> "select * from BULK_TABLE"? >> 4. How long does it take to run a full-scan join query on BULK_TABLE, >> like "select * from BULK_TABLE join (select file_id, recnum from >> BULK_TABLE) as SS on BULK_TABLE.file_id = SS.file_id and >> BULK_TABLE.recnum = SS.recnum"? >> >> >> Thanks, >> Maryann >> >> >> On Thu, Dec 11, 2014 at 6:28 PM, Perko, Ralph J <[email protected]> >> wrote: >>> >>> Hi, >>> >>> Thanks for all your help thus far with Phoenix. >>> >>> I am trying to understand the best way to construct a query that >>> returns all the fields from a table but still takes advantage of a single >>> field secondary index. I have a table with upwards of 50 fields and do >>> not wish to index them all but the use case exists to return them all. >>> >>> My general approach is to first select the records I want using an >>> indexed field then use the returned pk values to get the entire record in >>> the form of a subselect or join. >>> >>> The initial select executes very fast, sub-second , returning close to >>> 3000 records. When used as a subselect or join the entire query takes very >>> long (over 15min) or does not return. Based on the processing plans it >>> appears there is a lot more going on than just a simple look-up of the >>> values returned in the subselect. Is there a way to do this using Phoenix >>> SQL syntax? >>> >>> Any suggestions are appreciated. >>> >>> Initial indexed query (very fast): >>> >>> SELECT file_id,recnum >>> >>> FROM BULK_TABLE >>> >>> WHERE saddr IN (ip1,ip2,ip3)) >>> >>> >>> file_id and recnum make up the primary key >>> >>> >>> Plan: >>> >>> | CLIENT PARALLEL 10-WAY SKIP SCAN ON 30 KEYS OVER BULK_TABLE_SADDR_IDX >>> [0,1,000,004,076] - [9,1,000,142,114] | >>> >>> | CLIENT MERGE SORT | >>> >>> >>> Used as a subselect (times out): >>> >>> SELECT * FROM BULK_TABLE >>> >>> WHERE (file_id,recnum) IN(SELECT file_id,recnum >>> >>> FROM BULK_TABLE >>> >>> WHERE saddr IN (ip1,ip2,ip3)); >>> >>> Plan: >>> >>> | CLIENT PARALLEL 10-WAY FULL SCAN OVER BULK_TABLE | >>> >>> | CLIENT MERGE SORT | >>> >>> | PARALLEL SEMI-JOIN TABLE 0 (SKIP MERGE) | >>> >>> | CLIENT PARALLEL 10-WAY SKIP SCAN ON 30 KEYS >>> OVER BULK_TABLE_SADDR_IDX [0,1,000,004,076] - [9,1,000,142,114] | >>> >>> | SERVER AGGREGATE INTO DISTINCT ROWS BY [RECNUM, FILE_ID] | >>> >>> | CLIENT MERGE SORT | >>> >>> | DYNAMIC SERVER FILTER BY FILE_ID IN ($1.$3) | >>> >>> >>> Another approach using using a join instead: >>> >>> >>> SELECT * >>> >>> FROM BULK_TABLE >>> >>> JOIN >>> >>> (SELECT file_id, recnum >>> >>> FROM BULK_TABLE >>> >>> WHERE saddr in (ip1,ip2,ip3)) AS SS >>> >>> ON BULK_TABLE.file_id = SS.file_id AND BULK_TABLE.recnum = SS.recnum; >>> >>> >>> Runs faster but still can take about 15min >>> >>> >>> Plan: >>> >>> >>> | CLIENT PARALLEL 10-WAY FULL SCAN OVER BULK_TABLE | >>> >>> | CLIENT MERGE SORT | >>> >>> | PARALLEL INNER-JOIN TABLE 0 | >>> >>> | CLIENT PARALLEL 10-WAY SKIP SCAN ON 30 KEYS >>> OVER BULK_TABLE_SADDR_IDX [0,1,000,004,076] - [9,1,000,142,114] | >>> >>> | CLIENT MERGE SORT | >>> >>> | DYNAMIC SERVER FILTER BY FILE_ID IN (SS.FILE_ID) | >>> >>> >>> >>> Is there a more efficient way to run a query such as this? >>> >>> Thanks! >>> Ralph >>> >>> >>> >>> >>> >>> >> >> -- >> Thanks, >> Maryann >> > > > -- > Thanks, > Maryann > -- Thanks, Maryann
