Thread: 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

Manfred Koizar <mkoi-pg@aon.at> writes:
> 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;

I've been intending for some time to try to restructure the cost
estimator so that repeated indexscans can be costed more accurately.
Within the context of the heap-fetch-estimating algorithm, I think
the entire execution of a nestloop-with-inner-index-scan could probably
be treated as a single scan.  I'm not sure how we adjust the estimates
for the index-access part, though clearly those are too high as well.

This doesn't seem to be a localized change unfortunately.  Certainly
costsize.c can't do it alone.

            regards, tom lane