Re: Query plans for plpgsql triggers

On Mar 24, 2006, at 11:39 PM, Tom Lane wrote:

> The issue is probably that the planner is seeing a parameterized
> query. Try this:
>
> prepare foo(int8) as update some_other_table SET field = 'value'
> WHERE id = $1;
> explain execute foo(42);

I should have mentioned that while the UPDATE statement in the
trigger function really is as simple as the above, "some_other_table"
is actually a view with the requisite ON UPDATE DO INSTEAD rule:

CREATE OR REPLACE RULE some_other_table_update AS ON UPDATE TO
some_other_real_table DO INSTEAD
(
UPDATE some_other_real_table_1 SET field = NEW.field WHERE id =
OLD.id::int8;
UPDATE some_other_real_table_2 SET field = NEW.field WHERE id =
OLD.id::int8;
...
UPDATE some_other_real_table_39 SET field = NEW.field WHERE id =
OLD.id::int8;
UPDATE some_other_real_table SET field = NEW.field WHERE id =
OLD.id::int8;
);

The explain for your "prepare foo(42)" suggestion shows the correct
index scans for each of the 40 actual tables being updated by the RULE.

> and see what plan you get. If the id field has sufficiently
> discouraging statistics then the planner may think that a seqscan
> is the safest plan. In a "normal" query where you're comparing id
> to a constant, the planner can see whether the constant matches any
> of the most common values for the column --- if it doesn't then an
> indexscan is a good plan.

the "id" column, for *each* of the tables referenced in the RULE is
defined as
id int8 NOT NULL PRIMARY KEY

No value should be any more common than the other.

Could the fact that "some_other_table" is a view influence the
planner in some way?

> If you really want a replan every time, you can get it by using
> EXECUTE.

Indeed. If big-ugly-updateable-views can't influence the planner,
what positive impact would changing the statistics threshold have on
a primary key column?

As an aside, has there ever been any discussion/thought into some
ability to force all plpgsql queries to by dynamically planned w/o
the need to explicitly wrap them inside EXPLAIN? Maybe something like:

CREATE OR REPLACE FUNCTION foo() LANGUAGE 'plpgsql' OPTIONS
'dynamic_plans=on' AS '....';

or maybe a plpgsql, named 'plpgsql_dont_preplan_my_queries'?

Something like the above would at least make for "prettier" function
sources.

thanks for your time.

eric