Re: Index help

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: A Gilmore <agilmore(at)shaw(dot)ca>
Cc: Postgresql Mailing list <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Index help
Date: 2005-07-28 05:22:32
Message-ID: 20453.1122528152@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

In response to

  • Index help at 2005-07-27 18:18:26 from A Gilmore

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message A Gilmore 2005-07-28 06:09:13 Re: Index help
Previous Message Michael Glaesemann 2005-07-28 03:23:47 Re: SQL function