On Fri, Dec 14, 2012 at 4:22 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > "Kevin Grittner" <kgrittn@xxxxxxxx> writes: >> AI Rumman wrote: >>> Does FK Constraint help to improve performance? Or it is only >>> for maintaining data integrity? > >> I'm not aware of any situation where adding a foreign key >> constraint would improve performance. > > There's been talk of teaching the planner to use the existence of FK > constraints to improve plans, but I don't believe any such thing is > in the code today. That made me look the code. So, eqjoinsel_inner in selfuncs.c would need those smarts. Cool. Anyway, reading the code, I think I can now spot the possible issue behind all of this. Selectivity is decided based on the number of distinct values on both sides, and the table's name "entity" makes me think it's a table that is reused for several things. That could be a problem, since that inflates distinct values, feeding misinformation to the planner. Rather than a generic "entity" table, perhaps it would be best to separate them different entities into different tables. Failing that, maybe if you have an "entity type" kind of column, you could try refining the join condition to filter by that kind, hopefully there's an index over entity kind and the planner can use more accurate MCV data. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
