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 ...
    "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.

SELECT * FROM scheduler_appointment
    "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?

  • 1
    How many data in your table ? and how many data you your sql get ?
    – francs
    Jul 25, 2014 at 5:49
  • Half a millisecond doesn't seem to be slow though...
    – user1822
    Jul 25, 2014 at 6:02
  • @a_horse_with_no_name this is a contrived range, there's a lot more data in the table, and queries were taking up to 500ms for some views.
    – Thomas
    Jul 25, 2014 at 6:09
  • @Thomas the sequential scan runs through the whole table. As it is very fast, it would make no sense to go to the index. At least I guess this is what the planner is thinking :) depending on your DB version, probably changing SELECT * to the three indexed columns, you'd see the index being used.
    – dezso
    Jul 25, 2014 at 8:23
  • If the appointments do not overlap (i.e. a provider can never have 2 appointments that overlap), so this query always returns 1 row (or 0), you could probably get more efficiency by adding an EXCLUDE constraint and a gist index. Jul 25, 2014 at 9:05

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.