Re: pg9.6 segfault using simple query (related to use fk for join estimates)

On 6 May 2016 at 01:00, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:

> I think we can further limit the impact by ignoring foreign keys on a
> single column, because the primary goal of the patch is improving estimates
> with multi-column FKs (and matching joins). I'd argue that 99% of the FKs
> in practice is single-column ones, but sure - if you have a database with
> many multi-column FKs this won't help.
>
> I think it's also important to point out that whatever solution we choose,
> it should probably allow us to relax some of the restrictions in the
> future. For example, with a FK on 3 columns, the current patch only handles
> a "full match" joins, with conditions on all three columns. But it may be
> possible to also improve estimates on just two columns - the current patch
> does not do that, as that needs definitely further more thought and
> discussion.
>

The context of the patch is important. If we can save this for 9.6, we
should.

Multi-column joins are currently badly estimated. Multi-variate States
would be useful here also, but I chose not to commit that patch since it
was much larger. The current patch is small, isolated and yet very
effective in solving the estimation problem in some cases. Committing and
possibly reverting the feature was/is possible without collateral damage.

The current patch works only for multi-column joins, matching them against
multi-column FKs. I see no reason why that should give a massively negative
performance because that is a small subset of cases; I did test that to
check for regressions, but its possible I missed something that does cause
additional run-time in real world cases.

Clearly, an optimizer test suite would be helpful in analyzing the effect
of new patches.

I'll continue to look at this and comment further.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services