phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Talanki, Vikash" <vtala...@visa.com>
Subject RE: phoenix explain plan not showing any difference after adding a local index on the table column that is used in query filter
Date Wed, 29 Jun 2016 19:26:29 GMT
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta content="text/html; charset=utf-8">
</head>
<body>
Hi Ankit,
<div><br>
</div>
<div>I think this is not completely true.&nbsp;</div>
<div>Phoenix uses local index table when all columns in where clause and atleast one
column in select clause are part of local index table.&nbsp;</div>
<div><br>
</div>
<div>&quot;Select col1,col2,col3 from table&quot; uses local index but &quot;select
* from table&quot; do not.&nbsp;</div>
<div>I'm not certain of the reason&nbsp;<br>
<br>
<br>
Sent with Good Work (www.good.com)<br>
<br>
</div>
<div style="border-top:#b5c4df 1pt solid; padding-top:6px; font-size:14px">
<div><b>From: </b><span>Ankit Singhal &lt;<a href="mailto:ankitsinghal59@gmail.com">ankitsinghal59@gmail.com</a>&gt;</span></div>
<div><b>Date: </b><span>Wednesday, Jun 29, 2016, 12:15</span></div>
<div><b>To: </b><span>user@phoenix.apache.org &lt;<a href="mailto:user@phoenix.apache.org">user@phoenix.apache.org</a>&gt;</span></div>
<div><b>Subject: </b><span>Re: phoenix explain plan not showing any
difference after adding a local index on the table column that is used in query filter</span></div>
</div>
<br>
<div>
<div dir="ltr">Hi Vamsi,
<div><br>
</div>
<div>
<div style="font-size:12.8px"><u>Explain plan:</u></div>
<div style="font-size:12.8px">explain select * from vamsi.table_a where col2 = 'abc';</div>
<div style="font-size:12.8px">&#43;-------------------------------------------------------------&#43;</div>
<div style="font-size:12.8px">| &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PLAN
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |</div>
<div style="font-size:12.8px">&#43;-------------------------------------------------------------&#43;</div>
<div style="font-size:12.8px">| CLIENT 5-CHUNK PARALLEL 5-WAY FULL SCAN OVER VAMSI.TABLE_A
&nbsp;|</div>
<div style="font-size:12.8px">| &nbsp; &nbsp; SERVER FILTER BY COL2 = 'abc'
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; |</div>
<div style="font-size:12.8px">&#43;-------------------------------------------------------------&#43;</div>
</div>
<div style="font-size:12.8px"><br>
</div>
<div style="font-size:12.8px">You are selecting all the columns of the table, which
are not part of local index (and are also not covered) so instead of scanning index and join
back to data table to get all the columns is costly operation. so optimizer chooses
 data table to scan instead of using index to serve query.</div>
<div style="font-size:12.8px"><span style="font-size:12.8px"><br>
</span></div>
<div style="font-size:12.8px"><span style="font-size:12.8px">below query should
use local indexes:-</span></div>
<div style="font-size:12.8px"><span style="font-size:12.8px">explain select col2,
any_covered_colums from vamsi.table_a where col2 = 'abc';</span><br>
</div>
<div style="font-size:12.8px"><span style="font-size:12.8px"><br>
</span></div>
<div style="font-size:12.8px"><span style="font-size:12.8px">For covered indexes
, you can read&nbsp;</span></div>
<div><span style="font-size:12.8px"><a href="https://phoenix.apache.org/secondary_indexing.html">https://phoenix.apache.org/secondary_indexing.html</a></span><br>
</div>
<div><span style="font-size:12.8px"><br>
</span></div>
<div><span style="font-size:12.8px">Regards,</span></div>
<div><span style="font-size:12.8px">Ankit Singhal</span></div>
<div style="font-size:12.8px"><span style="font-size:12.8px"><br>
</span></div>
<div style="font-size:12.8px"><span style="font-size:12.8px"><br>
</span></div>
<div style="font-size:12.8px"><br>
</div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Tue, Jun 28, 2016 at 4:25 AM, Vamsi Krishna <span dir="ltr">
&lt;<a href="mailto:vamsi.attluri@gmail.com" target="_blank">vamsi.attluri@gmail.com</a>&gt;</span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex; border-left:1px #ccc solid; padding-left:1ex">
<div dir="ltr">Team,
<div><br>
</div>
<div>
<div>I'm using HDP 2.3.2 (HBase : 1.1.2, Phoenix : 4.4.0).</div>
<div><b>Question:&nbsp;</b>phoenix explain plan not showing any difference
after adding a local index on the table column that is used in query filter. Can someone please
explain why?</div>
<div><br>
</div>
<div><u>Create table:</u></div>
<div>
<div>CREATE TABLE IF NOT EXISTS VAMSI.TABLE_A (COL1 VARCHAR(36) , COL2 VARCHAR(36) ,
COL3 VARCHAR(36) CONSTRAINT TABLE_A_PK PRIMARY KEY (COL1))&nbsp;</div>
<div>COMPRESSION='SNAPPY', SALT_BUCKETS=5;</div>
</div>
<div>
<div><u>Insert data:</u></div>
<div>upsert into vamsi.table_a values ('abc123','abc','123');</div>
<div>upsert into vamsi.table_a values ('def456','def','456');</div>
</div>
<div><br>
</div>
<div><u>Explain plan:</u></div>
<div>
<div>explain select * from vamsi.table_a where col2 = 'abc';</div>
<div>&#43;-------------------------------------------------------------&#43;</div>
<div>| &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PLAN &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; |</div>
<div>&#43;-------------------------------------------------------------&#43;</div>
<div>| CLIENT 5-CHUNK PARALLEL 5-WAY FULL SCAN OVER VAMSI.TABLE_A &nbsp;|</div>
<div>| &nbsp; &nbsp; SERVER FILTER BY COL2 = 'abc' &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; |</div>
<div>&#43;-------------------------------------------------------------&#43;</div>
</div>
<div><br>
</div>
<div><u>Create local index:</u></div>
<div>CREATE LOCAL INDEX IF NOT EXISTS IDX_TABLE_A_COL2 ON VAMSI.TABLE_A (COL2);<br>
</div>
<div>
<div><br>
</div>
<div><u>Explain plan:</u></div>
<div>explain select * from vamsi.table_a where col2 = 'abc';</div>
<div>&#43;-------------------------------------------------------------&#43;</div>
<div>| &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PLAN &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; |</div>
<div>&#43;-------------------------------------------------------------&#43;</div>
<div>| CLIENT 5-CHUNK PARALLEL 5-WAY FULL SCAN OVER VAMSI.TABLE_A &nbsp;|</div>
<div>| &nbsp; &nbsp; SERVER FILTER BY COL2 = 'abc' &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; |</div>
<div>&#43;-------------------------------------------------------------&#43;</div>
</div>
<div><br>
</div>
<div>Thanks,</div>
</div>
<div>Vamsi Attluri</div>
<span class="HOEnZb"><font color="#888888">
<div><br>
</div>
</font></span></div>
<span class="HOEnZb"><font color="#888888">
<div dir="ltr">-- <br>
</div>
<div>
<div dir="ltr">Vamsi Attluri</div>
</div>
</font></span></blockquote>
</div>
<br>
</div>
</div>
</body>
</html>

Mime
View raw message