2011/10/10 Robert Haas <robertmhaas@gmail.com>:
> On Mon, Oct 10, 2011 at 1:36 PM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> wrote:
>>
>> That gives you an index-only scan; but without the WHERE clause it
>> uses a seq scan. I think it's mainly a matter of doing enough
>> benchmarks to figure out how best to model the costs of the index
>> scan so that it can be picked for that case.
>
> Right now, our costing model for index-only scans is pretty dumb. It
> assumes that using an index-only scan will avoid 10% of the heap
> fetches. That could easily be low, and on an insert-only table or one
> where only the recently-updated rows are routinely accessed, it could
> also be high. To use an index-only scan for a full-table COUNT(*),
> we're going to have to be significantly smarter, because odds are good
> that skipping 10% of the heap fetches won't be sufficient inducement
> to the planner to go that route; we are going to need a real number.
I have to raise that I think we are going to face the exact same issue
with the visibility_fraction that we face with the hack to set
random_page_cost very low to help optimizer (when index/table is
mostly in cache).
4 options have been viewed so far:
1. pg_class (initial proposal to store the cache estimates)
2. pg_class_nt (revived by Alvaro IIRC)
3. reloption
4. GUC (by Tom for visibility_fraction)
I am in favor of 1 or 2, 4 is a backup option, and 3 an open door to
planner hint (others also let DBA use its knowledge if he wants, but 3
make it mandatory for the DBA to decide, and no automatic way can be
used to update it, except if someone make ALTER TABLE lock free)
(It does not prevent a cost_indexonly() to be written meawhile...)
What do you think/prefer/suggest ?
--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation