[Geodata] Tiger - can't get SQL to use indexes in postgres

Stephen Woodbridge woodbri at swoodbridge.com
Mon Jun 23 12:14:30 EDT 2008


I loaded up some of the Tiger2007fe data into postgres and I'm trying to 
get a select SELECT to use indexes, but it isn't and I don't understand 
why. There are three tables, somewhat over indexed at the moment because 
  I can't get things to work.

I have vacuum analyzed the whole database. Below are the query, explain 
analyze plan and table definitions. This is a subset of the query:

select count(*) from featnames b, addr c, addrfn d, edges a
  where a.tlid=b.tlid and b.tlid=c.tlid and b.linearid=d.linearid and 
c.arid=d.arid and a.roadflg='Y';

for joining the road segments with the address ranges and feature names.

Any thoughts on speeding this up?
   -Steve


select count(*) from addr a, addrfn b, featnames c where a.arid=b.arid 
and b.linearid=c.linearid;


> "Aggregate  (cost=89284.44..89284.45 rows=1 width=0) (actual time=17814.308..17814.309 rows=1 loops=1)"
> "  ->  Merge Join  (cost=57767.01..84515.71 rows=1907489 width=0) (actual time=7950.466..16797.782 rows=2600660 loops=1)"
> "        Merge Cond: ("outer".linearid = "inner".linearid)"
> "        ->  Sort  (cost=33641.38..33999.15 rows=143108 width=26) (actual time=4650.449..4770.941 rows=145201 loops=1)"
> "              Sort Key: b.linearid"
> "              ->  Hash Join  (cost=5319.40..17291.51 rows=143108 width=26) (actual time=332.524..1097.152 rows=145201 loops=1)"
> "                    Hash Cond: ("outer".arid = "inner".arid)"
> "                    ->  Seq Scan on addrfn b  (cost=0.00..3103.01 rows=145201 width=52) (actual time=0.020..108.976 rows=145201 loops=1)"
> "                    ->  Hash  (cost=3932.12..3932.12 rows=134512 width=26) (actual time=332.223..332.223 rows=134512 loops=1)"
> "                          ->  Seq Scan on addr a  (cost=0.00..3932.12 rows=134512 width=26) (actual time=0.018..142.554 rows=134512 loops=1)"
> "        ->  Sort  (cost=24125.63..24445.04 rows=127765 width=26) (actual time=3299.985..5366.298 rows=2643396 loops=1)"
> "              Sort Key: c.linearid"
> "              ->  Seq Scan on featnames c  (cost=0.00..9795.65 rows=127765 width=26) (actual time=0.045..259.080 rows=127765 loops=1)"
> "Total runtime: 17824.746 ms"


CREATE TABLE addr
(
   tlid int4,
   fromhn varchar(12),
   tohn varchar(12),
   side char(1),
   zip varchar(5),
   plus4 varchar(4),
   fromtyp char(1),
   totyp char(1),
   arid char(22) NOT NULL,
   mtfcc varchar(5),
   CONSTRAINT addr_pkey PRIMARY KEY (arid)
)
WITHOUT OIDS;
ALTER TABLE addr OWNER TO postgres;


-- Index: addr_arid_idx

-- DROP INDEX addr_arid_idx;

CREATE UNIQUE INDEX addr_arid_idx
   ON addr
   USING btree
   (arid);

-- Index: addr_tlid_idx

-- DROP INDEX addr_tlid_idx;

CREATE INDEX addr_tlid_idx
   ON addr
   USING btree
   (tlid);

CREATE TABLE addrfn
(
   arid char(22) NOT NULL,
   linearid char(22) NOT NULL,
   CONSTRAINT addrfn_pkey PRIMARY KEY (arid, linearid)
)
WITHOUT OIDS;
ALTER TABLE addrfn OWNER TO postgres;


-- Index: addrfn_arid_idx

-- DROP INDEX addrfn_arid_idx;

CREATE INDEX addrfn_arid_idx
   ON addrfn
   USING btree
   (arid);

-- Index: addrfn_linearid_idx

-- DROP INDEX addrfn_linearid_idx;

CREATE INDEX addrfn_linearid_idx
   ON addrfn
   USING btree
   (linearid);

-- Index: addrfn_pk_idx

-- DROP INDEX addrfn_pk_idx;

CREATE UNIQUE INDEX addrfn_pk_idx
   ON addrfn
   USING btree
   (arid, linearid);

CREATE TABLE featnames
(
   tlid int4 NOT NULL,
   fullname varchar(100),
   name varchar(100),
   predirabrv varchar(15),
   pretypabrv varchar(50),
   prequalabr varchar(15),
   sufdirabrv varchar(15),
   suftypabrv varchar(50),
   sufqualabr varchar(15),
   predir varchar(2),
   pretyp varchar(3),
   prequal varchar(2),
   sufdir varchar(2),
   suftyp varchar(3),
   sufqual varchar(2),
   linearid char(22) NOT NULL,
   mtfcc varchar(5),
   paflag char(1),
   CONSTRAINT featnames_pkey PRIMARY KEY (tlid, linearid)
)
WITHOUT OIDS;
ALTER TABLE featnames OWNER TO postgres;


-- Index: featnames_linearid_idx

-- DROP INDEX featnames_linearid_idx;

CREATE INDEX featnames_linearid_idx
   ON featnames
   USING btree
   (linearid);

-- Index: featnames_tlid_idx

-- DROP INDEX featnames_tlid_idx;

CREATE INDEX featnames_tlid_idx
   ON featnames
   USING btree
   (tlid);





More information about the Geodata mailing list