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

2003-09-24 Thread Manfred Koizar
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) >>

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

2003-09-19 Thread Jenny Zhang
On Fri, 2003-09-19 at 06:12, Greg Stark wrote: > Tom Lane <[EMAIL PROTECTED]> writes: > > > I think this is a pipe dream. Variation in where the data gets laid > > down on your disk drive would alone create more than that kind of delta. > > I'm frankly amazed you could get repeatability within 2-

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

2003-09-19 Thread Jenny Zhang
On Thu, 2003-09-18 at 20:20, Tom Lane wrote: > Jenny Zhang <[EMAIL PROTECTED]> writes: > > ... It seems to me that small > > effective_cache_size favors the choice of nested loop joins (NLJ) > > while the big effective_cache_size is in favor of merge joins (MJ). > > No, I wouldn't think that,

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

2003-09-19 Thread Jenny Zhang
I posted more results as you requested: On Fri, 2003-09-19 at 08:08, Manfred Koizar wrote: > On Thu, 18 Sep 2003 15:36:50 -0700, Jenny Zhang <[EMAIL PROTECTED]> > wrote: > >We thought the large effective_cache_size should lead us to better > >plans. But we found the opposite. > > The common str

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

2003-09-19 Thread Manfred Koizar
On Thu, 18 Sep 2003 15:36:50 -0700, Jenny Zhang <[EMAIL PROTECTED]> wrote: >We thought the large effective_cache_size should lead us to better >plans. But we found the opposite. The common structure of your query plans is: Sort Sort Key: sum((partsupp.ps_supplycost * partsupp.ps_availqty))

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

2003-09-19 Thread Greg Stark
Tom Lane <[EMAIL PROTECTED]> writes: > I think this is a pipe dream. Variation in where the data gets laid > down on your disk drive would alone create more than that kind of delta. > I'm frankly amazed you could get repeatability within 2-3%. I think the reason he gets good repeatability is be

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

2003-09-19 Thread Matt Clark
> I put the EXPLAIN ANALYZE output at: > http://developer.osdl.org/~jenny/large_explain_analyze > http://developer.osdl.org/~jenny/small_explain_analyze > The actual execution time is 37 seconds(large) vs 5 seconds (small). > There's an obvious row count misestimation in the 'large' plan: -> So

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

2003-09-18 Thread Tom Lane
Jenny Zhang <[EMAIL PROTECTED]> writes: > ... It seems to me that small > effective_cache_size favors the choice of nested loop joins (NLJ) > while the big effective_cache_size is in favor of merge joins (MJ). No, I wouldn't think that, because a nestloop plan will involve repeated fetches of

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

2003-09-18 Thread Jenny Zhang
Thanks for your prompt reply. On Thu, 2003-09-18 at 16:19, Matt Clark wrote: > > We thought the large effective_cache_size should lead us to better > > plans. But we found the opposite. > > Maybe it's inappropriate for little old me to jump in here, but the plan > isn't usually that important com

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

2003-09-18 Thread Matt Clark
> We thought the large effective_cache_size should lead us to better > plans. But we found the opposite. Maybe it's inappropriate for little old me to jump in here, but the plan isn't usually that important compared to the actual runtime. The links you give show the output of 'explain' but not 'e