Thread: postgres optimizer
Hi: I am working on postgres optimizer code to add some statistics collection features for an academic project. At some point in my work, I need to be able to tell if a join is a foreign key join or not. Also in case of a foreign key join, I need to identify which input to the join operator has the primary key attribute and which one has teh foreign key. In other words, when we are making a JoinPath struct, I need to identify if the join between the inner and outer RelOptInfo structs is a foreign key join and if so, which RelOptInfo has the primary key. Does anyone know the best way to do this? Should I go to "joinrestrictinfo" field and go over all expressions? Is there any easier way? Regards, HSA
"Hossein S. Attar" <mhsheikh@softbase.math.uwaterloo.ca> writes:
> I am working on postgres optimizer code to add some statistics
> collection features for an academic project. At some point in my work, I
> need to be able to tell if a join is a foreign key join or not. Also in
> case of a foreign key join, I need to identify which input to the join
> operator has the primary key attribute and which one has teh foreign
> key. In other words, when we are making a JoinPath struct, I need to
> identify if the join between the inner and outer RelOptInfo structs is a
> foreign key join and if so, which RelOptInfo has the primary key. Does
> anyone know the best way to do this? Should I go to "joinrestrictinfo"
> field and go over all expressions? Is there any easier way?
The planner doesn't think there is any such thing as a "foreign key
join". Perhaps you should modify the foreign key triggers (in
ri_triggers.c) to collect the information you need.
regards, tom lane
Tom Lane wrote: > "Hossein S. Attar" <mhsheikh@softbase.math.uwaterloo.ca> writes: > >>I am working on postgres optimizer code to add some statistics >>collection features for an academic project. At some point in my work, I >>need to be able to tell if a join is a foreign key join or not. Also in >>case of a foreign key join, I need to identify which input to the join >>operator has the primary key attribute and which one has teh foreign >>key. In other words, when we are making a JoinPath struct, I need to >>identify if the join between the inner and outer RelOptInfo structs is a >>foreign key join and if so, which RelOptInfo has the primary key. Does >>anyone know the best way to do this? Should I go to "joinrestrictinfo" >>field and go over all expressions? Is there any easier way? I would be interested in knowing "why" you want to do such a thing? What is it you expect to gain? Sincerely, Joshua D. Drake > > > The planner doesn't think there is any such thing as a "foreign key > join". Perhaps you should modify the foreign key triggers (in > ri_triggers.c) to collect the information you need. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
Like I said it's a research project. There is a feature that I'm trying to add to optimizer, but this feature should only be used when a join is a foreign key join. Hossein Joshua D. Drake wrote: > > I would be interested in knowing "why" you want to do such a thing? What > is it you expect to gain? > > Sincerely, > > Joshua D. Drake > >> >> >> The planner doesn't think there is any such thing as a "foreign key >> join". Perhaps you should modify the foreign key triggers (in >> ri_triggers.c) to collect the information you need. >> >> regards, tom lane >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 4: Have you searched our list archives? >> >> http://archives.postgresql.org > > >