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@xxxxxxxxxxxxxx) > 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! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
