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 the query for 8.4 to make it run faster?
>
> Many thanks,
> Julie

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca