Re: [WIP] cache estimates, cache access cost

2011/6/14 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Tue, Jun 14, 2011 at 10:29 AM, Cédric Villemain
> <cedric(dot)villemain(dot)debian(at)gmail(dot)com> wrote:
>> 0001-Add-reloscache-column-to-pg_class.patch
>> 0002-Add-a-function-to-update-the-new-pg_class-cols.patch
>> 0003-Add-ANALYZE-OSCACHE-VERBOSE-relation.patch
>> 0004-Add-a-Hook-to-handle-OSCache-stats.patch
>> 0005-Add-reloscache-to-Index-Rel-OptInfo.patch
>> 0006-Add-cache_page_cost-GUC.patch
>
> It seems to me that posting updated versions of this patch gets us no
> closer to addressing the concerns I (and Tom, on other threads)
> expressed about this idea previously.  Specifically:
>
> 1. ANALYZE happens far too infrequently to believe that any data taken
> at ANALYZE time will still be relevant at execution time.

ANALYZE happens when people execute it, else it is auto-analyze and I
am not providing auto-analyze-oscache.
ANALYZE OSCACHE is just a very simple wrapper to update pg_class. The
frequency is not important here, I believe.

> 2. Using data gathered by ANALYZE will make plans less stable, and our
> users complain not infrequently about the plan instability we already
> have, therefore we should not add more.

Again, it is hard to do a UPDATE pg_class SET reloscache, so I used
ANALYZE logic.
Also I have taken into account the fact that someone may want to SET
the values like it was also suggested, so my patches allow to do :
'this table is 95% in cache, the DBA said' (it is stable, not based on
OS stats).

This case has been suggested several times and is covered by my patch.

> 3. Even if the data were accurate and did not cause plan stability, we
> have no evidence that using it will improve real-world performance.

I have not finish my work on cost estimation and I believe this work
will take some time and can be done in another commitfest. At the
moment my patches do not change anything on the dcision of the
planner, just offers the tools I need to hack cost estimates.

>
> Now, it's possible that you or someone else could provide some
> experimental evidence refuting these points.  But right now there
> isn't any, and until there is, -1 from me on applying any of this.

I was trying to split the patch size by group of features to reduce
its size. The work is in progress.

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

--
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support