On 2007-04-02, Peter Schuller <peter.schuller@infidyne.com> wrote:
> I have confirmed that I am seeing expected performance for random
> short and highly concurrent reads in one large (> 200 GB) file. The
> I/O is done using libaio however, so depending on implementation I
> suppose the I/O scheduling behavior of the fs/raid driver might be
> affected compared to having a number of concurrent threads doing
> synchronous reads. I will try to confirm performance in a way that
> will more closely match PostgreSQL's behavior.
>
> I have to say though that I will be pretty surprised if the
> performance is not matched in that test.
The next question then is whether anything in your postgres configuration
is preventing it getting useful performance from the OS. What settings
have you changed in postgresql.conf? Are you using any unusual settings
within the OS itself?
> Is there any chance there is some operation system conditional code in
> pg itself that might affect this behavior?
Unlikely.
>> Most likely your index is small enough that large parts of it will be
>> cached in RAM, so that the scan of the index to build the bitmap does
>> not need to hit the disk much if at all.
>
> Even so however, several seconds of CPU activity to scan the index for
> a few tens of thousands of entries sounds a bit excessive. Or does it
> not? Because at that level, the CPU bound period alone is approaching
> the time it would take to seek for each entry instead. But then I
> presume the amount of work is similar/the same for the other case,
> except it's being done at the beginning of the query instead of before
> each seek.
You're forgetting the LIMIT clause. For the straight index scan, the
query aborts when the LIMIT is reached having scanned only the specified
number of index rows (plus any index entries that turned out to be dead
in the heap). For the bitmap scan case, the limit can be applied only after
the heap scan is under way, therefore the index scan to build the bitmap
will need to scan ~50k rows, not the 10k specified in the limit, so the
amount of time spent scanning the index is 50 times larger than in the
straight index scan case.
However, I do suspect you have a problem here somewhere, because in my
tests the time taken to do the bitmap index scan on 50k rows, with the
index in cache, is on the order of 30ms (where the data is cached in
shared_buffers) to 60ms (where the data is cached by the OS). That's on
a 2.8GHz xeon.
--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services