Index help

From: A Gilmore <agilmore(at)shaw(dot)ca>
To: Postgresql Mailing list <pgsql-novice(at)postgresql(dot)org>
Subject: Index help
Date: 2005-07-27 18:18:26
Message-ID: 42E7CFF2.9090903@shaw.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message operationsengineer1 2005-07-27 18:47:55 Re: Index help
Previous Message Vladimir D Belousov 2005-07-27 14:48:35 Troubles with PL/Perl in PgSQL