Re: Slow Query

On Wed, Aug 12, 2015 at 12:34 PM, robbyc <robcampbell73(at)gmail(dot)com> 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