Re: index-only scans

2011/8/12 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Thu, Aug 11, 2011 at 5:39 PM, Cédric Villemain
> <cedric(dot)villemain(dot)debian(at)gmail(dot)com> wrote:
>> 2011/8/11 Robert Haas <robertmhaas(at)gmail(dot)com>:
>>> Please find attached a patch implementing a basic version of
>>> index-only scans.  This patch is the work of my colleague Ibrar Ahmed
>>> and myself, and also incorporates some code from previous patches
>>> posted by Heikki Linnakanagas.
>>
>> Great!
>
> Glad you like it...!
>
>> 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() )

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

>
> But even that trick would only work with a single index.  I'm not sure
> there's any good way to assemble pieces of tuples from two different
> indexes, at least not efficiently.

okay.

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

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