Re: query taking much longer since Postgres 8.4 upgrade

On 22 Mar 2011, at 24:20, F. BROUARD / SQLpro wrote:

> Try this :
>
> 1) rewrite your query as is :

Indeed, a join is probably more efficient than a big IN-list. Good point ;)

>
> select course_id AS EXTERNAL_COURSE_KEY,
> user_id AS EXTERNAL_PERSON_KEY,
> 'Student' AS ROLE,
> 'Y' AS AVAILABLE_IND
> from course_user_link AS CUL
> INNER JOIN course_control AS CC
> ON CUL.course_id = CC.course_id
> where CUL.instructor = false
> AND CC.course_begin_date::date IN ( '20100412','20100510','20100607','20100712','20100830','20100927','20101025','20101122','20101213','20110110','20110207','20110307' )
> and CC.course_delivery LIKE 'O%'
> and CC.course_cross_section IS NULL

> and NOT EXISTS(SELECT *
> FROM instr_as_stutemp AS IAS
> WHERE C.user_id = IAS.user_id)

You could write this last condition as a LEFT OUTER JOIN even, where valid records match IAS.user_id IS NULL.

> 2) prefix all tables by your SQL schema (public by default)

I don't think that will matter much, it might shave off a tiny bit of planner execution time if tables aren't in the first schema in the search_path, but otherwise not worth the hassle.

> 3) create theses indexes (if not) :

I think the usual convention is to suffix with _idx instead of prefixing with x_. It's what automatically created indexes do anyway. That's a matter of personal preference though.

> CREATE INDEX X_CUL_INS_CRS_UID
> ON course_user_link (instructor,
> course_id,
> user_id);
> CREATE INDEX X_CC_CDV_CCS_CBD_CID
> ON course_control (course_delivery,
> course_cross_section,
> course_begin_date,
> course_id);

If queries where cross_section IS NULL (especially in combination with the other fields in this index) are very common, while the opposite is quite rare, you may want to add a WHERE-clause with that condition to this index.

> CREATE INDEX X_IAS ON IAS_UID
> ON instr_as_stutemp (user_id);

> 4) beware of using reserved words for the name of a database object like ROLE !

Good advise, but not really needed in the case of aliases I think. There's also the possibility to quote those fields as identifiers (which also makes them case-sensitive, so beware!) - in this case that would be "ROLE".

> Le 16/03/2011 16:49, Davenport, Julie a écrit :
>> 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)
>>
>
>
> --
> Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> *********************** http://www.sqlspot.com *************************
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4d8852ad651346607679948!