Re: Index help

A Gilmore <agilmore(at)shaw(dot)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