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 *************************