Jessi Berkelhammer <jberkelhammer@xxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
