Nicolas Le Manchet

PostgreSQL index not being used

I was trying to speed up a query by adding a composite index to a table:

CREATE INDEX ON reader_article (feed_id, published_at);

I was sure that the index should be able to improve things but somehow I didn't notice any difference after creating it. Running EXPLAIN ANALYZE indeed showed that another index was used, but not the newly created one:

EXPLAIN ANALYZE SELECT COUNT(*) AS "__count" FROM "reader_article" WHERE ("reader_article"."feed_id" = 1890 AND "reader_article"."published_at" > '2018-07-07T16:08:09.562165+00:00'::timestamptz);
Aggregate  (cost=25857.39..25857.40 rows=1 width=8) (actual time=3444.532..3444.532 rows=1 loops=1)
    ->  Index Scan using reader_article_feed_id_a710e3e1 on reader_article  (cost=0.43..25790.23 rows=26865 width=0) (actual time=1.216..3436.333 rows=26985 loops=1)
          Index Cond: (feed_id = 1890)
          Filter: (published_at > '2018-07-07 16:08:09.562165+00'::timestamp with time zone)
Planning Time: 0.476 ms
Execution Time: 3444.624 ms

It turns out PostgreSQL needs to update its statistics to better know the cost associated with using (or not) each index. This is done with the help of VACUUM ANALYZE:

VACUUM ANALYZE reader_article;

Sure enough, once the stats are updated the query runs in 14 ms using the composite index.