Help with query plan inconsistencies - Mailing list pgsql-performance

Hello,

I am using postgres 7.4.2 as a backend for geocode data for a mapping
application.  My question is why can't I get a consistent use of my indexes
during a query, I tend to get a lot of seq scan results.

I use a standard query:

SELECT lat, long, mac, status FROM (
   SELECT text(mac) as mac, lat long, CASE status WHEN 0 THEN 0 WHEN 1 THEN
1 ELSE -1 END
      as status FROM cable_billing LEFT OUTER JOIN davic USING(mac) WHERE
boxtype='d'
)AS FOO WHERE (long>=X1) AND (long<=X2) AND (lat>=Y1) AND (lat<=Y2)

Where X1,X2,Y1,Y2 are the coordinates for the rectangle of the map viewing
area.

QUERY PLAN #1 & #2 are from when I get a view from 10 miles out, sometimes
it uses the index(#1) and most of the time not(#2).  I do run into plans
that seq scan both sides of the join.

QUERY PLAN #3 is when I view from 5 miles out, and I have much greater
chance of getting index scans ( about 90% of the time).

I have listed information about the database below.

Cable_billing ~500,000 rows updated once per day
Davic  ~500,000 rows, about 100 rows update per minute

Any info or suggestions would be appreciated.

Woody


twc-ral-overview=# \d cable_billing;
             Table "public.cable_billing"
     Column      |          Type          | Modifiers
-----------------+------------------------+-----------
 cable_billingid | integer                | not null
 mac             | macaddr                | not null
 account         | integer                |
 number          | character varying(10)  |
 address         | character varying(200) |
 region          | character varying(30)  |
 division        | integer                |
 franchise       | integer                |
 node            | character varying(10)  |
 lat             | numeric                |
 long            | numeric                |
 trunk           | character varying(5)   |
 ps              | character varying(5)   |
 fd              | character varying(5)   |
 le              | character varying(5)   |
 update          | integer                |
 boxtype         | character(1)           |
Indexes: cable_billing_pkey primary key btree (mac),
         cable_billing_account_index btree (account),
         cable_billing_lat_long_idx btree (lat, long),
         cable_billing_node_index btree (node),
         cable_billing_region_index btree (region)

twc-ral-overview=# \d davic
            Table "public.davic"
 Column  |         Type          | Modifiers
---------+-----------------------+-----------
 davicid | integer               | not null
 mac     | macaddr               | not null
 source  | character varying(20) |
 status  | smallint              |
 updtime | integer               |
 type    | character varying(10) |
 avail1  | integer               |
Indexes: davic_pkey primary key btree (mac)



twc-ral-overview=# vacuum analyze;
VACUUM
twc-ral-overview=# explain analyze SELECT lat, long, mac, status FROM
(SELECT text(mac) as mac, lat, long, CASE status WHEN 0 THEN 0 WHEN 1 THEN 1
ELSE -1 END as status FROM cable_billing LEFT OUTER JOIN davic USING(mac)
WHERE boxtype='d') AS foo  WHERE (long>=-78.70723462816063) AND
(long<=-78.53096764204116) AND (lat>=35.57411187866667) AND
(lat<=35.66366331376857);
QUERY PLAN #1

----------------------------------------------------------------------------
----------------------------------------------------------------------------
-----
 Nested Loop Left Join  (cost=0.00..23433.18 rows=1871 width=34) (actual
time=0.555..5095.434 rows=3224 loops=1)
   ->  Index Scan using cable_billing_lat_long_idx on cable_billing
(cost=0.00..12145.85 rows=1871 width=32) (actual time=0.431..249.931
rows=3224 loops=1)
         Index Cond: ((lat >= 35.57411187866667) AND (lat <=
35.66366331376857) AND (long >= -78.70723462816063) AND (long <=
-78.53096764204116))
         Filter: (boxtype = 'd'::bpchar)
   ->  Index Scan using davic_pkey on davic  (cost=0.00..6.01 rows=1
width=8) (actual time=1.476..1.480 rows=1 loops=3224)
         Index Cond: ("outer".mac = davic.mac)
 Total runtime: 5100.028 ms
(7 rows)



twc-ral-overview=# vacuum analyze;
VACUUM
twc-ral-overview=# explain analyze SELECT lat, long, mac, status FROM
(SELECT text(mac) as mac, lat, long, CASE status WHEN 0 THEN 0 WHEN 1 THEN 1
ELSE -1 END as status FROM cable_billing LEFT OUTER JOIN davic USING(mac)
WHERE boxtype='d') AS foo  WHERE (long>=-78.87878592206046) AND
(long<=-78.70220280717479) AND (lat>=35.71703190638861) AND
(lat<=35.80658335998006);
QUERY PLAN #2

----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------------------
 Nested Loop Left Join  (cost=0.00..76468.90 rows=9223 width=34) (actual
time=0.559..17387.427 rows=19997 loops=1)
   ->  Seq Scan on cable_billing  (cost=0.00..20837.76 rows=9223 width=32)
(actual time=0.290..7117.799 rows=19997 loops=1)
         Filter: ((boxtype = 'd'::bpchar) AND (long >= -78.87878592206046)
AND (long <= -78.70220280717479) AND (lat >= 35.71703190638861) AND (lat <=
35.80658335998006))
   ->  Index Scan using davic_pkey on davic  (cost=0.00..6.01 rows=1
width=8) (actual time=0.455..0.461 rows=1 loops=19997)
         Index Cond: ("outer".mac = davic.mac)
 Total runtime: 17416.501 ms
(6 rows)



twc-ral-overview=# explain analyze SELECT lat, long, mac, status FROM
(SELECT text(mac) as mac, lat, long, CASE status WHEN 0 THEN 0 WHEN 1 THEN 1
ELSE -1 END as status FROM cable_billing LEFT OUTER JOIN davic USING(mac)
WHERE boxtype='d') AS foo  WHERE (long>=-78.83419423836857) AND
(long<=-78.7467945148866) AND (lat>=35.73964586635293) AND
(lat<=35.783969313080604);
QUERY PLAN #3

----------------------------------------------------------------------------
----------------------------------------------------------------------------
-----
 Nested Loop Left Join  (cost=0.00..29160.02 rows=2327 width=34) (actual
time=0.279..510.773 rows=5935 loops=1)
   ->  Index Scan using cable_billing_lat_long_idx on cable_billing
(cost=0.00..15130.08 rows=2326 width=32) (actual time=0.197..274.115
rows=5935 loops=1)
         Index Cond: ((lat >= 35.73964586635293) AND (lat <=
35.783969313080604) AND (long >= -78.83419423836857) AND (long <=
-78.7467945148866))
         Filter: (boxtype = 'd'::bpchar)
   ->  Index Scan using davic_pkey on davic  (cost=0.00..6.01 rows=1
width=8) (actual time=0.021..0.024 rows=1 loops=5935)
         Index Cond: ("outer".mac = davic.mac)
 Total runtime: 516.782 ms
(7 rows)

----------------------------------------------------------------------------
-----------------------------------

iglass Networks
211-A S. Salem St.
(919) 387-3550 x813
P.O. Box 651
(919) 387-3570 fax
Apex, NC 27502
http://www.iglass.net