Speed up SELECT with WINDOW, (PARTITION ... ORDER) with compound index I have the following table:

CREATE TABLE ranking_points  (
  id serial NOT NULL,
  store_id integer,
  book_id integer,
  list_name character varying(255),
  rank integer,
  created_at timestamp without time zone,
  updated_at timestamp without time zone,
  storewide boolean,
  yesterday_delta integer,
  best_ever_rank integer,
  best_ever_date date,
  CONSTRAINT ranking_points_pkey PRIMARY KEY (id)
);

CREATE INDEX index_ranking_points_on_book_id_and_created_at
  ON ranking_points (book_id, created_at);

CREATE INDEX index_ranking_points_on_book_id_list_name_and_created_at_desc
  ON ranking_points (book_id, list_name, created_at DESC);

CREATE INDEX index_ranking_points_on_store_id_and_created_at
  ON ranking_points (store_id, created_at);`


I am executing the following SELECT statement:

select distinct book_id, list_name, 
                first_value(created_at) over w as created_at, 
                first_value(rank) over w as rank, 
                first_value(id) over w as id
from ranking_points where book_id in (61,62,63)
window w as (partition by book_id, list_name order by 3 desc)


I would expect Postgres to use the index_ranking_points_on_book_id_list_name_and_created_at_desc index since it perfectly matches the partition by and order by, but instead it uses the index_ranking_points_on_book_id_and_created_at index. Here is the explain analyze:

HashAggregate  (cost=895.70..901.41 rows=571 width=42) (actual time=0.740..0.740 rows=0 loops=1)
  -&gt;  WindowAgg  (cost=874.28..888.56 rows=571 width=42) (actual time=0.737..0.737 rows=0 loops=1)
        -&gt;  Sort  (cost=874.28..875.71 rows=571 width=42) (actual time=0.737..0.737 rows=0 loops=1)
              Sort Key: book_id, list_name
              Sort Method: quicksort  Memory: 25kB
              -&gt;  Bitmap Heap Scan on ranking_points  (cost=17.25..848.14 rows=571 width=42) (actual time=0.728..0.728 rows=0 loops=1)
                    Recheck Cond: (book_id = ANY ('{61,62,63}'::integer[]))
                    -&gt;  Bitmap Index Scan on index_ranking_points_on_book_id_and_created_at  (cost=0.00..17.11 rows=571 width=0) (actual time=0.726..0.726 rows=0 loops=1)
                          Index Cond: (book_id = ANY ('{61,62,63}'::integer[]))
Total runtime: 0.811 ms


You can see that what I'm trying to do is to build a result set consisting of the grouping values and the first_value of several fields. My hope was that Postgres could do all of the selection using only the index and then read one record from each group to obtain the first_value values. Is that achievable? I have other solutions, but they generate a sequential scan of the table, so that's not very good.
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