Re: Erroneous cost estimation for nested loop join

On 9 November 2015 at 10:08, <kawamichi(at)tkl(dot)iis(dot)u-tokyo(dot)ac(dot)jp> wrote:

>
> We guessed the cause of this error would be in the cost model of Postgres,
> and investigated the source code of optimizer, and we found the cause of
> this problem. It was in the index cost estimation process. On scanning
> inner table, if loop count is greater than 1, its I/O cost is counted as
> random access. In the case of Query2, in one loop (i.e. one inner table
> scan) , much data is read sequentially with clustered index, so it seems to
> be wrong that optimizer thinks its I/O workload is random access.
>

We don't have a clustered index in Postgres. We do store correlation stats
for the index, which is used in some places to reduce cost.

Could you look some more at this and see if a model change that uses the
correlation can improve this?

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services