Re: Index help

A Gilmore <agilmore@shaw.ca> writes:
> I have a query that looks similiar to this :

> SELECT appointments.id,
>     recur.id AS recur_id,
>     recur.limitType,
>     recur.limitDate,
>     calendars_permission.perm_read,
>     calendars_permission.perm_write
> FROM appointments LEFT JOIN calendars_permission
>     ON appointments.cal_id = calendars_permission.cal_id
>     AND calendars_permission.user_id = '1'
>     LEFT JOIN recur
>     ON appointments.id = recur.appt_id
> WHERE appointments.cal_id in ('82')
>     AND appointments.start_date <= '2005-12-31'
>     AND appointments.start_date >= '2004-01-01'
>     AND appointments.modified >= '2005-01-01';

> This query is run a lot so Id like to make it as fast as possible.  I
> believe my problem is that its always doing a seq scan of the
> appointments table, Ive tried creating multicolumn indexes and such but
> it still does a seq scan.

Uh, what multicolumn indexes did you try, exactly?

If this is the standard form of the query, I'd think that an index on
(cal_id, start_date, modified) --- in that order --- would be a good
bet.  It's also possible that indexing only (cal_id, start_date), or
even just (cal_id), would be the winner.  With no info about the
statistics of your database, it's hard to tell which.

            regards, tom lane