Re: Why does the number of rows are different in actual and estimated.

Yes, I do have a column in entity table like
setype where the values are 'Contacts', 'Candidate' etc.
I have an index on it also.
Are you suggesting to make different table for Contacts, Candidate etc.

On Fri, Dec 14, 2012 at 3:10 PM, Claudio Freire <klaussfreire(at)gmail(dot)com>wrote:

> On Fri, Dec 14, 2012 at 4:22 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > "Kevin Grittner" <kgrittn(at)mail(dot)com> 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.
>