Re: [HACKERS] [PERFORM] osdl-dbt3 run results - puzzled by the execution
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
Re: [HACKERS] [PERFORM] osdl-dbt3 run results - puzzled by the execution
Manfred Koizar [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
