On Wed, Aug 12, 2015 at 3:29 PM, robbyc <robcampbel...@gmail.com> wrote:
> Hi Venkata, > > work_mem was set to 72MB, increased to 144MB, no change. > Increasing work_mem depends on various other factors like Table size (amount of data being sorted), available memory etc. > Added an index of type varchar_pattern_ops to Vacancy.JobTitle, this did > not help either. > Sorry, I did not mean to say that an Index must be added straight away. The column must be eligible to have an Index. Meaning, Index will be beneficial if created on a column with high number of distinct values. If either of the above does not help, then options to rewrite the query must be explored. Thanks, Venkata Balaji N Fujitsu Australia > > On Wed, Aug 12, 2015 at 2:09 PM, Venkata Balaji N [via PostgreSQL] <[hidden > email] <http:///user/SendEmail.jtp?type=node&node=5861850&i=0>> 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. >> 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 > [hidden email] <http:///user/SendEmail.jtp?type=node&node=5861850&i=1> > > ------------------------------ > View this message in context: Re: Slow Query > <http://postgresql.nabble.com/Slow-Query-tp5861835p5861850.html> > > Sent from the PostgreSQL - performance mailing list archive > <http://postgresql.nabble.com/PostgreSQL-performance-f2050081.html> at > Nabble.com. >