Re: index-only scans

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.

>> 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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company