On 12 Feb, Tom Lane wrote: > [EMAIL PROTECTED] writes: >> Ok, I have EXPLAIN ANALYZE results for both the power and throughput >> tests: >> http://developer.osdl.org/markw/dbt3-pgsql/ > > Thanks. I just looked at Q9 and Q21, since those are the slowest > queries according to your chart. (Are all the queries weighted the same > for evaluation purposes, or are some more important than others?) > [snip] > > The estimate for the part/partsupp join is close enough (60K vs 90K > rows), but why is it estimating 92 rows out of the join to lineitem when > the true figure is 681518? With a more accurate estimate the planner > would probably have chosen different join methods above this point. > > Can you show us the pg_stats rows for the columns p_partkey, l_partkey, > ps_suppkey, and l_suppkey? > > It would also be interesting to see whether a better estimate emerges > if you increase default_statistics_target (try 100 or so).
http://developer.osdl.org/markw/dbt3-pgsql/62/ This run changes default_statistics_target to 1000 and I have p_partkey, l_partkey, ps_suppkey, and l_suppkey pg_stats here at 1 min intervals http (no links on the web page.) Pretty significant performance change. Power: http://developer.osdl.org/markw/dbt3-pgsql/62/db_stat/power.l_partkey.out http://developer.osdl.org/markw/dbt3-pgsql/62/db_stat/power.l_suppkey.out http://developer.osdl.org/markw/dbt3-pgsql/62/db_stat/power.p_partkey.out http://developer.osdl.org/markw/dbt3-pgsql/62/db_stat/power.ps_suppkey.out Throughput: http://developer.osdl.org/markw/dbt3-pgsql/62/db_stat/thuput.l_partkey.out http://developer.osdl.org/markw/dbt3-pgsql/62/db_stat/thuput.l_suppkey.out http://developer.osdl.org/markw/dbt3-pgsql/62/db_stat/thuput.p_partkey.out http://developer.osdl.org/markw/dbt3-pgsql/62/db_stat/thuput.ps_suppkey.out Something went wrong when I tried to run another test with the Q21 changes overnight, so I'll have to get back to you on that one. Mark ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
