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
