On Thu, Jun 1, 2017 at 8:40 AM, Tom Lane <t...@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_suppkey agg_suppkey from lineitem /* BEGIN my addition */ where exists ( 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_name like '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 -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers