Re: strange cost for correlated subquery

"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
> It's strange, so correlated subqueries is faster, but it has much higher cost:

In the nestloop plan, the estimated cost for the indexscan is discounted
based on the knowledge that it'll be executed repeatedly:

> -> Index Scan using fxxx on history t2
> (cost=0.00..0.31 rows=1 width=11) (actual time=0.017..0.087 rows=40
> loops=5003)

In the subplan case that doesn't happen:

> -> Index Scan using fxxx on history (cost=0.00..8.27
> rows=1 width=4) (actual time=0.018..0.086 rows=40 loops=5003)
> Index Cond: (((product)::text = ($0)::text) AND (id <= $1))

Had the same discount been applied then the estimated costs would be
pretty nearly in line with reality, if I did the math right.

It'd be nice to do better but I'm not sure how; at the time that we
create plans for sub-queries we don't really have any way to know how
often they'll be called by the upper query.

regards, tom lane