Re: Proposed Query Planner TODO items

On 12 Feb, Tom Lane wrote:
> markw(at)osdl(dot)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