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

Peter Geoghegan <pg@bowt.ie> writes:
> 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?:

Uh, no.  I don't think we want to add any run-time checks.  The point in
this example is that we'd get a better rowcount estimate if we noticed
that the FK constraint could be considered while estimating the size of
the partsupp-to-aggregated-subquery join.

> Apparently selectivity estimation isn't particularly challenging with
> the TPC-H queries.

Maybe not with the rest of them, but we're certainly having an issue there
with Q20.
        regards, tom lane