Index problem. - Mailing list pgsql-general

From Dustin Sallings
Subject Index problem.
Date
Msg-id Pine.SGI.3.95.981016104749.18413A-100000@bleu.west.spy.net
Whole thread Raw
List pgsql-general
    I've got a phonebook database I'm writing, an in it is a zip code
table that has ~42k zip codes indexed off the integer zip code.  This work's
*great* 'cept the last time I built my database and loaded the zip codes,
my query didn't want to use the index

    I think this is 6.3.2 (is there a quick way to get the version
number?).

phonebook=> explain
phonebook->     select person.person_key, person.first_name,
person.last_name,
           addr_type.name as type, addrs.addr, zip.city, zip.state,
           zip.zip_key as zip_code
        from person, addr_type, addrs, zip, addr_map
        where addr_type.addr_type_key=addrs.addr_type_key
              and addrs.zip_key=zip.zip_key
              and addr_map.person_key=person.person_key
              and addr_map.addrs_key=addrs.addrs_key;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=1.03 size=1 width=104)
  ->  Nested Loop  (cost=0.00 size=1 width=76)
        ->  Nested Loop  (cost=0.00 size=1 width=68)
              ->  Nested Loop  (cost=0.00 size=1 width=40)
                    ->  Seq Scan on addrs  (cost=0.00 size=0 width=24)
                    ->  Seq Scan on addr_type  (cost=1.10 size=3 width=16)
              ->  Seq Scan on zip  (cost=0.00 size=0 width=28)
        ->  Seq Scan on addr_map  (cost=0.00 size=0 width=8)
  ->  Seq Scan on person  (cost=1.03 size=1 width=28)

...notice the statistics are *obviously* wrong there, though I did a few
vacuum verbose analyze's which seemed to do the right thing.

I dropped the Index and rebuilt it, and I get this:

Nested Loop  (cost=2.13 size=1 width=104)
  ->  Nested Loop  (cost=1.10 size=1 width=76)
        ->  Nested Loop  (cost=0.00 size=1 width=60)
              ->  Nested Loop  (cost=0.00 size=1 width=52)
                    ->  Seq Scan on addrs  (cost=0.00 size=0 width=24)
                    ->  Index Scan on zip  (cost=2.05 size=42155 width=28)
              ->  Seq Scan on addr_map  (cost=0.00 size=0 width=8)
        ->  Seq Scan on addr_type  (cost=1.10 size=3 width=16)
  ->  Seq Scan on person  (cost=1.03 size=1 width=28)

    I also get my data back a lot faster.  :)

--
SA, beyond.com           My girlfriend asked me which one I like better.
pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
|    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________


pgsql-general by date:

Previous
From: Jeff Gerhart
Date:
Subject: Table Locking
Next
From: "Philippe Rochat (RSR: 318 17 93)"
Date:
Subject: Re: Triggers and SQL functions