pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Constraint question
5+ messages / 3 participants
[nested] [flat]

* Constraint question
@ 2006-05-15 02:19  Enzo Daddario <enzo@pienetworks.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Enzo Daddario @ 2006-05-15 02:19 UTC (permalink / raw)
  To: pgsql-admin

Hi All,

I am currently using Postgres 8.1.2 and am in the process
of restructuring our entire DB.

The current structure - table "X" is the central/main table in the DB
and has approx 100 tables which reference it in the form of foreign key
constaints.

I am now required the remove thousands of obsolete records from table
"X" and even though I have removed all related rows in all related
tables, the deletion of rows in table "X" is taking WAY TOO LONG. I am
assuming that this is because the deletion of each row in table "X"
requires that all the related tables(a number of them are huge) need to
be first checked.

Is there a way I can temporarily disable this checking and then
re-enable it after the rows in table "X" are deleted.

Thanks ENZO...




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Constraint question
@ 2006-05-15 02:50  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Enzo Daddario <enzo@pienetworks.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2006-05-15 02:50 UTC (permalink / raw)
  To: Enzo Daddario <enzo@pienetworks.com>; +Cc: pgsql-admin

Enzo Daddario <enzo@pienetworks.com> writes:
> I am now required the remove thousands of obsolete records from table
> "X" and even though I have removed all related rows in all related
> tables, the deletion of rows in table "X" is taking WAY TOO LONG.

You need to put indexes on the referencing columns.  Then start a fresh
session for doing the actual deletes (FK check plans are usually cached
per-session).

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Constraint question
@ 2006-05-15 15:12  Jim C. Nasby <jnasby@pervasive.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Jim C. Nasby @ 2006-05-15 15:12 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Enzo Daddario <enzo@pienetworks.com>; pgsql-admin

On Sun, May 14, 2006 at 10:50:50PM -0400, Tom Lane wrote:
> Enzo Daddario <enzo@pienetworks.com> writes:
> > I am now required the remove thousands of obsolete records from table
> > "X" and even though I have removed all related rows in all related
> > tables, the deletion of rows in table "X" is taking WAY TOO LONG.
> 
> You need to put indexes on the referencing columns.  Then start a fresh
> session for doing the actual deletes (FK check plans are usually cached
> per-session).

Would SET CONSTRAINTS ... DEFERRED not help, or does it still use the
same machinery to do the checking, regardless of how much data there is
to check?
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Constraint question
@ 2006-05-15 15:17  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2006-05-15 15:17 UTC (permalink / raw)
  To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Enzo Daddario <enzo@pienetworks.com>; pgsql-admin

"Jim C. Nasby" <jnasby@pervasive.com> writes:
> On Sun, May 14, 2006 at 10:50:50PM -0400, Tom Lane wrote:
>> Enzo Daddario <enzo@pienetworks.com> writes:
>>> I am now required the remove thousands of obsolete records from table
>>> "X" and even though I have removed all related rows in all related
>>> tables, the deletion of rows in table "X" is taking WAY TOO LONG.
>> 
>> You need to put indexes on the referencing columns.  Then start a fresh
>> session for doing the actual deletes (FK check plans are usually cached
>> per-session).

> Would SET CONSTRAINTS ... DEFERRED not help, or does it still use the
> same machinery to do the checking, regardless of how much data there is
> to check?

It's the same machinery.  We've speculated about having the thing switch
over to doing a full-table recheck (comparable to what ADD CONSTRAINT
FOREIGN KEY does) once the number of pending individual row checks
exceeds some threshold, but that's not done yet --- and it's not clear
how to do it in a concurrent fashion, so don't hold your breath ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Constraint question
@ 2006-05-15 15:53  Jim C. Nasby <jnasby@pervasive.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Jim C. Nasby @ 2006-05-15 15:53 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Enzo Daddario <enzo@pienetworks.com>; pgsql-admin

On Mon, May 15, 2006 at 11:17:31AM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jnasby@pervasive.com> writes:
> > On Sun, May 14, 2006 at 10:50:50PM -0400, Tom Lane wrote:
> >> Enzo Daddario <enzo@pienetworks.com> writes:
> >>> I am now required the remove thousands of obsolete records from table
> >>> "X" and even though I have removed all related rows in all related
> >>> tables, the deletion of rows in table "X" is taking WAY TOO LONG.
> >> 
> >> You need to put indexes on the referencing columns.  Then start a fresh
> >> session for doing the actual deletes (FK check plans are usually cached
> >> per-session).
> 
> > Would SET CONSTRAINTS ... DEFERRED not help, or does it still use the
> > same machinery to do the checking, regardless of how much data there is
> > to check?
> 
> It's the same machinery.  We've speculated about having the thing switch
> over to doing a full-table recheck (comparable to what ADD CONSTRAINT
> FOREIGN KEY does) once the number of pending individual row checks
> exceeds some threshold, but that's not done yet --- and it's not clear
> how to do it in a concurrent fashion, so don't hold your breath ...

I'm assuming that in order for deferred constraints to work we must have
a list of what's changed... couldn't that list be fed into an
appropriate query and then planned accordingly? This would allow the
backend to use the best validation method possible; merge join, hash
join, etc. If the number of changed rows is small, the planner would
probably pick nested loop (I'm assuming that's roughly how the IMMEDIATE
case works); if the number of changed rows is large it'd favor something
else, but at least it wouldn't be re-checking the entire source table.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2006-05-15 15:53 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-15 02:19 Constraint question Enzo Daddario <enzo@pienetworks.com>
2006-05-15 02:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-05-15 15:12   ` Jim C. Nasby <jnasby@pervasive.com>
2006-05-15 15:17     ` Tom Lane <tgl@sss.pgh.pa.us>
2006-05-15 15:53       ` Jim C. Nasby <jnasby@pervasive.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