Thread: View's plan not taking advantage of WHERE?

On Wed, Jun 5, 2013 at 5:31 AM, Mike Summers <msummers57@gmail.com> wrote:
> It appears that the culprit is a cached query plan, the tables in the UNION
> have changed and no long match however the View does not throw a "each UNION
> query must have the same number of columns" error.
>
> Is there a way to force the View's query plan to be updated on each access?

In postgresql, a view is simply a re-write driven by a rule. So when
you use it it always creates a new plan. Are you sure you don't have >
1 view def in another schema or something?

On Wed, Jun 5, 2013 at 6:01 AM, Mike Summers <msummers57@gmail.com> wrote:
> From what I'm reading the View is frozen when it's created, including it's
> plan, and the usual solution is to use a set returning function... is this
> not true?

No it is not.  Here:

smarlowe=# create table a (id int);
CREATE TABLE
smarlowe=# create index a_id on a(id);
CREATE INDEX
smarlowe=# insert into a values (1),(2),(3);
INSERT 0 3
smarlowe=# create view x as select * from a;
CREATE VIEW

smarlowe=# analyze a;
ANALYZE
smarlowe=# show enable_seqscan;
 enable_seqscan
----------------
 on
(1 row)
smarlowe=# explain select * from x where id=1;
                   QUERY PLAN
-------------------------------------------------
 Seq Scan on a  (cost=0.00..1.04 rows=1 width=4)
   Filter: (id = 1)
(2 rows)

smarlowe=# set enable_seqscan =off;
SET
smarlowe=# explain select * from x where id=1;
                          QUERY PLAN
--------------------------------------------------------------
 Index Scan using a_id on a  (cost=0.00..8.27 rows=1 width=4)
   Index Cond: (id = 1)
(2 rows)

smarlowe=#

> I've double checked all schemas and the view is only defined once.

Well you're gonna have to come up with some kind of self-contained
test to show what's happening then.

Note that the view DEFINITION is frozen, the query PLAN is NOT.

Mike Summers <msummers57@gmail.com> writes:
> Other than the tests in the original post do you have any suggestions?

If you're speaking of
http://www.postgresql.org/message-id/CAJGeMG89QbDxMab7-aPD_yXVsGx7Q=auXYM9UFVaq06cRZ4E2A@mail.gmail.com
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