Query optimization

From: "Ryan Riehle" <rkr(at)buildways(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Query optimization
Date: 2005-03-18 20:23:05
Message-ID: 000001c52bf8$4f050520$6901a8c0@buildways
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Keith Worthington 2005-03-18 21:41:21 output a single and double quote in a string
Previous Message Tom Lane 2005-03-18 19:44:28 Re: Multidimensional arrays in plpgsql