title: Postgres not using index to sort data
body: <p>I have a table <code>learners</code> which has around 3.2 million rows. This table contains user related information like name and email. I need to optimize some queries that uses order by on some column. So for testing I have created a <code>temp_learners</code> table, with 0.8 million rows. I have created two indexes on this table:</p>
  <pre><code>CREATE UNIQUE INDEX &quot;temp_learners_companyId_userId_idx&quot;
    ON temp_learners (&quot;companyId&quot; ASC, &quot;userId&quot; ASC, &quot;learnerUserName&quot; ASC, &quot;learnerEmailId&quot; ASC);
  </code></pre>
  <p>and</p>
  <pre><code>CREATE INDEX temp_learners_company_name_email_index
    ON temp_learners (&quot;companyId&quot;, &quot;learnerUserName&quot;, &quot;learnerEmailId&quot;);
  </code></pre>
  <p>The second index is just for testing.
  Now When I run this query:</p>
  <pre><code>SELECT *
  FROM temp_learners
  WHERE &quot;companyId&quot; = 909666665757230431 AND &quot;userId&quot; IN (
                                                          4990609084216745771,
                                                          4990610022492247987,
                                                          4990609742667096366,
                                                          4990609476136523663,
                                                          5451985767018841230,
                                                          5451985767078553638,
                                                          5270390122102920730,
                                                          4763688819142650938,
                                                          5056979692501246449,
                                                          5279569274741647114,
                                                          5031660827132289520,
                                                          4862889373349389098,
                                                          5299864070077160421,
                                                          4740222596778406913,
                                                          5320170488686569878,
                                                          5270367618320474818,
                                                          5320170488587895729,
                                                          5228888485293847415,
                                                          4778050469432720821,
                                                          5270392314970177842,
                                                          4849087862439244546,
                                                          5270392117430427860,
                                                          5270351184072717902,
                                                          5330263074228870897,
                                                          4763688829301614114,
                                                          4763684609695916489,
                                                          5270390232949727716
    ) ORDER BY &quot;learnerUserName&quot;,&quot;learnerEmailId&quot;;
  </code></pre>
  <p>The query plan used by db is this:</p>
  <pre><code>Sort  (cost=138.75..138.76 rows=4 width=1581) (actual time=0.169..0.171 rows=27 loops=1)
  &quot;  Sort Key: &quot;&quot;learnerUserName&quot;&quot;, &quot;&quot;learnerEmailId&quot;&quot;&quot;
    Sort Method: quicksort  Memory: 73kB
    -&gt;  Index Scan using &quot;temp_learners_companyId_userId_idx&quot; on temp_learners  (cost=0.55..138.71 rows=4 width=1581) (actual time=0.018..0.112 rows=27 loops=1)
  &quot;        Index Cond: ((&quot;&quot;companyId&quot;&quot; = '909666665757230431'::bigint) AND (&quot;&quot;userId&quot;&quot; = ANY ('{4990609084216745771,4990610022492247987,4990609742667096366,4990609476136523663,5451985767018841230,5451985767078553638,5270390122102920730,4763688819142650938,5056979692501246449,5279569274741647114,5031660827132289520,4862889373349389098,5299864070077160421,4740222596778406913,5320170488686569878,5270367618320474818,5320170488587895729,5228888485293847415,4778050469432720821,5270392314970177842,4849087862439244546,5270392117430427860,5270351184072717902,5330263074228870897,4763688829301614114,4763684609695916489,5270390232949727716}'::bigint[])))&quot;
  Planning time: 0.116 ms
  Execution time: 0.191 ms
  </code></pre>
  <p>In this it does not sort on indexs.
  But when I run this query</p>
  <pre><code>SELECT *
  FROM temp_learners
  WHERE &quot;companyId&quot; = 909666665757230431
     ORDER BY &quot;learnerUserName&quot;,&quot;learnerEmailId&quot; limit 500;
  </code></pre>
  <p>This query uses indexs on sorting.</p>
  <pre><code>Limit  (cost=0.42..1360.05 rows=500 width=1581) (actual time=0.018..0.477 rows=500 loops=1)
    -&gt;  Index Scan using temp_learners_company_name_email_index on temp_learners  (cost=0.42..332639.30 rows=122327 width=1581) (actual time=0.018..0.442 rows=500 loops=1)
          Index Cond: (&quot;companyId&quot; = '909666665757230431'::bigint)
  Planning time: 0.093 ms
  Execution time: 0.513 ms
  </code></pre>
  <p>What I am not able to understand is why postgre does not uses index in first query? Also, I want to clear out that the normal use case of this table <code>learner</code> is to join with other tables. So the first query I written is more similar to joins equation. So for example,</p>
  <pre><code>SELECT *
  FROM temp_learners AS l
  INNER JOIN entity_learners_basic AS elb
  ON l.&quot;companyId&quot; = elb.&quot;companyId&quot; AND l.&quot;userId&quot; = elb.&quot;userId&quot;
  WHERE l.&quot;companyId&quot; = 909666665757230431 AND elb.&quot;gameId&quot; = 1050403501267716928
  ORDER BY &quot;learnerUserName&quot;, &quot;learnerEmailId&quot; limit 5000;
  </code></pre>
  <p>Even after correcting indexes the query plan does not indexes for sorting.</p>
  <pre><code>QUERY PLAN
  Limit  (cost=3785.11..3785.22 rows=44 width=1767) (actual time=163.554..173.135 rows=5000 loops=1)
    -&gt;  Sort  (cost=3785.11..3785.22 rows=44 width=1767) (actual time=163.553..172.791 rows=5000 loops=1)
  &quot;        Sort Key: l.&quot;&quot;learnerUserName&quot;&quot;, l.&quot;&quot;learnerEmailId&quot;&quot;&quot;
          Sort Method: external merge  Disk: 35416kB
          -&gt;  Nested Loop  (cost=1.12..3783.91 rows=44 width=1767) (actual time=0.019..63.743 rows=21195 loops=1)
                -&gt;  Index Scan using primary_index__entity_learners_basic on entity_learners_basic elb  (cost=0.57..1109.79 rows=314 width=186) (actual time=0.010..6.221 rows=21195 loops=1)
                      Index Cond: ((&quot;companyId&quot; = '909666665757230431'::bigint) AND (&quot;gameId&quot; = '1050403501267716928'::bigint))
                -&gt;  Index Scan using &quot;temp_learners_companyId_userId_idx&quot; on temp_learners l  (cost=0.55..8.51 rows=1 width=1581) (actual time=0.002..0.002 rows=1 loops=21195)
                      Index Cond: ((&quot;companyId&quot; = '909666665757230431'::bigint) AND (&quot;userId&quot; = elb.&quot;userId&quot;))
  Planning time: 0.309 ms
  Execution time: 178.422 ms
  
  </code></pre>
  <p>Does Postgres not use indexes when joining and ordering data?</p>