Speed up SELECT with WINDOW, (PARTITION ... ORDER) with compound index
The answer is that Postgres does use the index, if you have enough records. This was against my development database, which is relatively empty. In production without the index, I get this from EXPLAIN ANALYZE:

HashAggregate  (cost=186849.60..187449.78 rows=200061 width=48) (actual time=129567.598..129568.987 rows=1903 loops=1)
  -&gt;  WindowAgg  (cost=185149.08..186349.45 rows=200061 width=48) (actual time=129182.770..129460.868 rows=186023 loops=1)
        -&gt;  Sort  (cost=185149.08..185249.11 rows=200061 width=48) (actual time=129181.497..129228.725 rows=186023 loops=1)
              Sort Key: book_id, list_name
              Sort Method: quicksort  Memory: 25441kB
              -&gt;  Bitmap Heap Scan on ranking_points  (cost=3813.71..181625.99 rows=200061 width=48) (actual time=67.821..128210.717 rows=186023 loops=1)
                    Recheck Cond: (book_id = ANY ('{61,62,63,64,66,70,78,270,301,298,398,402,414,15485,15416,2767,6922,6920,6974,18002,9122,15444,65,1774,4939,18331,22828,22209,19841,22402,22841,18232}'::integer[]))
                    -&gt;  Bitmap Index Scan on index_ranking_points_on_book_id_and_created_at  (cost=0.00..3803.70 rows=200061 width=0) (actual time=55.568..55.568 rows=186023 loops=1)
                          Index Cond: (book_id = ANY ('{61,62,63,64,66,70,78,270,301,298,398,402,414,15485,15416,2767,6922,6920,6974,18002,9122,15444,65,1774,4939,18331,22828,22209,19841,22402,22841,18232}'::integer[]))
Total runtime: 129573.021 ms


With the index, I get this:

HashAggregate  (cost=193464.14..194108.37 rows=214744 width=48) (actual time=1446.685..1447.571 rows=1903 loops=1)
  -&gt;  WindowAgg  (cost=191638.82..192927.28 rows=214744 width=48) (actual time=1091.394..1349.506 rows=186027 loops=1)
        -&gt;  Sort  (cost=191638.82..191746.19 rows=214744 width=48) (actual time=1091.381..1136.297 rows=186027 loops=1)
              Sort Key: book_id, list_name
              Sort Method: quicksort  Memory: 25441kB
              -&gt;  Bitmap Heap Scan on ranking_points  (cost=3772.47..187835.22 rows=214744 width=48) (actual time=58.064..221.151 rows=186027 loops=1)
                    Recheck Cond: (book_id = ANY ('{61,62,63,64,66,70,78,270,301,298,398,402,414,15485,15416,2767,6922,6920,6974,18002,9122,15444,65,1774,4939,18331,22828,22209,19841,22402,22841,18232}'::integer[]))
                    -&gt;  Bitmap Index Scan on index_ranking_points_on_book_id_list_name_and_created_at_desc  (cost=0.00..3761.74 rows=214744 width=0) (actual time=44.349..44.349 rows=186027 loops=1)
                          Index Cond: (book_id = ANY ('{61,62,63,64,66,70,78,270,301,298,398,402,414,15485,15416,2767,6922,6920,6974,18002,9122,15444,65,1774,4939,18331,22828,22209,19841,22402,22841,18232}'::integer[]))
Total runtime: 1450.444 ms