On Fri, 19 Sep 2003 11:35:35 -0700, Jenny Zhang <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings