On Mon, Oct 10, 2011 at 3:16 PM, Cédric Villemain
cedric.villemain.deb...@gmail.com wrote:
2011/10/10 Robert Haas robertmh...@gmail.com:
On Mon, Oct 10, 2011 at 1:36 PM, Kevin Grittner
kevin.gritt...@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 ?
Well, I think a GUC is kind of useless, because you're going to want
to make this per-table.
As to the rest, I think they're all going to have the same problems -
or non-problems - with ALTER TABLE locking the full table. If that's
a show-stopper, we should try to fix it. But how to do that is a
topic for another thread.
--
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
