Re: anti-join chosen even when slower than old plan

I supposed it was an answer to my mail but not sure... please keep
CC'ed people, it is easier to follow threads (at least for me)

2010/11/12 Vitalii Tymchyshyn <tivv00(at)gmail(dot)com>:
> I'd say there are two Qs here:
>
> 1) Modify costs based on information on how much of the table is in cache.
> It would be great  if this can be done, but I'd prefer to have it as admin
> knobs (because of plan stability). May be both admin and automatic ways can
> be followed with some parallel (disableable) process modify knobs on admin
> behalf. In this case different strategies to automatically modify knobs can
> be applied.

OS cache is usualy stable enough to keep your plans stable too, I think.

>
> 2) Modify costs for part of table retrieval. Then you need to define "part".
> Current ways are partitioning and partial indexes. Some similar to partial
> index thing may be created, that has only "where" clause and no data. But
> has statistics and knobs (and may be personal bufferspace if they are
> introduced). I don't like to gather data about "last X percents" or like,
> because it works only in clustering and it's hard for optimizer to decide if
> it will be enough to scan only this percents for given query.

Modifying random_page_cost and sequential_page_cost thanks to
statistics about cached blocks can be improved if we know the
distribution.

It does not mean : we know we have last 15% in cache, and we are goign
to request those 15%.

>
> Best regards, Vitalii Tymchyshyn
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

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