Query optimization - Mailing list pgsql-novice

From Ryan Riehle
Subject Query optimization
Date
Msg-id 000001c52bf8$4f050520$6901a8c0@buildways
Whole thread Raw
Responses Re: Query optimization
List pgsql-novice
I have this query that is taking way too long for what I want to do:
 
explain analyze select cs.contractcode from contractservices cs left join serviceevents se
    on se.businessserviceid = cs.businessserviceid and se.contractcode = cs.contractcode
where cs.businessserviceid = 167
group by cs.contractcode, cs.businessserviceid having count(se.businessserviceid) = 0;
 
the results of the explain analyze are:
 
"HashAggregate  (cost=24094.79..24095.29 rows=101 width=22) (actual time=14501.476..14502.124 rows=143 loops=1)"
"  Filter: (count(businessserviceid) = 0)"
"  ->  Merge Left Join  (cost=22983.55..24093.64 rows=153 width=22) (actual time=14454.046..14481.790 rows=1482 loops=1)"
"        Merge Cond: (("outer".businessserviceid = "inner".businessserviceid) AND ("outer"."?column3?" = "inner"."?column3?"))"
"        ->  Sort  (cost=432.97..433.23 rows=101 width=18) (actual time=57.763..57.879 rows=160 loops=1)"
"              Sort Key: cs.businessserviceid, (cs.contractcode)::text"
"              ->  Seq Scan on contractservices cs  (cost=0.00..429.61 rows=101 width=18) (actual time=46.401..54.840 rows=160 loops=1)"
"                    Filter: (businessserviceid = 167)"
"        ->  Sort  (cost=22550.58..22919.85 rows=147708 width=19) (actual time=13875.680..14140.073 rows=146205 loops=1)"
"              Sort Key: se.businessserviceid, (se.contractcode)::text"
"              ->  Seq Scan on serviceevents se  (cost=0.00..6098.08 rows=147708 width=19) (actual time=0.108..1056.808 rows=147708 loops=1)"
"Total runtime: 14512.805 ms"
I want to optimize this query, but I'm new to the process and can use some input.  Do you see any red flags here?  If so what might I try to improve the query?
 
  -RYAN
Attachment

pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: Multidimensional arrays in plpgsql
Next
From: "Keith Worthington"
Date:
Subject: output a single and double quote in a string