Re: Delete query takes exorbitant amount of time

From: Simon Riggs
Subject: Re: Delete query takes exorbitant amount of time
Date: ,
Msg-id: 1111775050.11750.790.camel@localhost.localdomain
(view: Whole thread, Raw)
In response to: Re: Delete query takes exorbitant amount of time  (Stephan Szabo)
Responses: Re: Delete query takes exorbitant amount of time  (Tom Lane)
Re: Delete query takes exorbitant amount of time  (Christopher Kings-Lynne)
List: pgsql-performance

Tree view

Delete query takes exorbitant amount of time  (Karim Nassar, )
 Re: Delete query takes exorbitant amount of time  (Tom Lane, )
  Re: Delete query takes exorbitant amount of time  (Mark Lewis, )
   Re: Delete query takes exorbitant amount of time  (Tom Lane, )
    Re: Delete query takes exorbitant amount of time  (Christopher Kings-Lynne, )
    Re: Delete query takes exorbitant amount of time  (Oleg Bartunov, )
    Re: Delete query takes exorbitant amount of time  (Mark Lewis, )
     Re: Delete query takes exorbitant amount of time  (Gaetano Mendola, )
   Re: Delete query takes exorbitant amount of time  (Christopher Kings-Lynne, )
  Re: Delete query takes exorbitant amount of time  (Karim Nassar, )
   Re: Delete query takes exorbitant amount of time  (Josh Berkus, )
    Re: Delete query takes exorbitant amount of time  (Karim Nassar, )
 Re: Delete query takes exorbitant amount of time  (Tom Lane, )
  Re: Delete query takes exorbitant amount of time  (Christopher Kings-Lynne, )
   Re: Delete query takes exorbitant amount of time  (Vivek Khera, )
   Re: Delete query takes exorbitant amount of time  (Tom Lane, )
    Re: Delete query takes exorbitant amount of time  (Simon Riggs, )
     Re: Delete query takes exorbitant amount of time  (Tom Lane, )
      Re: Delete query takes exorbitant amount of time  (Simon Riggs, )
       Re: Delete query takes exorbitant amount of time  (Stephan Szabo, )
        Re: Delete query takes exorbitant amount of time  (Simon Riggs, )
         Re: Delete query takes exorbitant amount of time  (Tom Lane, )
          Re: Delete query takes exorbitant amount of time  (Simon Riggs, )
           Re: Delete query takes exorbitant amount of time  (Stephan Szabo, )
            Re: Delete query takes exorbitant amount of time  (Tom Lane, )
             Re: Delete query takes exorbitant amount of time  (Simon Riggs, )
         Re: Delete query takes exorbitant amount of time  (Christopher Kings-Lynne, )
     Re: Delete query takes exorbitant amount of time  (Karim Nassar, )
      Re: Delete query takes exorbitant amount of time  (Stephan Szabo, )
       Re: Delete query takes exorbitant amount of time  (Karim Nassar, )
        Re: Delete query takes exorbitant amount of time  (Stephan Szabo, )
         Re: Delete query takes exorbitant amount of time  (Karim Nassar, )
          Re: Delete query takes exorbitant amount of time  (Stephan Szabo, )
           Re: Delete query takes exorbitant amount of time  (Simon Riggs, )
  Re: Delete query takes exorbitant amount of time  (Karim Nassar, )
 Re: Delete query takes exorbitant amount of time  (Tom Lane, )
 Re: Delete query takes exorbitant amount of time  (Josh Berkus, )
  Re: Delete query takes exorbitant amount of time  (Simon Riggs, )
 Re: Delete query takes exorbitant amount of time  (Stephan Szabo, )
  Re: Delete query takes exorbitant amount of time  (Karim A Nassar, )
 Re: Delete query takes exorbitant amount of time  (Simon Riggs, )
  Re: Delete query takes exorbitant amount of time  (Karim A Nassar, )
 Re: Delete query takes exorbitant amount of time  (Simon Riggs, )
  Re: Delete query takes exorbitant amount of time  (Karim A Nassar, )
   Re: Delete query takes exorbitant amount of time  (Bruno Wolff III, )
 Re: Delete query takes exorbitant amount of time  (Simon Riggs, )
  Re: Delete query takes exorbitant amount of time  (Stephan Szabo, )
   Re: Delete query takes exorbitant amount of time  (Stephan Szabo, )
    Re: Delete query takes exorbitant amount of time  (Tom Lane, )
     Re: Delete query takes exorbitant amount of time  (Simon Riggs, )
      Re: Delete query takes exorbitant amount of time  (Tom Lane, )
       Re: Delete query takes exorbitant amount of time  (Simon Riggs, )
        Re: Delete query takes exorbitant amount of time  (Stephan Szabo, )
     Re: Delete query takes exorbitant amount of time  (Stephan Szabo, )
   Re: Delete query takes exorbitant amount of time  (Tom Lane, )
   Re: Delete query takes exorbitant amount of time  (Simon Riggs, )
  Re: Delete query takes exorbitant amount of time  (Tom Lane, )
   Re: Delete query takes exorbitant amount of time  (Simon Riggs, )
    Re: Delete query takes exorbitant amount of time  (Tom Lane, )
     Re: Delete query takes exorbitant amount of time  (Simon Riggs, )

On Fri, 2005-03-25 at 08:23 -0800, Stephan Szabo wrote:
> On Fri, 25 Mar 2005, Simon Riggs wrote:
>
> > On Fri, 2005-03-25 at 10:17 -0500, Tom Lane wrote:
> > > Simon Riggs <> writes:
> > > > I vote to make this an ERROR in 8.1 - I see little benefit in allowing
> > > > this situation to continue.
> > >
> > > Other than spec compliance, you mean?  SQL99 says
> > >
> > >             ... The declared type of each referencing column shall be
> > >             comparable to the declared type of the corresponding referenced
> > >             column.
> > >
> > > It doesn't say that it has to be indexable, and most definitely not that
> > > there has to be an index.
> >
> > specs at dawn, eh?
> >
> > Well, SQL:2003 Foundation, p.550 clause 3a) states that the the
> > <reference columns> in the referencing table must match a unique
> > constraint on the referenced table, or the PRIMARY KEY if the columns
> > are not specified. Either way, the referenced columns are a unique
> > constraint (which makes perfect sense from a logical data perspective).
> >
> > We implement unique constraints via an index, so for PostgreSQL the
> > clause implies that it must refer to an index.
>
> IMHO, that reference is irrrelevant.

Tom had said SQL99 required this; I have pointed out SQL:2003, which
supercedes the SQL99 standard, does not require this.

Leading us back to my original point - what is the benefit of continuing
with having a WARNING when that leads people into trouble later?

> Yes, there must be an index due to
> our implementation, however that doesn't imply that the types must be the
> same

No, it doesn't imply it, but what benefit do you see from the
interpretation that they are allowed to differ? That interpretation
currently leads to many mistakes leading to poor performance.

There is clear benefit from forcing them to be the same. In logical data
terms, they *should* be the same. I don't check fruit.apple_grade
against fruit_type.orange_grade. When would I want to make a check of
that nature? If there is a reason, thats great, lets keep status quo
then.

I respect the effort and thought that has already gone into the
implementation; I seek only to offer a very minor improvement based upon
recent list issues.

> nor even that the index must be usable for the cross table
> comparison.

Thats a separate discussion, possibly the next one.

Best Regards, Simon Riggs





pgsql-performance by date:

From: Steve Poe
Date:
Subject: How to improve db performance with $7K?
From: Tom Lane
Date:
Subject: Re: How to improve db performance with $7K?