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

On 04/01/2017 02:57 AM, Robert Haas wrote:
> On Fri, Mar 31, 2017 at 7:53 AM, Rafia Sabih
> <rafia.sabih@enterprisedb.com> wrote:
>> So, it looks like in the problematic area, it is not improving much.
>> Please find the attached file for the query plan of Q20 with and
>> without patch. I haven't evaluated the performance of this query with
>> patch.
> 
> Yeah, I don't think this patch is fixing whatever is going wrong with
> Q20.  It's just a problem Tomas found while investigating that issue.
> Unfortunately, I don't think we know what's really going on here yet.
> 

Yeah, it's just a side-bug. I'll look into the issue again once I get 
back home from pgconf.us, it's a little bit difficult to investigate 
this over ssh on the in-flight wifi :-/

My hunch is that the two foreign keys interfere in some strange way, 
i.e. we should apply just one and end up applying both, or something 
like that. Or perhaps it gets confused by the join-to-aggregated-rel?.

It's a bit weird, though, because the patch was originally meant to help 
with multi-column keys. It was then extended to also consider 
single-column FKs, but that should really be just 1/ndistinct anyway.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services