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