Re: Explain plan over primary key column
Sure James. I think I also figured why rowkeyindex is used here. The global index rule for having all columns in the query applies to secondary indexes only, I suppose and not to primary key based lookups so which is why explain plan correctly reports using rowkey in this case. From: James Heather <james.heat...@mendeley.com> To: user@phoenix.apache.org Sent: Wednesday, September 30, 2015 8:08 PM Subject: Re: Explain plan over primary key column I don't see why it wouldn't be able to use the index for that. In any case, if that's what "explain" is telling you, I think you can trust it... James On 30/09/15 15:36, Sumit Nigam wrote: Thanks so much James ... I am sorry to be asking so many questions ... But last one - When I - EXPLAIN SELECT ID, TEXT FROM EXP WHERE ID > 5 AND ID < 10 here TEXT is not a part of PK, then also I get the same plan - RANGE SCAN OVER EXP [6] - [10] Which I suppose just means that rowkey index is still used. I thought that global indexes are only used when all columns in the query are a part of it. Sorry again. Sumit From: James Heather <james.heat...@mendeley.com> To: user@phoenix.apache.org Sent: Wednesday, September 30, 2015 7:58 PM Subject: Re: Explain plan over primary key column Yup On 30/09/15 15:25, Sumit Nigam wrote: Thanks James. So, if I did a range lookup like - EXPLAIN SELECT ID FROM EXP WHERE ID > 5 AND ID < 10 then I get RANGE SCAN OVER EXP [6] - [10] Is that indication enough that PK/ index is used? From: James Heather <james.heat...@mendeley.com> To: user@phoenix.apache.org Sent: Wednesday, September 30, 2015 7:49 PM Subject: Re: Explain plan over primary key column You're asking for every single row of the table, so nothing's going to avoid a full scan. The index wouldn't help. On 30/09/15 15:18, Sumit Nigam wrote: Hi, I have a table as: CREATE TABLE EXP (ID BIGINT NOT NULL PRIMARY KEY, TEXT VARCHAR); If I explain the select: EXPLAIN SELECT ID FROM EXP; Then it shows CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER EXP I assume it still uses rowkey. Or is it should have shown using rowkey in explain plan? Also, is it a good idea to declare VARCHAR(n) vs plain VARCHAR? Does it save some space? Thanks, Sumit
Re: Explain plan over primary key column
You're asking for every single row of the table, so nothing's going to avoid a full scan. The index wouldn't help. On 30/09/15 15:18, Sumit Nigam wrote: Hi, I have a table as: CREATE TABLE EXP (ID BIGINT NOT NULL PRIMARY KEY, TEXT VARCHAR); If I explain the select: EXPLAIN SELECT ID FROM EXP; Then it shows CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER EXP I assume it still uses rowkey. Or is it should have shown using rowkey in explain plan? Also, is it a good idea to declare VARCHAR(n) vs plain VARCHAR? Does it save some space? Thanks, Sumit
Re: Explain plan over primary key column
Yup On 30/09/15 15:25, Sumit Nigam wrote: Thanks James. So, if I did a range lookup like - EXPLAIN SELECT ID FROM EXP WHERE ID > 5 AND ID < 10 then I get RANGE SCAN OVER EXP [6] - [10] Is that indication enough that PK/ index is used? *From:* James Heather <james.heat...@mendeley.com> *To:* user@phoenix.apache.org *Sent:* Wednesday, September 30, 2015 7:49 PM *Subject:* Re: Explain plan over primary key column You're asking for every single row of the table, so nothing's going to avoid a full scan. The index wouldn't help. On 30/09/15 15:18, Sumit Nigam wrote: Hi, I have a table as: CREATE TABLE EXP (ID BIGINT NOT NULL PRIMARY KEY, TEXT VARCHAR); If I explain the select: EXPLAIN SELECT ID FROM EXP; Then it shows CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER EXP I assume it still uses rowkey. Or is it should have shown using rowkey in explain plan? Also, is it a good idea to declare VARCHAR(n) vs plain VARCHAR? Does it save some space? Thanks, Sumit
Re: Explain plan over primary key column
Thanks so much James ... I am sorry to be asking so many questions ... But last one - When I - EXPLAIN SELECT ID, TEXT FROM EXP WHERE ID > 5 AND ID < 10 here TEXT is not a part of PK, then also I get the same plan - RANGE SCAN OVER EXP [6] - [10] Which I suppose just means that rowkey index is still used. I thought that global indexes are only used when all columns in the query are a part of it. Sorry again.Sumit From: James Heather <james.heat...@mendeley.com> To: user@phoenix.apache.org Sent: Wednesday, September 30, 2015 7:58 PM Subject: Re: Explain plan over primary key column Yup On 30/09/15 15:25, Sumit Nigam wrote: Thanks James. So, if I did a range lookup like - EXPLAIN SELECT ID FROM EXP WHERE ID > 5 AND ID < 10 then I get RANGE SCAN OVER EXP [6] - [10] Is that indication enough that PK/ index is used? From: James Heather <james.heat...@mendeley.com> To: user@phoenix.apache.org Sent: Wednesday, September 30, 2015 7:49 PM Subject: Re: Explain plan over primary key column You're asking for every single row of the table, so nothing's going to avoid a full scan. The index wouldn't help. On 30/09/15 15:18, Sumit Nigam wrote: Hi, I have a table as: CREATE TABLE EXP (ID BIGINT NOT NULL PRIMARY KEY, TEXT VARCHAR); If I explain the select: EXPLAIN SELECT ID FROM EXP; Then it shows CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER EXP I assume it still uses rowkey. Or is it should have shown using rowkey in explain plan? Also, is it a good idea to declare VARCHAR(n) vs plain VARCHAR? Does it save some space? Thanks, Sumit
Re: Explain plan over primary key column
I don't see why it wouldn't be able to use the index for that. In any case, if that's what "explain" is telling you, I think you can trust it... James On 30/09/15 15:36, Sumit Nigam wrote: Thanks so much James ... I am sorry to be asking so many questions ... But last one - When I - EXPLAIN SELECT ID, TEXT FROM EXP WHERE ID > 5 AND ID < 10 here TEXT is not a part of PK, then also I get the same plan - RANGE SCAN OVER EXP [6] - [10] Which I suppose just means that rowkey index is still used. I thought that global indexes are only used when all columns in the query are a part of it. Sorry again. Sumit *From:* James Heather <james.heat...@mendeley.com> *To:* user@phoenix.apache.org *Sent:* Wednesday, September 30, 2015 7:58 PM *Subject:* Re: Explain plan over primary key column Yup On 30/09/15 15:25, Sumit Nigam wrote: Thanks James. So, if I did a range lookup like - EXPLAIN SELECT ID FROM EXP WHERE ID > 5 AND ID < 10 then I get RANGE SCAN OVER EXP [6] - [10] Is that indication enough that PK/ index is used? *From:* James Heather <james.heat...@mendeley.com> <mailto:james.heat...@mendeley.com> *To:* user@phoenix.apache.org <mailto:user@phoenix.apache.org> *Sent:* Wednesday, September 30, 2015 7:49 PM *Subject:* Re: Explain plan over primary key column You're asking for every single row of the table, so nothing's going to avoid a full scan. The index wouldn't help. On 30/09/15 15:18, Sumit Nigam wrote: Hi, I have a table as: CREATE TABLE EXP (ID BIGINT NOT NULL PRIMARY KEY, TEXT VARCHAR); If I explain the select: EXPLAIN SELECT ID FROM EXP; Then it shows CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER EXP I assume it still uses rowkey. Or is it should have shown using rowkey in explain plan? Also, is it a good idea to declare VARCHAR(n) vs plain VARCHAR? Does it save some space? Thanks, Sumit
