Thread: interesting trigger behaviour in 8.3

Hello,

We came accross interesting behaviour of the update statement inside
an after insert or update trigger in PostgreSQL 8.3.1. Briefly, the
update run within one line trigger function takes always 1.5 sec
whereas exactly the same update hitting the same rows takes always 1ms
if run from the psql terminal.

In pseudo code it looks like the following. There are 2 tables, empty
abstract_table with 3 columns (id, col1, col2) and many tables (e.g.
inherited_table1_with_data) that inherit abstract_table.
Constraint_exclusion is set up on id column and works perfectly. So
we've got update like this

UPDATE abstract_table SET col1 = 1, col2 = 2 WHERE id = 12345;

that takes too long when run from trigger (fired on any third table)
and takes 1ms if run literally with all the same constants from
terminal. However there's one more issue with that. If we change
update within trigger to eliminate constraint_exclusion check and
point it directly to child table with data:

UPDATE inherited_table1_with_data SET col1 = 1, col2 = 2 WHERE id = 12345;

trigger works perfectly doing everything for 1ms as in terminal.

Any clues? Can anybody suggest how to debug this? Is it possible to
get an explain of the query within the trigger?

--
Regards,
 Ivan

On Tue, 2008-07-29 at 19:25 +0400, Ivan Zolotukhin wrote:
> Any clues? Can anybody suggest how to debug this? Is it possible to
> get an explain of the query within the trigger?

I bet it's the difference between prepared/not prepared plans. The
trigger prepares the plan without considering the actual parameter
values, on the psql prompt you give the parameter values explicitly in
the sql. Try to use the PREPARE command to prepare the plan on the psql
prompt, and EXPLAIN EXECUTE it to see how it works in the trigger...

Cheers,
Csaba.

"Ivan Zolotukhin" <ivan.zolotukhin@gmail.com> writes:
> In pseudo code it looks like the following. There are 2 tables, empty
> abstract_table with 3 columns (id, col1, col2) and many tables (e.g.
> inherited_table1_with_data) that inherit abstract_table.
> Constraint_exclusion is set up on id column and works perfectly. So
> we've got update like this

> UPDATE abstract_table SET col1 = 1, col2 = 2 WHERE id = 12345;

I bet it does not *really* look like that, but has a parameterized
WHERE clause.  As per the fine manual:

    Constraint exclusion only works when the query's WHERE clause
    contains constants. A parameterized query will not be optimized,
    since the planner cannot know which partitions the parameter value
    might select at run time. For the same reason, "stable" functions
    such as CURRENT_DATE must be avoided.


            regards, tom lane

On Tue, Jul 29, 2008 at 7:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Ivan Zolotukhin" <ivan.zolotukhin@gmail.com> writes:
>> In pseudo code it looks like the following. There are 2 tables, empty
>> abstract_table with 3 columns (id, col1, col2) and many tables (e.g.
>> inherited_table1_with_data) that inherit abstract_table.
>> Constraint_exclusion is set up on id column and works perfectly. So
>> we've got update like this
>
>> UPDATE abstract_table SET col1 = 1, col2 = 2 WHERE id = 12345;
>
> I bet it does not *really* look like that, but has a parameterized
> WHERE clause.  As per the fine manual:
>
>    Constraint exclusion only works when the query's WHERE clause
>    contains constants. A parameterized query will not be optimized,
>    since the planner cannot know which partitions the parameter value
>    might select at run time. For the same reason, "stable" functions
>    such as CURRENT_DATE must be avoided.

Thank you Tom for your remark. I just missed this point from the docs.

--
Regards,
 Ivan