Re: Index help

Tom Lane wrote:
> 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.
>

I tried a multicolumn on (cal_id, start_date, modified), and (cal_id,
start_date), and a single column for (cal_id), none are used.

The (cal_id) is a reference to another table, with about 30 rows. I use
the array method because in practice Ill often want to match several
cal_ids, this is setup in php ahead of the query.

The (appointments) table only contains about 2500 rows on the test db Im
working with. Typically the two (start_date)s WHERE clause are going to
be 3 months apart, and (modified) is a timestamp of row insert/update.
In the scenario Im most concerned about, the (modified) WHERE clause
will match everything, and probably should have been left out of my
example for clarity.

- A Gilmore