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

On 06/06/2016 07:40 PM, Tom Lane wrote: Tomas Vondra writes: On 06/06/2016 06:15 PM, Tom Lane wrote: This checks that you found a joinclause mentioning foreignrel. But foreignrel need have nothing to do with the foreign key; it could be any table in the query.

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

Tomas Vondra writes: > When it comes to improving multiple (multi-column) foreign keys, I think > it may get way more complicated that it might seem. What if the foreign > keys overlap, for example? Or what if the keys go in opposite directions > (cycle). And so

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

Tomas Vondra writes: > On 06/06/2016 06:15 PM, Tom Lane wrote: >> This checks that you found a joinclause mentioning foreignrel. But >> foreignrel need have nothing to do with the foreign key; it could be any >> table in the query. > I don't follow. How could it

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

On 06/06/2016 06:34 PM, Tom Lane wrote: ... BTW, another thought occurred to me yesterday: it seems like the existing code hasn't thought through its behavior for multiple foreign keys very carefully. That is, suppose we have both "A.J references B.K" and "A.X references B.Y", as separate FKs

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

On 06/06/2016 06:15 PM, Tom Lane wrote: Tomas Vondra writes: On 06/04/2016 08:15 PM, Tom Lane wrote: * Make RelationGetFKeyList cache a list of ForeignKeyOptInfo structs, not just constraint OIDs. It's insane that the relcache scans pg_constraint to collect

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

... BTW, another thought occurred to me yesterday: it seems like the existing code hasn't thought through its behavior for multiple foreign keys very carefully. That is, suppose we have both "A.J references B.K" and "A.X references B.Y", as separate FKs not a single multicolumn FK. The current

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

Tomas Vondra writes: > On 06/04/2016 08:15 PM, Tom Lane wrote: >> * Make RelationGetFKeyList cache a list of ForeignKeyOptInfo structs, >> not just constraint OIDs. It's insane that the relcache scans >> pg_constraint to collect those OIDs and then the planner

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

Hi, While this thread was effectively superseded by the 'new design' thread [1], I'd like to address a few points raised here, as they are relevant for the new design (at least I believe so). [1] https://www.postgresql.org/message-id/31041.1465069...@sss.pgh.pa.us On 06/04/2016 08:15 PM,

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

David Rowley writes: > I think your wires are crossed to what this patch actually does. A > unique index could only prove that no more than 1 rows exists. This > goes to prove that exactly 1 exists, then will reduce that estimate by > any other join conditions which

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

Robert Haas writes: > FYI, I spoke to Tom Lane about this at PGCon and suggested that he > look at the proposed patch as I requested in > https://www.postgresql.org/message-id/CA+TgmobPqrAVXOBMHTcpDq8hX7gCzcVhoUvC8s9V=d09+bt...@mail.gmail.com > and see whether that would

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

Robert Haas writes: > FYI, I spoke to Tom Lane about this at PGCon and suggested that he > look at the proposed patch as I requested in > https://www.postgresql.org/message-id/CA+TgmobPqrAVXOBMHTcpDq8hX7gCzcVhoUvC8s9V=d09+bt...@mail.gmail.com > and see whether that would

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

On Wed, Jun 1, 2016 at 9:29 PM, Noah Misch wrote: > This PostgreSQL 9.6 open item is past due for your status update. Kindly send > a status update within 24 hours, and include a date for your subsequent status > update. Refer to the policy on open item ownership: >

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

On 9 May 2016 at 00:24, Tomas Vondra wrote: > Hi, > > Attached is a minor revision of the patch posted by David a few days ago, > rebased on the current master (which already includes 68d704 fixing the > segfault that started this thread). > > The modifications are

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

Hi, Attached is a minor revision of the patch posted by David a few days ago, rebased on the current master (which already includes 68d704 fixing the segfault that started this thread). The modifications are fairly small: * The 'possibleRef' flag is renamed to 'use_for_estimation' which I

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

On 6 May 2016 at 01:00, Tomas Vondra 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

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

On 3 May 2016 at 16:10, Tomas Vondra wrote: > While this in itself is about a two-line fix, after reviewing >> 137805f89acb3611 I'm pretty unhappy that it got committed at all. >> I think this obvious bug is a good sign that it wasn't ready. >> Other unfinished

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

On Thu, May 5, 2016 at 10:48 AM, David Rowley wrote: > On 5 May 2016 at 16:04, David Rowley wrote: >> I've started making some improvements to this, but need to talk to >> Tomas. It's currently in the middle of his night, but will try

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

On 6 May 2016 at 02:48, David Rowley wrote: > In the attached I've left the GUC remaining. The reason for the GUC is > for testing purposes and it should be removed before release. It > should likely be documented though, even if we're planning to remove > it later.

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

Hi, On 05/05/2016 04:48 PM, David Rowley wrote: On 5 May 2016 at 16:04, David Rowley wrote: I've started making some improvements to this, but need to talk to Tomas. It's currently in the middle of his night, but will try to catch him in his morning to discuss

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

On 5 May 2016 at 16:04, David Rowley wrote: > I've started making some improvements to this, but need to talk to > Tomas. It's currently in the middle of his night, but will try to > catch him in his morning to discuss this with him. Ok, so I spoke to Tomas about

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

On 5 May 2016 at 06:54, Tom Lane wrote: > David Rowley writes: >> On 4 May 2016 at 09:18, David Rowley wrote: >>> On 4 May 2016 at 02:10, Tomas Vondra wrote: There are probably a

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

On Wed, May 4, 2016 at 2:54 PM, Tom Lane 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

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

On Wed, May 4, 2016 at 5:51 PM, Robert Haas wrote: > On Wed, May 4, 2016 at 2:54 PM, Tom Lane 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

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

On Wed, May 4, 2016 at 2:54 PM, Tom Lane 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

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

On Wed, May 4, 2016 at 5:02 PM, Tom Lane wrote: > Very good point, but unless I'm missing something, that is not what the > current patch does. I'm not sure offhand whether that's an important > estimation failure mode currently, or if it is whether it would be > sensible to

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

Hi, On 05/04/2016 11:02 PM, Tom Lane wrote: Robert Haas writes: On Wed, May 4, 2016 at 2:54 PM, Tom Lane wrote: My other design-level complaint is that basing this on foreign keys is fundamentally the wrong thing. What actually matters is the

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

Robert Haas writes: > On Wed, May 4, 2016 at 2:54 PM, Tom Lane wrote: >> My other design-level complaint is that basing this on foreign keys is >> fundamentally the wrong thing. What actually matters is the unique index >> underlying the FK; that is,

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

Hi, On 05/04/2016 08:54 PM, Tom Lane wrote: David Rowley writes: On 4 May 2016 at 09:18, David Rowley wrote: On 4 May 2016 at 02:10, Tomas Vondra wrote: There are probably a few reasonably simple

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

On Wed, May 4, 2016 at 2:54 PM, Tom Lane wrote: > My other design-level complaint is that basing this on foreign keys is > fundamentally the wrong thing. What actually matters is the unique index > underlying the FK; that is, if we have "a.x = b.y" and there's a > compatible

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

David Rowley writes: > On 4 May 2016 at 09:18, David Rowley wrote: >> On 4 May 2016 at 02:10, Tomas Vondra wrote: >>> There are probably a few reasonably simple things we could do - e.g. ignore >>> foreign

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

On 4 May 2016 at 09:18, David Rowley wrote: > On 4 May 2016 at 02:10, Tomas Vondra wrote: >> There are probably a few reasonably simple things we could do - e.g. ignore >> foreign keys with just a single column, as the primary goal of

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

On 4 May 2016 at 02:10, Tomas Vondra wrote: > There are probably a few reasonably simple things we could do - e.g. ignore > foreign keys with just a single column, as the primary goal of the patch is > improving estimates with multi-column foreign keys. I believe

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

Hi, On 05/02/2016 09:18 PM, Robert Haas wrote: On Sat, Apr 30, 2016 at 1:35 PM, Tom Lane wrote: Julien Rouhaud writes: On 29/04/2016 18:05, Tom Lane wrote: Julien Rouhaud writes: The segfault is caused by

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

Hi, On 04/30/2016 07:35 PM, Tom Lane wrote: Julien Rouhaud writes: On 29/04/2016 18:05, Tom Lane wrote: Julien Rouhaud writes: The segfault is caused by quals_match_foreign_key() calling get_leftop() and get_rightop() on a

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

On Sat, Apr 30, 2016 at 1:35 PM, Tom Lane wrote: > Julien Rouhaud writes: >> On 29/04/2016 18:05, Tom Lane wrote: >>> Julien Rouhaud writes: The segfault is caused by quals_match_foreign_key() calling get_leftop()

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

Julien Rouhaud writes: > On 29/04/2016 18:05, Tom Lane wrote: >> Julien Rouhaud writes: >>> The segfault is caused by quals_match_foreign_key() calling get_leftop() >>> and get_rightop() on a ScalarArrayOpExpr node. >>> >>> I'm not sure that

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

On 29/04/2016 18:05, Tom Lane wrote: > Julien Rouhaud writes: >> The segfault is caused by quals_match_foreign_key() calling get_leftop() >> and get_rightop() on a ScalarArrayOpExpr node. > >> Reordering the common fields of OpExpr and ScalarArrayOpExpr at the >>

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

Julien Rouhaud writes: > The segfault is caused by quals_match_foreign_key() calling get_leftop() > and get_rightop() on a ScalarArrayOpExpr node. > Reordering the common fields of OpExpr and ScalarArrayOpExpr at the > beginning of the struct so the get_*op() work with

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

On 29/04/2016 13:20, Michael Paquier wrote: > On Fri, Apr 29, 2016 at 7:25 PM, Stefan Huehner wrote: >> If you need any more info or testing done just let me know. > > The information you are providing is sufficient to reproduce the > problem, thanks! I have added this bug to

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

On Fri, Apr 29, 2016 at 7:25 PM, Stefan Huehner wrote: > If you need any more info or testing done just let me know. The information you are providing is sufficient to reproduce the problem, thanks! I have added this bug to the list of open items for 9.6. -- Michael --

[HACKERS] pg9.6 segfault using simple query (related to use fk for join estimates)

Hello, @Tomas put you in CC as it looks like related to work on fk -> join estimates i did a tiny bit of testing of our software against the nightly postgresql-9.6 debs from apt.postgresql.org Specifically against: ii postgresql-9.6