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

From Wells Oliver
Subject Why is my view making my disk churn? (iostat)
Date
Msg-id CAOC+FBX7z3XVKH8fdZF2G_M8e5zPN4tNQfFXYfUsqrxmiT_T8A@mail.gmail.com
Whole thread Raw
Responses Re: Why is my view making my disk churn? (iostat)
List pgsql-general
I have this view which combines a few tables. It's wide-ish, 60 rows, almost all columns integer. It joins five tables, all using primary keys. The explain output is as follows:

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:

cciss/c0d1        0.00     0.00  152.00    0.00  2784.00     0.00    18.32     0.97    6.38   6.18  94.00
cciss/c0d1        0.00     0.00  135.00    0.00  2688.00     0.00    19.91     0.92    6.96   6.22  84.00
cciss/c0d1        0.00     0.00  131.00    0.00  2928.00     0.00    22.35     0.75    5.80   5.42  71.00

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!

--
Wells Oliver
wellsoliver@gmail.com

pgsql-general by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: Bad pg_dump error message
Next
From: Tom Lane
Date:
Subject: Re: Bad pg_dump error message