From: | "Eric B(dot) Ridge" <ebr(at)tcdi(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Postgresql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Query plans for plpgsql triggers |
Date: | 2006-03-25 05:10:40 |
Message-ID: | E8C2A9EF-12AD-4245-B130-F51BD570710A@tcdi.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-03-25 05:24:04 | Re: Query plans for plpgsql triggers |
Previous Message | Tom Lane | 2006-03-25 04:39:40 | Re: Query plans for plpgsql triggers |