Re: query taking much longer since Postgres 8.4 upgrade

On Wed, Mar 16, 2011 at 2:14 PM, Davenport, Julie <JDavenport(at)ctcd(dot)edu> wrote:
> Hello Merlin,
> Thank you very much for your reply.
> I don't see any setting for lc_collate.  I assume it would be in postgresql.conf file if it were there?  These are the only lc_... settings I see in postgresql.conf:
>
> lc_messages = 'en_US.UTF-8'     # locale for system error message
> lc_monetary = 'en_US.UTF-8'     # locale for monetary formatting
> lc_numeric = 'en_US.UTF-8'     # locale for number formatting
> lc_time = 'en_US.UTF-8'        # locale for time formatting
>
> Am I looking in the wrong place?  Thanks much,
> Julie
>
>
> Julie A. Davenport
> julie(dot)davenport(at)ctcd(dot)edu
>
>
>
>
> -----Original Message-----
> From: Merlin Moncure [mailto:mmoncure(at)gmail(dot)com]
> Sent: Wednesday, March 16, 2011 1:37 PM
> To: Davenport, Julie
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] 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?

from psql, do:
show lc_collate;
more than likely, your lc_collate is set to UTF8, that means that
where a like 'foo%' will not use index, which is starting to sound
like your problem.

unfortunately, database collation is only settable when database is
created (or more typically with initdb).

merlin