Re: index with very high LIO

From: Greg Rahn <>
Date: Tue, 22 Nov 2011 13:33:14 -0800
Message-ID: <>

On Tue, Nov 22, 2011 at 7:15 AM, Mark W. Farnham <> wrote:
> It is a pity that there seems to be no way to use an index to grab block
> segments of a table to fts (and yes, then refilter the rows).

How does that help here? The system is limited by data movement between strorage and host so retrieving more data would not seem to add any benefit to me. Having just 35MB/s of bandwidth, sending any extra data seems counter productive -- even it it means getting the filter IOPS bound (which I doubt it is).

Just a thought. Might be more effort than value, but I think in the long
> haul everything just gets too big for a full FTS, even in parallel. Heh,
> the
> software effort would have been more defensible 20 years ago.

I'd say it's quite the opposite -- it is faster and more efficient to leverage a parallel sequential scan and filter out rows when it's a "many rows" problem than to get 1 block at a time for a "many rows" problem -- especially if your storage is rotating rust. Access via rowid is great for a "few rows" problem and "few" has an absolute ceiling IMO. This is the same reason that star transformation using bitmap indexes works great if the number of rowid gets is few, but once you cross into the 1 to 5 million range it degrades and a FTS with the appropriate hardware will beat it out. The caveat here is that FTS does not mean read ever single row blindly -- it means encompassing features like partitioning or storage index type functionality that leverages ordering/clustering.

The high level challenge here that I see is the client trying to fit the foot to the shoe, not the shoe to the foot which is resulting in some edge case engineering. As one of my university CS professors often said, "just because it works, doesn't mean it's right."

Greg Rahn  |  blog <>  |
twitter<>  |
 linkedin <>

Received on Tue Nov 22 2011 - 15:33:14 CST

Original text of this message