Re: Extra cost of "lossy mode" Bitmap Scan plan

On Tue, Apr 28, 2009 at 3:51 AM, Greg Stark <stark(at)enterprisedb(dot)com> wrote:
> On Tue, Apr 28, 2009 at 7:45 AM, higepon <higepon(at)gmail(dot)com> wrote:
>> "How much extra cost should we add for lossy mode?".
>
> There is something odd in this concern. Normally people aren't raising
> and lowering their work_mem so the comparison would be between a plan
> where the planner expects to see n records and a plan where the
> planner expects to see n+1 records where n would fit and n+1 wouldn't.
>
> It seems like an awfully narrow corner case where n records would be
> faster as a bitmap index scan but n+1 records would be faster as an
> index scan because the bitmap becomes lossy. The whole point of bitmap
> scans is that they're faster for large scans than index scans.
>
> If the logic you're suggesting would kick in at all it would be for a
> narrow range of scan sizes, so the net effect would be to use an index
> scan for small scans, then switch to a bitmap scan, then switch back
> to an index scan when the bitmap scan becomes lossy, then switch back
> to a lossy bitmap scan for large scans. I'm thinking that even if it's
> slightly faster when the planner has perfect inputs the downsides of
> switching back and forth might not be worth it. Especially when you
> consider than the planner is often going on approximate estimates and
> it is probably not switching in precisely the right spot.

You may be right, but on the other hand, I'm not sure there's any
sense in NOT trying to model the impact of the additional heap
fetches. Has anyone done a detailed analysis of the actual
performance characteristics of bitmap scans versus index scans, as
opposed to what the optimizer thinks? We've had long discussions on
this topic in relation to both the posix_fadvise patch and the gin
fast update patch, but I haven't seen a lot of hard numbers.

...Robert