Hive select - Fetch and MapReduce
<p>I have two tables</p>

<blockquote>
  <ol>
  <li>External Table A</li>
  <li>External Table B With paritioned and bucketed columns</li>
  </ol>
  
  <p>Table A has 5 columns (lets say c1,c2,c3,c4,c5) </p>
  
  <p>Table B has 5 columns (c1,c2,c3,c4 partitioned on c5 and buketed on c1.</p>
</blockquote>

<p>I run the following queries</p>

<pre><code>1. select * from A where c5="x" and c1="y"
2. select c1,c2,c3 from A where c5="x" and c1="y"
3. select * from B where c5="x" and c1="y"
4. select c1,c2,c3 from B where c5="x" and c1="y"
</code></pre>

<p>All of these do not use <code>MR</code> but just the fetch operator.</p>

<p>Q1: <code>MR</code> or Fetch - Does this depend on the size of the file or its the same behavior always?</p>

<p>Q2: 2nd and 4th queries' behaviors are contradictory to the <a href="https://stackoverflow.com/questions/26008645/hive-why-does-hive-generate-mapreduce-job-on-select-column-from-tablename-vs-n">link</a>. Is this a recent improvement?</p>

<pre><code>5. select count(*) from A where c5="x" and c1="y"
6. select count(*) from B where c5="x" and c1="y"
</code></pre>

<p>Q3: Obviously these 2 needs <code>MR</code> but 5 is twice faster than 6. Ideally, 6 should be faster as the table B is partitioned and bucketed and the query is based on those columns. What could be the possible reasons? I ran the queries multiple times.</p>

<p>Note: I use HDP 2.3</p>