Re: Seeing foreign key lookups in explain output

On Tuesday 20 September 2011 16:32:50 Marti Raudsepp wrote:
> On Tue, Sep 20, 2011 at 16:12, Marti Raudsepp <marti@juffo.org> wrote:
> > +1 for a TODO on adding foreign key trigger time to EXPLAIN ANALYZE
> > output.
> Sorry, that was too hasty. We already have that now in 9.0 and 9.1
> (not sure when it was introduced)
>
> create table a as select generate_series(1,10000) i;
> create table b as select generate_series(1,10000) i;
> alter table a add primary key (i);
> alter table b add foreign key (i) references a (i) on delete cascade;
> explain analyze delete from a;
>
>                                                  QUERY PLAN
>  ---------------------------------------------------------------------------
> -------------------------------- Delete  (cost=0.00..145.00 rows=10000
> width=6) (actual
> time=16.308..16.308 rows=0 loops=1)
>    ->  Seq Scan on a  (cost=0.00..145.00 rows=10000 width=6) (actual
> time=0.008..2.208 rows=10000 loops=1)
>  Trigger for constraint b_i_fkey: time=6324.652 calls=10000
>  Total runtime: 6342.406 ms
>
> Notice the line "Trigger for constraint b_i_fkey"

Ahhh I never spoted that in the changelogs, and haven't upgraded yet. Great :)
Yet another reason to upgrade ASAP. Thanks.

--
Vincent de Phily