On Fri, 19 Sep 2003 11:35:35 -0700, Jenny Zhang <jenny@osdl.org>
wrote:
>I posted more results as you requested:
Unfortunately they only confirm what I suspected earlier:
>> 2) -> Index Scan using i_ps_suppkey on partsupp
>> (cost=0.00..323.16 rows=80 width=34)
>> (actual time=0.16..2.98 rows=80 loops=380)
>> ctr=108.44
>> the planner does not
>> account for additional index scans hitting pages in the cache that
>> have been brought in by preceding scans. This is a known problem
PF1 = estimated number of page fetches for one loop ~ 320
L = estimated number of loops ~ 400
P = number of pages in relation ~ 21000
Cutting down the number of heap page fetches if PF1 * L > P and P <
effective_cache_size seems like an obvious improvement, but I was not
able to figure out where to make this change. Maybe it belongs into
costsize.c near
run_cost += outer_path_rows *
(inner_path->total_cost - inner_path->startup_cost) *
joininfactor;
in cost_nestloop() or it should be pushed into the index cost
estimation functions. Hackers?
For now you have to keep lying about effective_cache_size to make the
planner overestimate merge joins to compensate for the planner's
overestimation of nested loops. Sorry for having no better answer.
Servus
Manfred