Help with nested loop left join performance - Mailing list pgsql-performance

I am running 7.4.8 and have a query that I have been running for a while
that has recently have experienced a slowdown.  The original query
involves a UNION but I have narrowed it down to this half of the query
as being my issue.  (The other half take 4 seconds).

The only issue that I have had is index bloat which I had to reindex the
entire cluster to get rid of.  My query has been slow since the start of
the index bloat.

Thanks in advance

Woody




explain analyze SELECT column1, column2, column3, column4, column5,
column6, column7, column8 FROM (SELECT CASE status WHEN 0 THEN 0 WHEN 1
THEN 1 ELSE -1 END AS column1,  mac AS column2, account AS column3,
number || ' ' || address AS column4, 'qmod' || '.' || 'dmod' AS column5,
node AS column6, grid AS column7, boxtype AS column8, number, address
FROM settop_billing  LEFT OUTER JOIN (dhct JOIN dhct_davic USING(mac))
USING (mac) WHERE region='GTown1E' AND node='1E012' ) AS foo;


QUERY PLAN

------------------------------------------------------------------------
------------------------------------------------------------------------
--------------
 Nested Loop Left Join  (cost=38659.85..55988.94 rows=2 width=84)
(actual time=14054.297..294973.046 rows=418 loops=1)
   Join Filter: ("outer".mac = "inner".mac)
   ->  Index Scan using settop_billing_region_node_index on
settop_billing  (cost=0.00..7.99 rows=2 width=82) (actual
time=0.115..8.582 rows=418 loops=1)         Index Cond: (((region)::text
= 'GTown1E'::text) AND ((node)::text = '1E012'::text))
   ->  Materialize  (cost=38659.85..42508.98 rows=384913 width=8)
(actual time=2.211..286.267 rows=382934 loops=418)
         ->  Hash Join  (cost=14784.66..38659.85 rows=384913 width=8)
(actual time=923.855..13647.840 rows=382934 loops=1)
               Hash Cond: ("outer".mac = "inner".mac)
               ->  Append  (cost=0.00..8881.11 rows=384912 width=8)
(actual time=0.023..10914.365 rows=384900 loops=1)
                     ->  Seq Scan on dhct_davic  (cost=0.00..0.00 rows=1
width=8) (actual time=0.002..0.002 rows=0 loops=1)
                     ->  Seq Scan on probe_dhct_davic dhct_davic
(cost=0.00..8881.11 rows=384911 width=8) (actual time=0.018..10505.255
rows=384900 loops=1)
               ->  Hash  (cost=12154.13..12154.13 rows=410613 width=6)
(actual time=923.433..923.433 rows=0 loops=1)
                     ->  Seq Scan on dhct  (cost=0.00..12154.13
rows=410613 width=6) (actual time=0.019..534.641 rows=409576 loops=1)
 Total runtime: 294994.440 ms
(13 rows)

The tables involved are defined as follows:

                                               Table
"public.settop_billing"
     Column      |          Type          |
Modifiers
-----------------+------------------------+-----------------------------
---------------------------------------------------
 cable_billingid | integer                | not null default
nextval('public.new_cable_billing_cable_billingid_seq'::text)
 mac             | macaddr                | not null
 account         | character varying(20)  |
 number          | character varying(12)  |
 address         | character varying(200) |
 name            | character varying(100) |
 phone           | character varying(10)  |
 region          | character varying(30)  |
 node            | character varying(10)  |
 grid            | character varying(15)  |
 lat             | numeric                |
 long            | numeric                |
 boxtype         | character(1)           |
Indexes:
    "settop_billing_mac_index" unique, btree (mac)
    "settop_billing_account_index" btree (account)
    "settop_billing_lat_log_index" btree (lat, long)
    "settop_billing_region_node_index" btree (region, node)
Inherits: cable_billing

                              Table "public.dhct"
   Column   |         Type          |                 Modifiers
------------+-----------------------+-----------------------------------
--------
 dhctid     | integer               | not null default
nextval('dhct_id'::text)
 mac        | macaddr               | not null
 ip         | inet                  |
 serial     | macaddr               |
 admin_stat | integer               |
 oper_stat  | integer               |
 qmod       | character varying(50) |
 dmod       | integer               |
 hub        | character varying(50) |
 dncs       | character varying(50) |
 auth       | text                  |
 updtime    | integer               |
Indexes:
    "dhct_pkey" primary key, btree (mac)
    "dhct_qmod_index" btree (qmod)

                              Table "iprobe024.probe_dhct_davic"
 Column  |         Type          |                         Modifiers
---------+-----------------------+--------------------------------------
----------------------
 davicid | integer               | not null default
nextval('public.davic_davicid_seq'::text)
 mac     | macaddr               | not null
 type    | character varying(10) | default 'davic'::character varying
 source  | character varying(20) |
 status  | smallint              |
 updtime | integer               |
 avail1  | integer               |
Indexes:
    "probe_dhct_davic_mac_index" unique, btree (mac)
Inherits: dhct_davic


----------------------------------------
iGLASS Networks
211-A S. Salem St
Apex NC 27502
(919) 387-3550 x813
www.iglass.net