Re: Slow Query - Mailing list pgsql-performance
| From | Venkata Balaji N |
|---|---|
| Subject | Re: Slow Query |
| Date | |
| Msg-id | CAEyp7J9SCCpZEbR0y6Xq-rq5yVALAnaOF=aJzOqv=Vj4C8vxYQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Slow Query (robbyc <robcampbell73@gmail.com>) |
| List | pgsql-performance |
On Wed, Aug 12, 2015 at 3:29 PM, robbyc <robcampbell73@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]> wrote:On Wed, Aug 12, 2015 at 12:34 PM, robbyc <[hidden email]> 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 BalajiFujitsu AustraliaIf you reply to this email, your message will be added to the discussion below:http://postgresql.nabble.com/Slow-Query-tp5861835p5861839.html
--
View this message in context: Re: Slow Query
pgsql-performance by date: