On Thu, Jun 1, 2017 at 8:40 AM, Tom Lane <[email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
