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;
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)
-> 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
Total runtime: 781.000 ms