Index help - Mailing list pgsql-novice

Hello,

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.  Switching off seqscans will have it do a
index scan (but not the multicolumn index) but its more costly then the
seq scan.  Below is the explain analyze output :

---
With seq scan on :

Hash Left Join  (cost=3.13..123.32 rows=742 width=42) (actual
time=2.572..64.782 rows=742 loops=1)
    Hash Cond: ("outer".id = "inner".appt_id)
    ->  Hash Left Join  (cost=1.55..117.89 rows=742 width=26) (actual
time=0.964..46.467 rows=742 loops=1)
          Hash Cond: ("outer".cal_id = "inner".cal_id)
          ->  Seq Scan on appointments  (cost=0.00..109.62 rows=742
width=24) (actual time=0.365..32.246 rows=742 loops=1)
                Filter: ((cal_id = 82) AND (start_date <= '2005-12-31
00:00:00+00'::timestamp with time zone) AND (start_date >= '2004-01-01
00:00:00+00'::timestamp with time zone) AND (modified >= '2005-01-01
00:00:00+00'::timestamp with time zone))
          ->  Hash  (cost=1.55..1.55 rows=2 width=6) (actual
time=0.426..0.426 rows=0 loops=1)
                ->  Seq Scan on calendars_permission  (cost=0.00..1.55
rows=2 width=6) (actual time=0.153..0.396 rows=3 loops=1)
                      Filter: (user_id = 1)
    ->  Hash  (cost=1.46..1.46 rows=46 width=20) (actual
time=1.440..1.440 rows=0 loops=1)
          ->  Seq Scan on recur  (cost=0.00..1.46 rows=46 width=20)
(actual time=0.100..1.131 rows=46 loops=1)
  Total runtime: 68.321 ms

---
With seq scan off :

Hash Left Join  (cost=9.51..166.96 rows=742 width=42) (actual
time=11.049..162.821 rows=742 loops=1)
    Hash Cond: ("outer".id = "inner".appt_id)
    ->  Hash Left Join  (cost=4.99..158.60 rows=742 width=26) (actual
time=3.806..131.755 rows=742 loops=1)
          Hash Cond: ("outer".cal_id = "inner".cal_id)
          ->  Index Scan using appointments_id_index on appointments
(cost=0.00..146.89 rows=742 width=24) (actual time=1.925..109.835
rows=742 loops=1)
                Filter: ((cal_id = 82) AND (start_date <= '2005-12-31
00:00:00+00'::timestamp with time zone) AND (start_date >= '2004-01-01
00:00:00+00'::timestamp with time zone) AND (modified >= '2005-01-01
00:00:00+00'::timestamp with time zone))
          ->  Hash  (cost=4.99..4.99 rows=2 width=6) (actual
time=1.585..1.585 rows=0 loops=1)
                ->  Index Scan using calendars_permission_userid_index
on saa_calendars_permission  (cost=0.00..4.99 rows=2 width=6) (actual
time=1.333..1.485 rows=3 loops=1)
                      Index Cond: (user_id = 1)
    ->  Hash  (cost=4.40..4.40 rows=46 width=20) (actual
time=4.138..4.138 rows=0 loops=1)
          ->  Index Scan using recur_apptid_index on saa_recur
(cost=0.00..4.40 rows=46 width=20) (actual time=1.208..3.565 rows=46
loops=1)
  Total runtime: 168.144 ms


If someone could provide some insight into speeding this query up I
would greatly appreciate it.

Thank you,
-A Gilmore