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

[Christopher Schmidt]

On Mon, Jun 23, 2008 at 08:26:32PM -0500, Stephen Woodbridge wrote:
> select count(*) from addr a, addrfn b where a.arid=b.arid;
> 
> I realize that this requires to a full table or index scan regardless 
> because of the count(*), but I thought I should be able to use an index. 
> Maybe not, the optimizer might have decided that doing the full table 
> scan since it was required would be just as fast as scanning the index. 

Right. count(*) requires a sequential scan, no matter how the data is
indexed, as I understand it.

> I was thinking that doing a merge of two sorted indexes would be faster 
> than building a hash and then doing a hash join. But probably it is 
> smarter than I am :)

Almost always true: "The optimizer is better at what you're doing than
you are" is pretty much my maxim for working with Postgres. (It also
tends to mean "You can't fool the optimizer into doing what you think it
should": it's going to optimize your various attempts to fool it in
exactly the same way, most of the time.)

Regards,
-- 
Christopher Schmidt
Web Developer