Hello!
First off, I'm a real newbie at trying to read the output of explain
analyze.
I have several similar queries in my application that I've got
incorporated into views. When they run sub 300ms, the users don't
seem to mind. However, one of them (query is below along with some
relevant table information) is running about 800ms and my users are
starting to grumble.
I ran explain analyze on it (explain analyze results are
below). I noticed that the biggest chunk of time is being taken
by a Hash Join near the top of the output (I'm still not sure what the
indentation means and what the order means). If I look at the
estimate, it is comparable to several other hash join estimates in the
query; however, the actual cost in time is significantly higher than
those other hash joins. Is this significant?
I tried optimizing according to "SQL Tuning" by Tow, but this
actually seemed to slow things down. It also seemed that the
query optimizer in PostgreSQL reordered things on its own according to
its own plan anyway. Is this correct?
I'd appreciate any help I can get to try to get this query below
300ms.
Thanks!
Mark
The platform is a dual 2.2GHz Xeon 1.2GB RAM with mirrored drives
(raid 1) running Win2000 Pro. I run "vacuum analyze" every
night. The postgresql.conf is basically standard except that I've
opened it up to listen to the external network. Other changes:
max_connections = 100
shared_buffers = 10000
query (the person_id = 1 in the where clause is changed on a case by
case basis - depending upon who's running the query):
explain analyze
SELECT DISTINCT c.job_id, g.person_id,
c.job_no, b.deadline, c.name, bid_date(c.job_id) AS bid_date, c.miscq,
c.city, c.st, j.name AS eng, c.s_team AS salesteam,
CASE
WHEN c.file_loc = 0 THEN 'No Bid'::character
varying
WHEN c.file_loc = -1 THEN 'Bid Board'::character
varying
WHEN c.file_loc = -2 THEN 'Lost Job'::character
varying
WHEN c.file_loc = -3 THEN 'See Job Notes'::character
varying
WHEN c.file_loc < -3 OR c.file_loc IS NULL THEN ''::character
varying
WHEN h.initials IS NOT NULL THEN
h.initials
ELSE 'Unknown person'::character
varying
END AS file_loc,
COALESCE(c.city::text || COALESCE(', '::text || c.st::text, ''::text),
COALESCE(c.st, ''::character varying)::text) AS "location", c.file_loc
AS file_loc_id
FROM status a
LEFT JOIN
status_list b ON a.status_id = b.status_id AND b.active
LEFT JOIN job c ON c.job_id = b.job_id
LEFT JOIN
builder_list d ON c.job_id = d.job_id AND (d.won_heat OR d.won_vent OR
d.won_tc OR c.heat AND d.bid_heat AND d.won_heat IS NULL OR c.vent AND
d.bid_vent AND d.won_vent IS NULL OR c.tc AND d.bid_tc AND d.won_tc IS
NULL) AND d.role = 'C'::bpchar
LEFT JOIN company e ON
d.company_id = e.company_id
LEFT JOIN call_list f ON
e.company_id = f.company_id
LEFT JOIN person g ON
f.person_id = g.person_id OR "position"(c.s_team::text,
g.initials::text) > 0
LEFT JOIN person h ON
c.file_loc = h.person_id
LEFT JOIN builder_list i ON
c.job_id = i.job_id AND i.role = 'E'::bpchar
LEFT JOIN
company j ON i.company_id = j.company_id
WHERE a.name::text =
'Awaiting Award'::character varying::text and g.person_id = 1
ORDER BY c.job_id, g.person_id, c.job_no, b.deadline, c.name,
bid_date(c.job_id), c.miscq, c.city, COALESCE(c.city::text ||
COALESCE(', '::text || c.st::text, ''::text), COALESCE(c.st,
''::character varying)::text), c.st,
CASE
WHEN c.file_loc = 0 THEN 'No Bid'::character
varying
WHEN c.file_loc = -1 THEN 'Bid
Board'::character varying
WHEN c.file_loc = -2
THEN 'Lost Job'::character varying
WHEN
c.file_loc = -3 THEN 'See Job Notes'::character
varying
WHEN c.file_loc < -3 OR c.file_loc IS
NULL THEN ''::character varying
WHEN h.initials
IS NOT NULL THEN h.initials
ELSE 'Unknown
person'::character varying
END, j.name, c.s_team,
c.file_loc;
Tables:
status - 14 rows
status_list - 6566 rows
job - 2210 rows
builder_list - 9670 rows
company - 1249 rows
call_list - 4731 rows
person - 27 rows
Primary keys:
any field with a "_id" suffix is a primary key; and thus is
implicitly indexed.
Other indexes:
status_list(job_id) btree
status_list(status_id) btree
job(file_loc) btree
builder_list(company_id) btree
call_list(company_id) btree
call_list(person_id) btree
call_list(company_id) btree
person(company_id) btree
explain analyze:
Unique (cost=1798.47..1809.38 rows=291 width=114) (actual
time=766.000..781.000 rows=566 loops=1)
->
Sort (cost=1798.47..1799.19 rows=291 width=114) (actual
time=766.000..766.000 rows=1473
loops=1)
Sort Key:
c.job_id, g.person_id, c.job_no, b.deadline, c.name,
bid_date(c.job_id), c.miscq, c.city, COALESCE(((c.city)::text ||
COALESCE((', '::text || (c.st)::text), ''::text)), (COALESCE(c.st,
''::character varying))::text), c.st, CASE WHEN (c.fi
(..)
-> Hash
Left Join (cost=1750.81..1786.56 rows=291 width=114) (actual
time=453.000..750.000 rows=1473
loops=1)
Hash Cond: ("outer".company_id =
"inner".company_id)
-> Merge Left Join (cost=1707.20..1722.53 rows=291
width=95) (actual time=437.000..484.000 rows=1473
loops=1)
Merge Cond: ("outer".job_id =
"inner".job_id)
-> Sort (cost=1382.44..1383.17 rows=291 width=91) (actual
time=406.000..406.000 rows=1473
loops=1)
Sort Key:
c.job_id
-> Hash Left Join (cost=1137.28..1370.53 rows=291
width=91) (actual time=234.000..390.000 rows=1473
loops=1)
Hash Cond: ("outer".file_loc =
"inner".person_id)
-> Nested Loop (cost=1135.94..1365.27 rows=291 width=84)
(actual time=234.000..390.000 rows=1473
loops=1)
Join Filter: (("inner".person_id = "outer".person_id) OR
("position"(("inner".s_team)::text, ("outer".initials)::text) >
0))
-> Seq Scan on person g (cost=0.00..1.34 rows=1 width=11)
(actual time=0.000..0.000 rows=1
loops=1)
Filter: (person_id =
1)
-> Merge Right Join (cost=1135.94..1349.74 rows=811
width=84) (actual time=234.000..297.000 rows=7490
loops=1)
Merge Cond: ("outer".company_id =
"inner".company_id)
-> Index Scan using idx_company_id_call_list on call_list
f (cost=0.00..189.80 rows=4731 width=8) (actual
time=0.000..15.000 rows=4731
loops=1)
-> Sort (cost=1135.94..1136.48 rows=214 width=84) (actual
time=234.000..234.000 rows=7490
loops=1)
Sort Key:
e.company_id
-> Merge Right Join (cost=1004.19..1127.66 rows=214
width=84) (actual time=203.000..219.000 rows=1569
loops=1)
Merge Cond: ("outer".company_id =
"inner".company_id)
-> Index Scan using company_pkey on company e
(cost=0.00..117.13 rows=1249 width=4) (actual time=0.000..0.000
rows=1249
loops=1)
-> Sort (cost=1004.19..1004.73 rows=214 width=84) (actual
time=203.000..203.000 rows=1569
loops=1)
Sort Key:
d.company_id
-> Hash Left Join (cost=633.74..995.91 rows=214 width=84)
(actual time=156.000..187.000 rows=1569
loops=1)
Hash Cond: ("outer".job_id =
"inner".job_id)
Join Filter: ("inner".won_heat OR "inner".won_vent OR "inner".won_tc OR
("outer".heat AND "inner".bid_heat AND ("inner".won_heat IS NULL)) OR
("outer".vent AND "inner".bid_vent AND ("inner
(..)
-> Merge Left Join (cost=368.17..381.60 rows=159
width=83) (actual time=78.000..93.000 rows=695
loops=1)
Merge Cond: ("outer".job_id =
"inner".job_id)
-> Sort (cost=168.31..168.71 rows=159 width=8) (actual
time=31.000..31.000 rows=695
loops=1)
Sort Key:
b.job_id
-> Nested Loop Left Join (cost=0.00..162.50 rows=159
width=8) (actual time=0.000..31.000 rows=695
loops=1)
Join Filter: ("outer".status_id =
"inner".status_id)
-> Seq Scan on status a (cost=0.00..1.18 rows=1 width=4)
(actual time=0.000..0.000 rows=1
loops=1)
Filter: ((name)::text = 'Awaiting
Award'::text)
-> Seq Scan on status_list b (cost=0.00..133.66 rows=2213
width=12) (actual time=0.000..15.000 rows=2210
loops=1)
Filter:
active
-> Sort (cost=199.86..205.39 rows=2210 width=79) (actual
time=47.000..47.000 rows=2194
loops=1)
Sort Key:
c.job_id
-> Seq Scan on job c (cost=0.00..77.10 rows=2210
width=79) (actual time=0.000..31.000 rows=2210
loops=1)
-> Hash (cost=202.88..202.88 rows=7475 width=14) (actual
time=78.000..78.000 rows=0
loops=1)
-> Seq Scan on builder_list d (cost=0.00..202.88
rows=7475 width=14) (actual time=0.000..15.000 rows=7517
loops=1)
Filter: (role =
'C'::bpchar)
-> Hash (cost=1.27..1.27 rows=27 width=11) (actual
time=0.000..0.000 rows=0
loops=1)
-> Seq Scan on person h (cost=0.00..1.27 rows=27
width=11) (actual time=0.000..0.000 rows=27
loops=1)
-> Sort (cost=324.76..330.25 rows=2196 width=8) (actual
time=31.000..31.000 rows=3044
loops=1)
Sort Key:
i.job_id
-> Seq Scan on builder_list i (cost=0.00..202.88
rows=2196 width=8) (actual time=0.000..31.000 rows=2153
loops=1)
Filter: (role =
'E'::bpchar)
-> Hash (cost=40.49..40.49 rows=1249 width=27) (actual
time=16.000..16.000 rows=0 loops=1)
-> Seq Scan on company j (cost=0.00..40.49 rows=1249
width=27) (actual time=0.000..0.000 rows=1249 loops=1)
Total
runtime: 781.000 ms