Re: index-only scans

2011/8/12 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Fri, Aug 12, 2011 at 6:20 AM, Cédric Villemain
> <cedric(dot)villemain(dot)debian(at)gmail(dot)com> wrote:
>>>> Can this faux heap tuple be appended by the data from another index
>>>> once it has been created ? ( if the query involves those 2 index)
>>>
>>> I don't see how to make that work.  In general, a query like "SELECT
>>> a, b FROM foo WHERE a = 1 AND b = 1" can only use both indexes if we
>>> use a bitmap index scan on each followed by a bitmapand and then a
>>> bitmap heap scan.  However, this patch only touches the index-scan
>>> path, which only knows how to use one index for any given query.
>>
>> I thought of something like that:  'select a,b from foo where a=1
>> order by b limit 100' (or: where a=1 and b< now() )
>
> Well... PostgreSQL can only use the index on a or the index on b, not
> both.  This patch doesn't change that.  I'm not trying to use indexes
> in some completely new way; I'm just trying to make them faster by
> optimizing away the heap access.

For this kind of plan :
Bitmap Heap Scan
Recheck Cond
BitmapAnd
Bitmap Index Scan
Bitmap Index Scan

It may prevent useless Heap Fetch during "Bitmap Heap Scan", isn't it ?

>
>>> Actually, I can see a possible way to allow an index-only type
>>> optimization to be used for bitmap scans.  As you scan the index, any
>>> tuples that can be handled index-only get returned immediately; the
>>> rest are thrown into a bitmap.  Once you're done examining the index,
>>> you then do a bitmap heap scan to get the tuples that couldn't be
>>> handled index-only.  This seems like it might be our best hope for a
>>> "fast count(*)" type optimization, especially if you could combine it
>>> with some method of scanning the index in physical order rather than
>>> logical order.
>>
>> IIRC we expose some ideas around that, yes. (optimizing bitmap)
>>
>> Maybe a question that will explain me more about the feature
>> limitation (if any):
>> Does an index-only scan used when the table has no vismap set will
>> cost (in duration, IO, ...) more than a normal Index scan ?
>
> Yeah, it'll do a bit of extra work - the btree AM will cough up the
> tuple uselessly, and we'll check the visibility map, also uselessly.
> Then we'll end up doing it the regular way anyhow.  I haven't measured
> that effect yet; hopefully it's fairly small.

If it is small, or if we can reduce it to be near absent.
Then... why do we need to distinguish Index Scan at all ? (or
Index*-Only* scan, which aren't 100% 'Only' btw)
It is then just an internal optimisation on how we can access/use an
index. (really good and promising one)

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation