Re: Question about a query plan

On Tue, 20 Sep 2005, Bill Moseley wrote:

> 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.

IIRC, that's how many rows met the filter.

My guess is that unless there's dead space, 123 rows of person_role should
fit in 1 page, so it's probably deciding that using the index would
involve more disk access than not.