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

[Stephen Frost]

Stephen,

* Stephen Woodbridge (woodbri at swoodbridge.com) wrote:
> 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';

I'd start by recommending join-syntax rather than just commas, eg:

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

A better question, really, is- what is it you're really trying to do
here?  Do you really need a count?  Somehow, I doubt it..  There's
*alot* of edges and whatnot in TIGER, you're going to want to filter it
down more, one way or another.  I'm also not convinced that your joins
are set up correctly, honestly.  On tiger_01 I got 51.6M records
returned from that query, but there's only 1.6M edges, 1.5M featnames,
and 775K addr.  Seems a bit fishy to me though I don't see anything
obvious at the moment.

	Thanks,

		Stephen
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
Url : http://lists.osgeo.org/pipermail/geodata/attachments/20080623/14ee7a6e/attachment.bin