Jessi Berkelhammer <jberkelhammer@xxxxxxxx> writes: > Here are the 3 EXPLAIN ANALYZE commands followed by the output: Well, here's the problem: > Join Filter: (clinical_reg_current.client_id = client.client_id) > -> Subquery Scan clinical_reg_current (cost=754.36..758.23 > rows=1 width=4) (actual time=57.359..146.717 rows=684 loops=1) > Filter: (tier_program(benefit_type_code) = 'SAGE'::text) > -> Unique (cost=754.36..756.47 rows=117 width=211) > (actual time=56.427..67.998 rows=1000 loops=1) 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. This causes it to think a nestloop join to the client view would be a good idea. The same estimation error is present in your example with the function and no join, but it doesn't hurt anything because there are no planning decisions that depend on the estimate in that case. The estimate of the view's rowcount without the filter isn't that great either (117 vs 1000 actual) but it's not wrong enough to prompt selection of a bad plan choice. There's something funny going on with the estimation of the function's selectivity --- does the expression "tier_program(benefit_type_code)" match an index, perhaps? If so, have you updated stats for that table lately? 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? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
