Re: query taking much longer since Postgres 8.4 upgrade

Dne 16.3.2011 20:32, Merlin Moncure napsal(a):
> 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.

That probably is not the root cause here, according to a description
I've received off the list. There are no indexes at all (which is an
issue on it's own), but it means this kind of issue is not possible.

Or maybe there's a different lc_collate with much more complex rules?
That might result in very CPU-expensive operations, and as there's a lot
of sequential scans etc. (i.e. a lot of rows to process).

regards
Tomas