On Fri, Aug 12, 2011 at 6:20 AM, Cédric Villemain <cedric.villemain.deb...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
