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.