Re: oddly slow query

Jessi Berkelhammer <jberkelhammer(at)desc(dot)org> writes:
> Tom Lane wrote:
>> For some reason it's estimating only one row out of the
>> clinical_reg_current view will satisfy the
>> tier_program(benefit_type_code) = 'SAGE' constraint.

My math was off the other day --- actually, that's exactly what you'd
expect for the default estimate on an equality condition it has no stats
for, when the underlying scan is estimated to have only 117 rows. So
either you should do something about getting that underlying estimate
up closer to reality (perhaps increasing default_statistics_target would
improve matters?), or you need to fix things so that the planner can
apply its statistics to estimating what is happening with the
tier_program constraint. Expressed as a function this way, it's just a
black box to the planner so you get a default estimate. Given that the
function is just extracting from a table, I think you could remove the
function call and express the condition with a join instead, and that
might result in a better estimate.

>> I'm also wondering why the function call isn't getting pushed down
>> further into the plan --- what's the definition of that view look like?

> View definition:
> SELECT DISTINCT ON (clinical_reg.client_id)

Ah, it's the DISTINCT ON that's preventing any better optimization.
Not much to be done about that, unless you can recast things to not
need DISTINCT ON, which looks a bit hard.

regards, tom lane