"Pavel Stehule" <pavel.stehule@gmail.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