Re: How to specify/mock the statistic data of tables in PostgreSQL

On Fri, Jan 10, 2014 at 11:19 PM, Atri Sharma <atri(dot)jiit(at)gmail(dot)com> wrote:
>
>
> Sent from my iPad
>
> On 10-Jan-2014, at 19:42, "ygnhzeus" <ygnhzeus(at)gmail(dot)com> wrote:
>
> Thanks for your reply.
> So correlation is not related to the calculation of selectivity right? If I
> force PostgreSQL not to optimize the join order (by setting
> join_collapse_limit and from_collapse_limit to 1) , is there any other
> factor that may affect the structure of execution plan regardless of the
> data access method.
>
> 2014-01-10
> ________________________________
> ygnhzeus
> ________________________________
> 发件人：Amit Langote <amitlangote09(at)gmail(dot)com>
> 发送时间：2014-01-10 22:00
> 主题：Re: [GENERAL] How to specify/mock the statistic data of tables in
> PostgreSQL
> 收件人："ygnhzeus"<ygnhzeus(at)gmail(dot)com>
> 抄送："pgsql-general"<pgsql-general(at)postgresql(dot)org>
>
>
>
> AFAIK, correlation is involved in calculation of the costs that are used for
> deciding the type of access.If the correlation is low, index scan can lead
> to quite some random reads, hence leading to higher costs.
>

Ah, I forgot to mention this point about how planner uses correlation
for access method selection.

And selectivity is a function of statistical distribution of column
values described in pg_statistic by histograms, most common values
(with their occurrence frequencies), number of distinct values, etc.
It has nothing to do with correlation.

--
Amit Langote