Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

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