Hi Venkata, work_mem was set to 72MB, increased to 144MB, no change.
Added an index of type varchar_pattern_ops to Vacancy.JobTitle, this did not help either. On Wed, Aug 12, 2015 at 2:09 PM, Venkata Balaji N [via PostgreSQL] < ml-node+s1045698n5861839...@n5.nabble.com> wrote: > On Wed, Aug 12, 2015 at 12:34 PM, robbyc <[hidden email] > <http:///user/SendEmail.jtp?type=node&node=5861839&i=0>> wrote: > >> Hi, >> >> I am new to optimizing queries and i'm getting a slow running time >> (~1.5secs) with the following SQL: >> >> SELECT distinct("Vacancy"."ID"), "Vacancy"."JobTitle", >> "Vacancy"."DateCreated", "Vacancy"."CustomAccess" >> , "Department"."Name" as "Department", list("Occupation"."Name") as >> "Occupation", "Vacancy"."PositionNo" >> , "Vacancy"."Template" from >> "Vacancy" >> LEFT JOIN "CategoryOption_TableRow" as "c_22" >> ON ("c_22"."RowID" = "Vacancy"."ID" >> and "c_22"."Category_TableID" = 22) >> LEFT JOIN "CategoryOption" as "Occupation" >> ON ("Occupation"."ID" = "c_22"."CategoryOptionID") >> LEFT JOIN "TableRow_TableRow" as "t_33" >> ON ("t_33"."Table1RowID" = "Vacancy"."ID" >> and "t_33"."Table_TableID" = 33 ) >> LEFT JOIN "Department" >> ON ("Department"."ID" = "t_33"."Table2RowID" and >> "Department"."Active" = 't' and "Department" >> ."ClientID" = 263) >> JOIN "CategoryOption_TableRow" as "c_50" ON ("c_50"."Category_TableID"= 50 >> and "c_50"."RowID" = "Vacancy" >> ."ID" and "c_50"."CategoryOptionID"=19205) >> WHERE "Vacancy"."ClientID" = 263 AND "Vacancy"."ID" NOT IN(SELECT >> DISTINCT("Vacancy"."ID") >> FROM "Vacancy" join "CategoryOption_TableRow" "ct126" on >> ("ct126"."Category_TableID" = 126 >> and "RowID" = "Vacancy"."ID") >> left join "Workflow" on ("Workflow"."VacancyID" = >> "Vacancy"."ID" >> and "Workflow"."Level" >> = 1) >> left join "CategoryOption_TableRow" "c30" on ("c30"."RowID" = >> "Workflow"."ID" and "c30"."Category_TableID" >> = 30 and "c30"."CategoryOptionID" = 21923) >> WHERE "Template" AND "ct126"."CategoryOptionID" >> IN(34024,35254,35255,35256)) and "Vacancy" >> ."Template" = 't' >> GROUP BY "Vacancy"."ID", "Vacancy"."JobTitle", "Vacancy"."DateCreated", >> "Vacancy"."CustomAccess", "Department" >> ."Name", "Vacancy"."PositionNo", "Vacancy"."Template" >> UNION >> SELECT distinct("Vacancy"."ID"), "Vacancy"."JobTitle", >> "Vacancy"."DateCreated", "Vacancy"."CustomAccess" >> , "Department"."Name" as "Department", list("Occupation"."Name") as >> "Occupation", "Vacancy"."PositionNo" >> , "Vacancy"."Template" from >> "Vacancy" >> LEFT JOIN "CategoryOption_TableRow" as "c_22" >> ON ("c_22"."RowID" = "Vacancy"."ID" >> and "c_22"."Category_TableID" = 22) >> LEFT JOIN "CategoryOption" as "Occupation" >> ON ("Occupation"."ID" = "c_22"."CategoryOptionID") >> LEFT JOIN "TableRow_TableRow" as "t_33" >> ON ("t_33"."Table1RowID" = "Vacancy"."ID" >> and "t_33"."Table_TableID" = 33 ) >> LEFT JOIN "Department" >> ON ("Department"."ID" = "t_33"."Table2RowID" and >> "Department"."Active" = 't' and "Department" >> ."ClientID" = 263) >> JOIN "CategoryOption_TableRow" as "c_50" ON ("c_50"."Category_TableID"= 50 >> and "c_50"."RowID" = "Vacancy" >> ."ID" and "c_50"."CategoryOptionID"=19205) >> WHERE "Vacancy"."ClientID" = 263 AND "Vacancy"."ID" NOT IN(SELECT >> DISTINCT("Vacancy"."ID") >> FROM "Vacancy" join "CategoryOption_TableRow" "ct126" on >> ("ct126"."Category_TableID" = 126 >> and "RowID" = "Vacancy"."ID") >> left join "Workflow" on ("Workflow"."VacancyID" = >> "Vacancy"."ID" >> and "Workflow"."Level" >> = 1) >> left join "CategoryOption_TableRow" "c30" on ("c30"."RowID" = >> "Workflow"."ID" and "c30"."Category_TableID" >> = 30 and "c30"."CategoryOptionID" = 21923) >> WHERE "Template" AND "ct126"."CategoryOptionID" >> IN(34024,35254,35255,35256)) and "Vacancy" >> ."Template" <> 't' AND "Vacancy"."Level" = 1 >> GROUP BY "Vacancy"."ID", "Vacancy"."JobTitle", "Vacancy"."DateCreated", >> "Vacancy"."CustomAccess", "Department" >> ."Name", "Vacancy"."PositionNo", "Vacancy"."Template" >> ORDER BY "JobTitle" >> >> Running explain analyze gives me the following information: >> http://explain.depesz.com/s/pdC <http://explain.depesz.com/s/pdC> > > >> For a total runtime: 2877.157 ms >> >> If i remove the left joins on Department and TableRow_TableRow this >> reduces >> the run time by about a third. >> Additionally removing CategoryOption and CategoryOption_TableRow joins >> further reduces by a about a third. >> >> Given that i need both these joins for the information retrieved by them, >> what would be the best way to re-factor this query so it runs faster? >> >> Looking at the output of explain analyze the hash aggregates and sort seem >> to be the primary issue. >> > > The query has got a distinct and group-by/order-by clauses which seems to > be taking time. Without looking at much details of the query code and Table > size etc, did you try increasing the work_mem and then execute the query > and see if that helps ? This will reduce the on-disk IO for sorting. Also, > Vacancy.JobTitle seems to be a non-index column. > > Regards, > Venkata Balaji > > Fujitsu Australia > > > > ------------------------------ > If you reply to this email, your message will be added to the discussion > below: > http://postgresql.nabble.com/Slow-Query-tp5861835p5861839.html > To unsubscribe from Slow Query, click here > <http://postgresql.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5861835&code=cm9iY2FtcGJlbGw3M0BnbWFpbC5jb218NTg2MTgzNXwxOTc1MDc2ODM4> > . > NAML > <http://postgresql.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> > -- Regards Robert Campbell +61412062971 robcampbel...@gmail.com -- View this message in context: http://postgresql.nabble.com/Slow-Query-tp5861835p5861850.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
