Optimize a VIEW question - Mailing list pgsql-general

I'm just starting out using VIEWs -- and mostly new to postgresql and
I'm trying to learn a little about reading the EXPLAIN ANALYZE output.

I have the following view setup and I'm wonder where to create
indexes, and mostly if I'm doing anything the incorrect or a very
expensive way.

    CREATE VIEW class_list
        (
            id, name, class_time, location, workshop, review_mode, workshop_group,
            location_name,
            address, city, state, zip,
            region, region_name
        )
    AS
        SELECT class.id, class.name, class.class_time, class.location, class.workshop,
               class.review_mode, class.workshop_group,
               location.name,
               address.id, address.city, address.state, address.zip,
               region.id, region.name

          FROM class, location, address, region

         WHERE class.location           = location.id
               AND location.address     = address.id
               AND location.region      = region.id;


I'm not clear about the Seq Scan below.  The region
table is quite small, so am I correct that is why the planner is doing
a seq scan on that table?

    \d region
                                 Table "public.region"
       Column   |  Type   |                       Modifiers
    ------------+---------+--------------------------------------------------------
     id         | integer | not null default nextval('public.region_id_seq'::text)
     active     | boolean | not null default true
     sort_order | integer | not null default 1
     name       | text    | not null
    Indexes:
        "region_pkey" primary key, btree (id)
        "region_name_key" unique, btree (name)


EXPLAIN ANALYZE select * from class_list where workshop = 28;
                                                                          QUERY PLAN
                                      

--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=51.78..93.07 rows=9 width=157) (actual time=1.306..1.468 rows=6 loops=1)
   ->  Hash Join  (cost=51.78..76.87 rows=8 width=129) (actual time=1.245..1.299 rows=6 loops=1)
         Hash Cond: ("outer".id = "inner".region)
         ->  Seq Scan on region  (cost=0.00..20.00 rows=1000 width=36) (actual time=0.016..0.027 rows=10 loops=1)
         ->  Hash  (cost=51.76..51.76 rows=8 width=97) (actual time=1.019..1.019 rows=0 loops=1)
               ->  Hash Join  (cost=26.68..51.76 rows=8 width=97) (actual time=0.201..1.007 rows=6 loops=1)
                     Hash Cond: ("outer".id = "inner"."location")
                     ->  Seq Scan on "location"  (cost=0.00..20.00 rows=1000 width=44) (actual time=0.014..0.694
rows=104loops=1) 
                     ->  Hash  (cost=26.66..26.66 rows=7 width=57) (actual time=0.150..0.150 rows=0 loops=1)
                           ->  Index Scan using class_workshop_index on "class"  (cost=0.00..26.66 rows=7 width=57)
(actualtime=0.057..0.137 rows=6 loops=1) 
                                 Index Cond: (workshop = 28)
   ->  Index Scan using address_pkey on address  (cost=0.00..2.01 rows=1 width=32) (actual time=0.013..0.015 rows=1
loops=6)
         Index Cond: ("outer".address = address.id)
 Total runtime: 1.853 ms
(14 rows)


By the way -- at one point I managed to hang postgresql (7.4.8-16 on
Debian Sid).  I have not been able to make it happen again, but it
seemed odd.

    (gdb) bt
    #0  0x081e51ee in tuplestore_gettuple ()
    #1  0x0810c7f0 in ExecMaterial ()
    #2  0x08102cb2 in ExecProcNode ()
    #3  0x0810d8d5 in ExecNestLoop ()
    #4  0x08102ceb in ExecProcNode ()
    #5  0x081093a4 in ExecAgg ()
    #6  0x08102c79 in ExecProcNode ()
    #7  0x08101ecc in ExecutorRun ()
    #8  0x0816f58b in PortalSetResultFormat ()
    #9  0x0816f8c7 in PortalRun ()
    #10 0x0816da9f in PostgresMain ()
    #11 0x08148b4e in ClosePostmasterPorts ()
    #12 0x0814a4e1 in PostmasterMain ()
    #13 0x0811c2e7 in main ()


--
Bill Moseley
moseley@hank.org