Re: Delete query takes exorbitant amount of time

От: Bruno Wolff III
Тема: Re: Delete query takes exorbitant amount of time
Дата: ,
Msg-id: 20050403121820.GB27923@wolff.to
(см: обсуждение, исходный текст)
Ответ на: Re: Delete query takes exorbitant amount of time  (Karim A Nassar)
Список: pgsql-performance

Скрыть дерево обсуждения

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 Tue, Mar 29, 2005 at 01:48:48 -0700,
  Karim A Nassar <> wrote:
>
> For this FK check, there only need be one referring id to invalidate the
> delete. ISTM that for any delete with a FK reference, the index could
> always be used to search for a single value in the referring table
> (excepting very small tables). Why then must a sequential scan be
> performed in this case, and/or in general?

First the index needs to exist. It isn't created automatically because not
everyone wants such an index. Second, you need to have analyzed the
referencing table so that the planner will know it is big enough that
using an indexed search is worthwhile. The planner is getting better
about dealing with size changes without reanalyzing, but it seems there
are still some gotchas in 8.0.


В списке pgsql-performance по дате сообщения:

От: Bruno Wolff III
Дата:
Сообщение: Re: coalesce alternative
От: mark.lubratt@indeq.com
Дата:
Сообщение: Correcting Hash Join Estimates