Re: Seeing foreign key lookups in explain output

On Tue, Sep 20, 2011 at 16:12, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
> On Tue, Sep 20, 2011 at 15:35, Vincent de Phily
> <vincent(dot)dephily(at)mobile-devices(dot)fr> wrote:
>> The explain output will tell me it's using the index on t1's id, but it tells
>> me nothing about the seqscan that happens on t2 (because I forgot to add an
>> index on t2.ref).
>
> +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"

Regards,
Marti