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

On Wed, May 4, 2016 at 5:51 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, May 4, 2016 at 2:54 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I spent some time trying to make a test case that was impossibly slow,
>> without any really impressive result: I saw at most about a 25% growth in
>> planning time, for a 27-way join with one or two foreign keys per table.
>> I noted however that with a simple FROM list of tables, you don't really
>> get the full force of the combinatorial explosion, because
>> join_search_one_level prefers to generate left-deep trees first, and so
>> the first creation of a given joinrel is always N left-side rels against 1
>> right-side rel, causing the second level of looping to always iterate just
>> once. (GEQO behaves likewise, I think.) I spent a little time trying to
>> devise join order constraints that would result in a lot of high-level
>> joinrels being formed with many relations on both sides, which would cause
>> both of the second and third levels to iterate O(N) times not just once.
>> I didn't have much luck, but I have zero confidence that our users won't
>> find such cases.
>
> I'

Well, that wasn't my best-ever email to the list. Sigh.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company