Re: oddly slow query

Jessi Berkelhammer <jberkelhammer(at)desc(dot)org> 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