On Fri, Aug 12, 2011 at 6:20 AM, Cédric Villemain
<cedric.villemain.debian@gmail.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