Re: Delete query takes exorbitant amount of time

From: Tom Lane
Subject: Re: Delete query takes exorbitant amount of time
Date: ,
Msg-id: 12831.1111785909@sss.pgh.pa.us
(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  (Simon Riggs)
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, )

Stephan Szabo <> writes:
> On Fri, 25 Mar 2005, Simon Riggs wrote:
>> Could it be that because PostgreSQL has a very highly developed sense of
>> datatype comparison that we might be taking this to extremes? Would any
>> other RDBMS consider two different datatypes to be comparable?

> We do have a broader comparable than the spec.

However, the set of comparisons that we can presently support *with
indexes* is narrower than the spec, so rejecting nonindexable cases
would be a problem.

It's worth noting also that the test being discussed checks whether the
PK index is usable for testing the RI constraint.  In the problem that
started this thread, the difficulty is lack of a usable index on the FK
column, not the PK (because that's the table that has to be searched to
do a delete in the PK table).  We cannot enforce that there be a usable
index on the FK column (since indexes on the FK table may not have been
built yet when the constraint is declared), and shouldn't anyway because
there are reasonable usage patterns where you don't need one.

            regards, tom lane


pgsql-performance by date:

From: Steve Poe
Date:
Subject: Re: How to improve db performance with $7K?
From: Christopher Kings-Lynne
Date:
Subject: Re: Delete query takes exorbitant amount of time