On Thu, Jun 1, 2017 at 8:40 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The thing that would actually have a chance of improving matters for Q20
> would be if we could see our way to looking through the aggregation
> subquery and applying the foreign key constraint for lineitem. That
> seems like a research project though; it's surely not happening for v10.
Do you mean teaching the optimizer to do something like this?:
select ps_suppkey
from partsupp, ( select l_partkey agg_partkey,
l_suppkeyagg_suppkey from lineitem /* BEGIN my addition */
whereexists ( select p_partkey from part
where p_name like 'hot%' and p_partkey = l_partkey
) /* END my addition */ group by l_partkey, l_suppkey
) agg_lineitem
where agg_partkey = ps_partkey and agg_suppkey = ps_suppkey and ps_partkey in ( select
p_partkey from part where
p_namelike 'hot%' );
Note that I introduced a new, redundant exists() in the agg_lineitem
fact table subquery. It now takes 23 seconds for me on Tomas' 10GB
TPC-H dataset, whereas the original query took over 90 minutes.
Clearly we're missing a trick or two here. I think that you need a
DAG-shaped query plan to make this work well, though, so it is
certainly a big project.
Apparently selectivity estimation isn't particularly challenging with
the TPC-H queries. I think that the big challenge for us is
limitations like this; there are similar issues with a number of other
TPC-H queries. It would be great if someone looked into implementing
bitmap semi-join.
--
Peter Geoghegan