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
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 |