Re: query taking much longer since Postgres 8.4 upgrade

Output of explain (and as likely, explain analyze) for this would be
helpful.

A

On Wed, Mar 16, 2011 at 10:49:24AM -0500, Davenport, Julie wrote:
> When I run the following query in Postgres 8.0, it runs in 61,509.372 ms
>
> When I run it in Postgres 8.4, it runs in 397,857.472 ms
>
> Here is the query:
>
> 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)
>
> (table instr_as_stutemp has just one column and only 4 rows)
>
> What new feature of Postgres 8.4 would be making the query run so much more slowly?  Is there a better way to rewrite
thequery for 8.4 to make it run faster? 
>
> Many thanks,
> Julie

--
Andrew Sullivan
ajs@crankycanuck.ca