Thanks Tom,
I've found other discussion of this, that aggregates foul-up the planner with views.
GROUP BY & DISTINCT don't work, we're trying to grab a subset of records and backfill any nulls to present a complete, single record... we're stuck with a view as this is used by a Rails app.
We'll see what else we can come-up with.
Thanks again.
On Wed, Jun 5, 2013 at 9:16 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Mike Summers <msummers57@xxxxxxxxx> writes:If you're speaking of
> Other than the tests in the original post do you have any suggestions?
http://www.postgresql.org/message-id/CAJGeMG89QbDxMab7-aPD_yXVsGx7Q=auXYM9UFVaq06cRZ4E2A@xxxxxxxxxxxxxx
that has nothing to do with cached plans, obsolete or otherwise.
You seem to be wishing that the planner would deduce "x = constant" from
"aggregate_function(x) = constant", which is wrong on its face.
AFAICS it's not even correct for the special case that the aggregate
function is first_not_null(), since you have multiple occurrences of
that in the view and there's no certainty that they all choose to return
values from the same row.
Even if the optimization is valid given some additional assumptions that
you've not told us, it's going to be a sufficiently narrow case that
I doubt we'd ever be willing to expend planner cycles on checking for it.
If you want WHERE clauses to be pushed down into this query you need to
think of some other way to define the query. Perhaps something
involving GROUP BY or DISTINCT instead of first_not_null() would be more
amenable to optimization.
regards, tom lane
