Re: query taking much longer since Postgres 8.4 upgrade - Mailing list pgsql-general

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
firstschema 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
indexesdo 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,
whilethe 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
asidentifiers (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@postgresql.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!