Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

On 7 April 2016 at 12:23, Simon Riggs wrote: > For 0002 > For find_best_foreign_key_quals() how can this ever match 2 FKs with different keys? The fkrel references the foreignrel, which has a single PK. How can the FK have a different number of columns to the PK?

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

Hi, On 04/07/2016 01:23 PM, Simon Riggs wrote: On 7 April 2016 at 00:15, Tomas Vondra > wrote: Right. Fixed. 0001 committed. I added comments and a fastpath when no triggers are present. For 0002, I would be more

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

On 7 April 2016 at 00:15, Tomas Vondra wrote: > Right. Fixed. 0001 committed. I added comments and a fastpath when no triggers are present. For 0002, I would be more comfortable adding enable_fk_plans = on (true) | off even if we decided to remove that

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

Hi, attached is the patch split into two parts, as proposed by Simon. 0001 just adds the stuff to relcache, 0002 actually uses it for estimation. On 04/04/2016 12:03 PM, Amit Langote wrote: On 2016/04/04 17:25, Simon Riggs wrote: The rel cache code you're adding uses a flag called

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

On 2016/04/04 17:25, Simon Riggs wrote: > The rel cache code you're adding uses a flag called "rd_fkeyvalid" which > indicates that the relcache is correctly filled. That is confusing, since > it has nothing to do with the concept of constraint validity. We should > rename that to

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

On 3 April 2016 at 22:44, Simon Riggs wrote: > Detailed comments in the planner part of the patch. The discussion around > this patch isn't reflected enough in the patch. > I think we should record that the planner uses the constraint, even if the constraint is not yet

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

On 3 April 2016 at 22:09, Tomas Vondra wrote: > On 04/03/2016 10:06 PM, Simon Riggs wrote: > >> On 14 March 2016 at 19:42, Tomas Vondra > > wrote: >> >> ... > >> >> >> I'd like to split this into 2

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

On 04/03/2016 10:06 PM, Simon Riggs wrote: On 14 March 2016 at 19:42, Tomas Vondra > wrote: ... I'd like to split this into 2 patches 1) Add FK info to relcache 2) use FK info in planner Would the credit for this be 1)

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

On 14 March 2016 at 19:42, Tomas Vondra wrote: > Hi, > > On 03/14/2016 02:12 PM, David Steele wrote: > >> Hi Thomas, >> > ... > >> I don't think it would be clear to any reviewer which patch to apply >> even if they were working. I'm marking this "waiting for

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

Hi Simon, On 3/14/16 3:42 PM, Tomas Vondra wrote: Attached is v3 of the patch, and also three SQL scripts demonstrating the impact of the patch on simple examples. Do you know when you'll have a chance to review Tomas' latest patch? Thanks, -- -David da...@pgmasters.net -- Sent via

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

Hi, On 03/14/2016 02:12 PM, David Steele wrote: Hi Thomas, ... I don't think it would be clear to any reviewer which patch to apply even if they were working. I'm marking this "waiting for author". Yeah. Rebasing the patches to current master was simple enough (there was just a simple

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

Hi Thomas, On 2/24/16 11:21 AM, Tomas Vondra wrote: Overall, I still believe the FK patch is a clear improvement of the current status - while it's true it does not improve all possible cases and there's a room for additional improvements (like handling multiple candidate FK constraints), it

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

Hi, On 09/30/2015 03:12 AM, David Rowley wrote: ... CREATE TABLE f (id1 INT, id2 INT, PRIMARY KEY (id1, id2)); CREATE TABLE d1 (id1 INT, id2 INT, FOREIGN KEY (id1, id2) REFERENCES f(id1, id2)); CREATE TABLE d2 (id1 INT, id2 INT, FOREIGN KEY (id1, id2) REFERENCES f(id1,

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

David Rowley wrote: > I'm not sure that I agree with this being set to "Needs review". The last > progress that I see made on this was me hacking at the patch to remove some > equivalence class limitations. I think the logical next step would be for > you to look at these changes and either

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

On 12/24/2015 03:45 AM, Michael Paquier wrote: On Wed, Sep 30, 2015 at 10:12 AM, David Rowley ... In the attached I've coded it to take the Min() selectivity for when the same quals are matched more than once. I know this is not correct, but since it seems impossible to obtain an exact

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

On 24 December 2015 at 16:32, Tomas Vondra wrote: > > > On 12/24/2015 03:45 AM, Michael Paquier wrote: > >> On Wed, Sep 30, 2015 at 10:12 AM, David Rowley >> > ... > >> In the attached I've coded it to take the Min() selectivity for when the >>> same quals are

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

On Wed, Sep 30, 2015 at 10:12 AM, David Rowley wrote: > On 29 September 2015 at 01:59, Tomas Vondra > wrote: >> >> Hi, >> >> On 09/27/2015 02:00 PM, David Rowley wrote: >>> >>> I've been working on this again. I've put back the code

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

On 29 September 2015 at 01:59, Tomas Vondra wrote: > > CREATE TABLE f (id1 INT, id2 INT, PRIMARY KEY (id1, id2)); > > CREATE TABLE d1 (id1 INT, id2 INT, FOREIGN KEY (id1, id2) REFERENCES > f(id1, id2)); > CREATE TABLE d2 (id1 INT, id2 INT, FOREIGN KEY (id1, id2)

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

On 29 September 2015 at 01:59, Tomas Vondra wrote: > Hi, > > On 09/27/2015 02:00 PM, David Rowley wrote: > >> I've been working on this again. I've put back the code that you wrote >> for the looping over each combination of relations from either side of >> the

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

Hi, On 09/27/2015 02:00 PM, David Rowley wrote: I've been working on this again. I've put back the code that you wrote for the looping over each combination of relations from either side of the join. I've also added some code to get around the problem with eclass joins and the RestrictInfo

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

On 26 September 2015 at 01:57, Tomas Vondra wrote: > Hi, > > On 09/25/2015 03:39 AM, David Rowley wrote: > >> I looked at this again, and I'm not all that sure it's possible to >> > assume that 1.0 / is valid when there's more than one >> relation at either side of

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

Hi, On 09/25/2015 03:39 AM, David Rowley wrote: On 24 September 2015 at 23:57, Tomas Vondra > wrote: 2) find_best_match_foreign_key -- I think the comment before the function needs

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

Hi, Thanks for the review and time spent on reworking the patch! On 09/24/2015 07:41 AM, David Rowley wrote: On 23 September 2015 at 17:11, David Rowley > wrote: find_foreign_key_clauses() should look for the longest match

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

On 24 September 2015 at 23:57, Tomas Vondra wrote: > > 2) find_best_match_foreign_key > -- > > I think the comment before the function needs rephrasing (seems a bit > broken to me). I do like the approach in general, although it changes

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

On 23 September 2015 at 17:11, David Rowley wrote: > find_foreign_key_clauses() should look for the longest match and return a > Bitmap set of the list indexes to the caller. > It might be possible to fool the longest match logic by duplicating > clauses, e.g. a1 =

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

On 20 August 2015 at 13:49, Tomas Vondra wrote: > attached is a significantly reworked patch for using the foreign keys in > selectivity estimation. > Thanks for working a new patch, I'm starting to look at it again now: Here's my thoughts so far: + /* + * TODO

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

On Thu, Aug 20, 2015 at 11:25 AM, Tomas Vondra tomas.von...@2ndquadrant.com wrote: On 08/20/2015 03:49 AM, Tomas Vondra wrote: Then on current master, I get these estimates (showing just rows, because that's what matter): [...] Moved to next CF 2015-09. -- Michael -- Sent via

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

Hi, attached is a significantly reworked patch for using the foreign keys in selectivity estimation. The previous patch only really worked if the clauses matched the foreign key perfectly (i.e. no additional join clauses) - this patch attempts to relax those restrictions a bit. This patch

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

On 08/20/2015 03:49 AM, Tomas Vondra wrote: Then on current master, I get these estimates (showing just rows, because that's what matter): while with the patch I get this: And of course I forgot to include the plans from master, so here we go: select * from f join a on (f1 = a1 and f2 =

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

On 19 May 2015 at 11:08, Tomas Vondra tomas.von...@2ndquadrant.com wrote: On 05/17/15 14:31, David Rowley wrote: I think if you find any quals that are a part of *any* foreign key between the 2 join tables, then you should be never assume these quals to reduce the number of rows. I believe

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

Hi David, On 05/17/15 14:31, David Rowley wrote: Hi Tomas, I did glance at this patch a while back, but just thinking on it again. I think if you find any quals that are a part of *any* foreign key between the 2 join tables, then you should be never assume these quals to reduce the number of

Re: [HACKERS] PATCH: use foreign keys to improve join estimates v1

On 7 April 2015 at 13:41, Tomas Vondra tomas.von...@2ndquadrant.com wrote: (1) The current patch only does the trick when the FK matches the conditions perfectly - when there are no missing columns (present in the FK, not covered by a condition). Hi Tomas, I did glance at this