Re: query taking much longer since Postgres 8.4 upgrade

On Wed, Mar 16, 2011 at 10:49 AM, Davenport, Julie <JDavenport(at)ctcd(dot)edu> 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?

another common problem following upgrades are locale issues -- what is
your setting for lc_collate?

merlin