Why is my composite index not used for this table?

Question

I am working with a fairly complicated schema, but I've simplified it for the purposes of this question.

                Table "public.scheduler_appointment"
       Column        |           Type           |              Modifiers
---------------------+--------------------------+---------------------------------
 id                  | integer                  | not null default nextval(...)
 provider_id         | integer                  |
 title               | character varying(200)   | not null
 startTime           | timestamp with time zone | not null
 endTime             | timestamp with time zone | not null
 ... more columns ...
Indexes:
    "scheduler_appointment_pkey" PRIMARY KEY, btree (id)
    "scheduler_appointment_provider_id" btree ("provider_id")
    "scheduler_appointment_provider_id_68f646d601089728" btree ("provider_id", "startTime", "endTime")
    ... more indexes ...
Foreign-key constraints:
    "provider_id_refs_id_a536838c" FOREIGN KEY ("provider_id") REFERENCES scheduler_provider(id) DEFERRABLE INITIALLY DEFERRED
    ... more fks ...

I'm doing a complex query that includes a few joins, and slightly more complicated logic, but an EXPLAIN on that query revealed that 80% of the time was spend doing a Seq Scan on this table.

I read this article which suggested indexing on the column that will be equality tested first, then on columns that will be range tested. After adding a composite index on ("provider_id", "startTime", "endTime"), I noticed that the query plan did not change at all, and it was still doing a seq scan.

So I wrote the following smaller query to try and isolate the part of the query that should use an index.

EXPLAIN ANALYZE
SELECT * FROM scheduler_appointment
WHERE (
    "provider_id" = 14
    AND "startTime" >= '2014-07-19 14:30:00+00:00'
    AND "endTime" <= '2014-07-26 14:30:00+00:00'
)
ORDER BY "startTime" ASC;

However, this still does not use the index like I would expect.

 Sort  (cost=36.02..36.52 rows=201 width=143) (actual time=0.477..0.483 rows=57 loops=1)
   Sort Key: "startTime"
   Sort Method: quicksort  Memory: 33kB
   ->  Seq Scan on scheduler_appointment  (cost=0.00..28.33 rows=201 width=143) (actual time=0.050..0.428 rows=57 loops=1)
         Filter: (("startTime" >= '2014-07-20 00:00:00+09:30'::timestamp with time zone) AND ("endTime" <= '2014-07-27 00:00:00+09:30'::timestamp with time zone) AND ("provider_id" = 14))
 Total runtime: 0.538 ms

The first thing I noticed was that it's rewritten my filter to put the provider_id at the end. Is this the reason that it refuses to use the index, or is it simply that I don't yet have enough data in the table to make an index scan worthwhile? How would I go about testing this, or forcing it to use an index if available?

Answer 1

As @dezso suggested, the table with test data was not big enough for the query planner bother going to the index. After I imported a larger set of data, the query uses the index as expected.