Skip site navigation (1) Skip section navigation (2)

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

From: Cédric Villemain <cedric(dot)villemain(dot)debian(at)gmail(dot)com>
To: Vitalii Tymchyshyn <tivv00(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: anti-join chosen even when slower than old plan
Date: 2010-11-12 10:56:34
Message-ID: AANLkTi=4XP-QRVtR5aRhC+OMjkJanvq-tp4YU6LO-zbP@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Vitalii TymchyshynDate: 2010-11-12 12:06:25
Subject: Re: anti-join chosen even when slower than old plan
Previous:From: Vitalii TymchyshynDate: 2010-11-12 10:10:01
Subject: Re: anti-join chosen even when slower than old plan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group