On Wed, Mar 16, 2011 at 2:14 PM, Davenport, Julie <JDavenport@xxxxxxxx> 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.davenport@xxxxxxxx > > > > > -----Original Message----- > From: Merlin Moncure [mailto:mmoncure@xxxxxxxxx] > Sent: Wednesday, March 16, 2011 1:37 PM > To: Davenport, Julie > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: query taking much longer since Postgres 8.4 upgrade > > On Wed, Mar 16, 2011 at 10:49 AM, Davenport, Julie <JDavenport@xxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
