Re: LONG delete with LOTS of FK's

Larry Rosenman <ler(at)lerctr(dot)org> writes:
> Any ideas on how to figure out if we ARE getting seqscan check plans,
> and better fix it?

Try an EXPLAIN ANALYZE VERBOSE on something that just deletes one row,
and wait however long it takes. The printout should show how much time
is taken in the implementation trigger for each foreign key. That will
at least nail down which table(s) are causing problems.

A different line of thought is that the EXPLAIN I suggested in
<25119(dot)1367507317(at)sss(dot)pgh(dot)pa(dot)us> isn't an entirely accurate
representation of what a foreign-key checking query is like, because the
check queries are parameterized. You might need to do this instead:

prepare foo(referenced_column_data_type) as
select 1 from <referencing_table> where referencing_column = $1;
explain execute foo(sample_value);

and verify you get a cheap plan for each referencing table.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general