2011/5/11 Bruce Momjian <br...@momjian.us>: > Cédric Villemain wrote: >> 2011/5/10 Kevin Grittner <kevin.gritt...@wicourts.gov>: >> > Simon Riggs <si...@2ndquadrant.com> wrote: >> >> The typical speed up for non-covered indexes will come when we >> >> access a very large table (not in cache) via an index scan that is >> >> smaller than a bitmapindex scan. Will we be able to gauge >> >> selectivities sufficiently accurately to be able to pinpoint that >> >> during optimization? How will we know that the table is not in >> >> cache? Or is this an optimisation in the executor for a bitmapheap >> >> scan? >> > >> > I would continue to object to using current cache contents for plan >> > choice because of plan instability and the fact that an odd initial >> > cache load could skew plans in a bad direction indefinitely. ?I do >> > agree (and have already posted) that I think the hardest part of >> > this might be developing a good cost model. ?I doubt that's an >> > insoluble problem, especially since it is something we can refine >> > over time as we gain experience with the edge cases. >> >> you will have the same possible instability in planning with the >> index(-only?) scan because we may need to access heap anyway and this >> needs is based on estimation, or I miss something ? I understood the >> idea was just to bypass the heap access *if* we can for *this* >> heap-page. >> >> In reality, I am not really scared by plan instability because of a >> possible PG/OS cache estimation. The percentages remain stable in my >> observations ... I don't know yet how it will go for vis map. >> >> And, we already have plan instability currently, which is *good* : at >> some point a seq scan is better than an bitmap heap scan. Because the >> relation size change and because ANALYZE re-estimate the distribution >> of the data. I will be very happy to issue ANALYZE CACHE as I have to >> ANALYZE temp table for large query if it allows the planner to provide >> me the best plan in some scenario...but this is another topic, sorry >> for the digression.. > > Good point --- we would be making plan decisions based on the visibility > map coverage. The big question is whether visibility map changes are > more dynamic than the values we already plan against, like rows in the > table, table size, and value distributions. I don't know the answer. >
Robert, I though of Covered-Index as just a usage of the vis map: don't take the heap block if not needed. This look easier to do and better in the long term (because read-only DB may quickly turn into a no-heap access DB for example). Thus this is not real covered-index. Did you want to implement real covered-index and did you have ideas on how to do that ? Or just an optimization of the current planner/executor on index usage ? ___ I don't know VM internals: * do we have a counter of ALL_VISIBLE flag set on a relation ? (this should be very good for planner) * do we need a pg_class.rel_vmvisible ?! (I have hands up, don't shoot pleeaase) * is it ok to parse VM for planning (I believe it is not) ? Ideas ? -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers