On Wed, Aug 5, 2009 at 8:50 PM, Ip Wing Kin John<wkipjohn@gmail.com> wrote:
> I have a database (699221). It contains of 1.8GB data (707710). I am
> doing a complex query. Which required to load a 80MB index (732287).
>
> I restarted Postgresql so the cache is empty and it has to read the
> table and index from disk. Which I understand is an expensive process.
> But what I don't understand is even I split the index into a different
> tablespace located on a completely separate disk (mounted on /hdd2)
> there is still a very long I/O wait time. That index is only thing
> exist on that disk. Any idea why? Or any way I can find out what it is
> waiting for? Thanks.
OK before DTrace, did you run explain analyze on the query? I think
the output of that would be interesting.
Looking at the DTrace output it looks to me like you're reading at
least one > 1GB table. since you're accessing a file with a .1 on it.