pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDelete Cascade FK speed issue
4+ messages / 4 participants
[nested] [flat]
* Delete Cascade FK speed issue
@ 2007-07-03 06:05 Patric de Waha <lists@p-dw.com>
2007-07-03 11:33 ` Re: Delete Cascade FK speed issue Michael Fuhr <mike@fuhr.org>
2007-07-03 16:01 ` Re: Delete Cascade FK speed issue Mark Lewis <mark.lewis@mir3.com>
0 siblings, 2 replies; 4+ messages in thread
From: Patric de Waha @ 2007-07-03 06:05 UTC (permalink / raw)
To: pgsql-performance
Hi,
I've dbase with about 80 relations.
On deleting a user, this cascades through all the tables.
This is very slow, for 20 users it takes 4 hours, with exclusive
access to the dbase.
No other users connected to the dbase.
Ok I know there will be somewhere a relation with a FK without
index, which
is being scanned sequentially. But how can I find out what postgres
is doing
while it is handling the transaction?
Is there a way I can find out what postgres does, and where it hangs
around, so I know
where the FK might not be indexed. (The dbase is to big to analyze
it by hand).
The way I do it now is to check the pg_locks relation, but this is
not very representative.
Is there profiling method for triggers/constraints, or a method
which gives me a hint
why it is taking so long?
thanks in advance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Delete Cascade FK speed issue
2007-07-03 06:05 Delete Cascade FK speed issue Patric de Waha <lists@p-dw.com>
@ 2007-07-03 11:33 ` Michael Fuhr <mike@fuhr.org>
2007-07-04 07:30 ` Re: Delete Cascade FK speed issue Axel Rau <Axel.Rau@Chaos1.DE>
1 sibling, 1 reply; 4+ messages in thread
From: Michael Fuhr @ 2007-07-03 11:33 UTC (permalink / raw)
To: Patric de Waha <lists@p-dw.com>; +Cc: pgsql-performance
On Tue, Jul 03, 2007 at 08:05:27AM +0200, Patric de Waha wrote:
> Is there a way I can find out what postgres does, and where it hangs
> around, so I know where the FK might not be indexed. (The dbase is
> to big to analyze it by hand).
You could query the system catalogs to look for foreign key constraints
that don't have an index on the referencing column(s). Something like
the following should work for single-column foreign keys:
select n1.nspname,
c1.relname,
a1.attname,
t.conname,
n2.nspname as fnspname,
c2.relname as frelname,
a2.attname as fattname
from pg_constraint t
join pg_attribute a1 on a1.attrelid = t.conrelid and a1.attnum = t.conkey[1]
join pg_class c1 on c1.oid = t.conrelid
join pg_namespace n1 on n1.oid = c1.relnamespace
join pg_class c2 on c2.oid = t.confrelid
join pg_namespace n2 on n2.oid = c2.relnamespace
join pg_attribute a2 on a2.attrelid = t.confrelid and a2.attnum = t.confkey[1]
where t.contype = 'f'
and not exists (
select 1
from pg_index i
where i.indrelid = t.conrelid
and i.indkey[0] = t.conkey[1]
)
order by n1.nspname,
c1.relname,
a1.attname;
--
Michael Fuhr
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Delete Cascade FK speed issue
2007-07-03 06:05 Delete Cascade FK speed issue Patric de Waha <lists@p-dw.com>
2007-07-03 11:33 ` Re: Delete Cascade FK speed issue Michael Fuhr <mike@fuhr.org>
@ 2007-07-04 07:30 ` Axel Rau <Axel.Rau@Chaos1.DE>
0 siblings, 0 replies; 4+ messages in thread
From: Axel Rau @ 2007-07-04 07:30 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-performance
Am 03.07.2007 um 13:33 schrieb Michael Fuhr:
> Something like
> the following should work for single-column foreign keys:
Nice query. Found immediately 2 missing indexes. (-;)
Axel
---------------------------------------------------------------------
Axel Rau, ☀Frankfurt , Germany +49 69 9514 18 0
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Delete Cascade FK speed issue
2007-07-03 06:05 Delete Cascade FK speed issue Patric de Waha <lists@p-dw.com>
@ 2007-07-03 16:01 ` Mark Lewis <mark.lewis@mir3.com>
1 sibling, 0 replies; 4+ messages in thread
From: Mark Lewis @ 2007-07-03 16:01 UTC (permalink / raw)
To: Patric de Waha <lists@p-dw.com>; +Cc: pgsql-performance
On Tue, 2007-07-03 at 08:05 +0200, Patric de Waha wrote:
> Hi,
> I've dbase with about 80 relations.
> On deleting a user, this cascades through all the tables.
> This is very slow, for 20 users it takes 4 hours, with exclusive
> access to the dbase.
> No other users connected to the dbase.
>
> Ok I know there will be somewhere a relation with a FK without
> index, which
> is being scanned sequentially. But how can I find out what postgres
> is doing
> while it is handling the transaction?
>
> Is there a way I can find out what postgres does, and where it hangs
> around, so I know
> where the FK might not be indexed. (The dbase is to big to analyze
> it by hand).
>
> The way I do it now is to check the pg_locks relation, but this is
> not very representative.
>
> Is there profiling method for triggers/constraints, or a method
> which gives me a hint
> why it is taking so long?
In 8.1 and later, an EXPLAIN ANALYZE of the delete will show you the
amount of time spent in each trigger. Remember that it will still
perform the delete, so if you want to be able to re-run the DELETE over
and over as you add missing indexes, run it in a transaction and
rollback each time. That will tell you which foreign key constraint
checks are taking up time. The output will not be nearly as useful if
you don't name your foreign key constraints, but is still better than
nothing.
Alternatively, you can just dump the schema to a text file and spend 30
minutes and some text searching to reconstruct your foreign key
dependency graph rooted at the table in question and check each column
for proper indexes. We recently did this for a 150 relation database,
it's not as painful as you seem to think it is. An 80 relation database
is by no means "too big to analyze" :)
-- Mark Lewis
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-07-04 07:30 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-03 06:05 Delete Cascade FK speed issue Patric de Waha <lists@p-dw.com>
2007-07-03 11:33 ` Michael Fuhr <mike@fuhr.org>
2007-07-04 07:30 ` Axel Rau <Axel.Rau@Chaos1.DE>
2007-07-03 16:01 ` Mark Lewis <mark.lewis@mir3.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox