Date: Tue, 11 Aug 2015 19:34:20 -0700 (MST)
From: robbyc <robcampbell73@gmail.com>
To: pgsql-performance@postgresql.org
Message-ID: <1439346860122-5861835.post@n5.nabble.com>
Subject: Slow Query
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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.

Thanks in advance  


--
View this message in context: http://postgresql.nabble.com/Slow-Query-tp5861835.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance