Re: [PERFORM] osdl-dbt3 run results - puzzled by the execution

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