Re: Proposed Query Planner TODO items

On 12 Feb, Tom Lane wrote:
> markw@osdl.org 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