Re: osdl-dbt3 run results - puzzled by the execution

On Fri, 19 Sep 2003 11:35:35 -0700, Jenny Zhang <jenny(at)osdl(dot)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