Why is my view making my disk churn? (iostat) - Mailing list pgsql-general

Nested Loop Left Join  (cost=0.01..92.38 rows=4 width=222) (actual time=0.291..0.711 rows=4 loops=1)

  Join Filter: ((mytable.pid = info.pid) AND (mytable.year = info.year))

  ->  Nested Loop Left Join  (cost=0.01..81.93 rows=4 width=218) (actual time=0.110..0.221 rows=4 loops=1)

        Join Filter: ((mytable.pid = pos.pid) AND (mytable.year = pos.year))

        ->  Nested Loop Left Join  (cost=0.01..73.55 rows=4 width=214) (actual time=0.092..0.197 rows=4 loops=1)

              Join Filter: ((mytable.year = ipf.year) AND (mytable.pid = ipf.pid) AND ((mytable.sport_code)::text = (ipf.sport_code)::text))

              ->  Nested Loop  (cost=0.01..63.44 rows=4 width=202) (actual time=0.063..0.143 rows=4 loops=1)

                    ->  Index Scan using idx_persons_id on _persons player  (cost=0.00..8.28 rows=1 width=23) (actual time=0.016..0.017 rows=1 loops=1)

                          Index Cond: (mlbam_id = 461416)

                    ->  Nested Loop Left Join  (cost=0.01..55.12 rows=4 width=183) (actual time=0.045..0.122 rows=4 loops=1)

                          ->  Index Scan using mytable.pkey on mytable. (cost=0.00..21.92 rows=4 width=172) (actual time=0.027..0.071 rows=4 loops=1)

                                Index Cond: ((year = 2012) AND .pid = 461416) AND (gtype = 'R'::bpchar) AND (split = 'all'::text))

                          ->  Index Scan using teams_history_pkey on teams_history ts  (cost=0.01..8.28 rows=1 width=20) (actual time=0.007..0.007 rows=0 loops=4)

                                Index Cond: ((team_id = mytable.team_id) AND (year = textin(int4out(mytable.year))))

              ->  Materialize  (cost=0.00..10.03 rows=1 width=25) (actual time=0.006..0.009 rows=3 loops=4)

                    ->  Index Scan using ipf_pkey on ipf ipf  (cost=0.00..10.03 rows=1 width=25) (actual time=0.019..0.027 rows=3 loops=1)

                          Index Cond: ((year = 2012) AND .pid = 461416) AND ((factor_type)::text = 'run'::text) AND ((player_type)::text = 'hitter'::text))

        ->  Materialize  (cost=0.00..8.32 rows=1 width=12) (actual time=0.004..0.005 rows=1 loops=4)

              ->  Index Scan using primary_pos_pkey1 on pos  (cost=0.00..8.31 rows=1 width=12) (actual time=0.012..0.012 rows=1 loops=1)

                    Index Cond: (.pid = 461416) AND (year = 2012))

  ->  Materialize  (cost=0.00..8.30 rows=1 width=12) (actual time=0.003..0.004 rows=1 loops=4)

        ->  Index Scan using info_pkey on info  (cost=0.00..8.30 rows=1 width=12) (actual time=0.011..0.013 rows=1 loops=1)

              Index Cond: (.pid = 461416) AND (year = 2012))

Total runtime: 1.350 ms

When I run this query, the disk thrashes. It's on a 4-disk RAID5 array. I call it for some 6,000 values of pid, like: SELECT * FROM myview WHERE pid = 1, then 2, etc. The iostat outputs shows util% getting close to 100% quickly:

It hits 100% easily if other things are going on.

Is there anything I can do here? I suppose I could put my DB on a RAID0 array and that might help, but it seems drastic. This is not a high load environment and given that the view combines all of the tables via primary keys, I feel like this should be quicker. Is there some clue in the EXPLAIN output I am missing?

The throttling of the disk causes other processes to queue up.

Thanks!