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.id and (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@xxxxxxxx
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
