On Wed, Mar 16, 2011 at 2:14 PM, Davenport, Julie <JDavenport@ctcd.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
theonly 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.davenport@ctcd.edu
>
>
>
>
> -----Original Message-----
> From: Merlin Moncure [mailto:mmoncure@gmail.com]
> Sent: Wednesday, March 16, 2011 1:37 PM
> To: Davenport, Julie
> Cc: pgsql-general@postgresql.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@ctcd.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