Re: query taking much longer since Postgres 8.4 upgrade

Try this :

1) rewrite your query as is :

select course_id AS EXTERNAL_COURSE_KEY,
user_id AS EXTERNAL_PERSON_KEY,
'Student' AS ROLE,
'Y' AS AVAILABLE_IND
from course_user_link AS CUL
INNER JOIN course_control AS CC
ON CUL.course_id = CC.course_id
where CUL.instructor = false
AND CC.course_begin_date::date IN (
'20100412','20100510','20100607','20100712','20100830','20100927','20101025','20101122','20101213','20110110','20110207','20110307'
)
and CC.course_delivery LIKE 'O%'
and CC.course_cross_section IS NULL
and NOT EXISTS(SELECT *
FROM instr_as_stutemp AS IAS
WHERE C.user_id = IAS.user_id)

2) prefix all tables by your SQL schema (public by default)

3) create theses indexes (if not) :

CREATE INDEX X_CUL_INS_CRS_UID
ON course_user_link (instructor,
course_id,
user_id);
CREATE INDEX X_CC_CDV_CCS_CBD_CID
ON course_control (course_delivery,
course_cross_section,
course_begin_date,
course_id);
CREATE INDEX X_IAS ON IAS_UID
ON instr_as_stutemp (user_id);

4) beware of using reserved words for the name of a database object like
ROLE !

A +

Le 16/03/2011 16:49, Davenport, Julie a écrit :
> select
>
> course_id AS EXTERNAL_COURSE_KEY,
>
> user_id AS EXTERNAL_PERSON_KEY,
>
> 'Student' AS ROLE,
>
> 'Y' AS AVAILABLE_IND
>
> from course_user_link
>
> where instructor = false
>
> and course_id in
>
> (
>
> select course_id
>
> from course_control
>
> where to_char(course_begin_date,'YYYYMMDD') IN (
> '20100412','20100510','20100607','20100712','20100830','20100927','20101025','20101122','20101213','20110110','20110207','20110307'
> )
>
> and course_delivery LIKE 'O%'
>
> and course_cross_section IS NULL
>
> )
>
> and user_id not in (select user_id from instr_as_stutemp)
>

--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************