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(dot)sabih(at)enterprisedb(dot)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