Re: index with very high LIO

  • From: Jeremy Schneider <jeremy.schneider@xxxxxxxxxxxxxx>
  • To: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
  • Date: Thu, 17 Nov 2011 14:06:44 -0600

Yeah, you're not the first one to point that out...  I'm doing some
tests now.  FWIW it's 7 million out of over a billion rows that are all
stuffed into a 400GB heap table with the worst possible clustering
factor on the foreign key we're using to join.  Also I haven't been able
to get more than 20MB/s total throughput with parallel full scans,
although single-block reads go at a somewhat decent rate of 5ms.  But
all the same, I'm checking out the hash join... likely it will turn out
to be more efficient.

I should have checked this out more in-depth awhile ago...  I got a
little carried away with maths and mistakenly thought that it was
totally unrealistic.  This is the problem with ruling things out based
on "calculations".  There's little to verify your correctness.  It's
much easier to find your mistakes when you rule things out with
*testing* instead...  I should know this by now, for some reason I'm
still making the occasional poor judgements in the heat of the moment.  :)


On 11/17/2011 01:24 PM, Greg Rahn wrote:
> Am I reading this right?  7 million rows joined using NESTED LOOPS
> join?  Wouldn't a parallel hash join be better/faster?
> On Thu, Nov 17, 2011 at 3:34 AM, Jeremy Schneider
> <jeremy.schneider@xxxxxxxxxxxxxx> wrote:
>> PS... 8k block size; segment of index in question is 100G.  Query is
>> pulling 7 million rows from a join of two billion row tables...

Other related posts: