Re: Question about a query plan

I'm still trying to understand EXPLAIN ANALYZE output.

ws2=> select count(*) from person_role;
 count
-------
   123
(1 row)

ws2=> select count(*) from person;
 count
-------
 11033
(1 row)

ws2=> EXPLAIN ANALYZE select id, first_name, last_name from person, person_role where id = 94 and person_role.person =
person.idand (person_role.role = 2); 
                                                        QUERY PLAN
   

---------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..8.28 rows=1 width=23) (actual time=0.198..0.237 rows=1 loops=1)
   ->  Index Scan using person_pkey on person  (cost=0.00..5.44 rows=1 width=23) (actual time=0.054..0.056 rows=1
loops=1)
         Index Cond: (id = 94)
   ->  Seq Scan on person_role  (cost=0.00..2.83 rows=1 width=4) (actual time=0.130..0.165 rows=1 loops=1)
         Filter: ((role = 2) AND (person = 94))
 Total runtime: 0.379 ms
(6 rows)


Why does it say "Seq Scan" on person_role?  The query has both the
"person" and "role" to use as a primary key -- which is indexed.
Indeed, "rows=1" so it looks like an index fetch.

Perhaps, I'm reading that incorrectly?


ws2=> \d person_role;
  Table "public.person_role"
 Column |  Type   | Modifiers
--------+---------+-----------
 person | integer | not null
 role   | integer | not null
Indexes:
    "person_role_pkey" primary key, btree (person, role)
Foreign-key constraints:
    "$2" FOREIGN KEY (role) REFERENCES role(id) ON DELETE RESTRICT
    "$1" FOREIGN KEY (person) REFERENCES person(id) ON DELETE CASCADE

Thanks,



--
Bill Moseley
moseley@hank.org