title: Extremely slow distinct query on indexed column
body: <p>In a Postgres database, I am querying distinct values of <code>MY_DATE</code> in a large table with 300 million rows. There are about 400 of them and the column <code>MY_DATE</code> is indexed.</p>
  
  <pre><code>Select distinct  MY_DATE from MY_TABLE;
  </code></pre>
  
  <p>The query runs for <strong>22 min</strong>.</p>
  
  <p>The same query on my Oracle DB with the exact same data-set and the same index definition runs 11 seconds.</p>
  
  <p>The query plan shows that the query is using the index:</p>
  
  <pre><code>EXPLAIN Select distinct  MY_DATE from MY_TABLE LIMIT 200;
  </code></pre>
  
  <p>gives:</p>
  
  <pre><code>QUERY PLAN
  Limit  (cost=0.57..7171644.14 rows=200 width=8)
    -&gt;  Unique  (cost=0.57..15419034.24 rows=430 width=8)
          -&gt;  Index Only Scan using idx_obsdate on my_table  (cost=0.57..14672064.14 rows=298788038 width=8)
  </code></pre>
  
  <p>When I limit the results, the query can become much faster. Ee.g.</p>
  
  <pre><code>Select distinct  MY_DATE from MY_TABLE LIMIT 5;
  </code></pre>
  
  <p>runs in sub-seconds.</p>
  
  <p>but:</p>
  
  <pre><code>Select distinct  MY_DATE from MY_TABLE LIMIT 50;
  </code></pre>
  
  <p>already takes minutes. Time seems to increase exponentially with the <code>LIMIT</code> clause.</p>
  
  <p>I expect the Postgres query to run in seconds, as my OracleDB does.
  20 minutes for an index scan - even for a large table - seems way off the mark.</p>
  
  <p>Any suggestions what causes the issue and what I can do?</p>