Re: Why does the number of rows are different in actual and estimated. - Mailing list pgsql-performance

From Claudio Freire
Subject Re: Why does the number of rows are different in actual and estimated.
Date
Msg-id CAGTBQpa4Xb6PiEtC5cDXUrwf7DOSdBxpSLgSsJw3m=ZALaJ=1A@mail.gmail.com
Whole thread Raw
In response to Re: Why does the number of rows are different in actual and estimated.  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Why does the number of rows are different in actual and estimated.
List pgsql-performance
On Fri, Dec 14, 2012 at 4:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <kgrittn@mail.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.


pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: backend suddenly becomes slow, then remains slow
Next
From: Andrew Dunstan
Date:
Subject: Re: backend suddenly becomes slow, then remains slow