Re: Foreign key slows down copy/insert

От: Marko Ristola
Тема: Re: Foreign key slows down copy/insert
Дата: ,
Msg-id: 425E99C1.6050506@kolumbus.fi
(см: обсуждение, исходный текст)
Ответ на: Re: Foreign key slows down copy/insert  (Christopher Kings-Lynne)
Список: pgsql-performance

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

Foreign key slows down copy/insert  (Richard van den Berg, )
 Re: Foreign key slows down copy/insert  (Christopher Kings-Lynne, )
  Re: Foreign key slows down copy/insert  (Richard van den Berg, )
   Re: Foreign key slows down copy/insert  (Christopher Kings-Lynne, )
    Re: Foreign key slows down copy/insert  (Richard van den Berg, )
     Re: Foreign key slows down copy/insert  (Christopher Kings-Lynne, )
      Re: Foreign key slows down copy/insert  (Richard van den Berg, )
       Re: Foreign key slows down copy/insert  (Christopher Kings-Lynne, )
        Re: Foreign key slows down copy/insert  (Marko Ristola, )
      Re: Foreign key slows down copy/insert  (Tom Lane, )
   Re: Foreign key slows down copy/insert  (Stephan Szabo, )
    Re: Foreign key slows down copy/insert  (Tom Lane, )
     Re: Foreign key slows down copy/insert  (Stephan Szabo, )
 Re: Foreign key slows down copy/insert  (PFC, )
  Re: Foreign key slows down copy/insert  (Richard van den Berg, )
  Re: Foreign key slows down copy/insert  (PFC, )
   Re: Foreign key slows down copy/insert  (Richard van den Berg, )
    Re: Foreign key slows down copy/insert  (Christopher Kings-Lynne, )
     Re: Foreign key slows down copy/insert  (Richard van den Berg, )
      Re: Foreign key slows down copy/insert  (Tom Lane, )
       Re: Foreign key slows down copy/insert  (Richard van den Berg, )

About the foreign key performance:

Maybe foreign key checks could be delayed into the COMMIT phase.
In that position, you could check, that there are lots of foreign key
checks
for each foreign key pending, and do the foreign key check for an area
or for the whole table, if it is faster.

I have heard, that the database must be in consistent state after COMMIT,
but it does not have necessarily to be okay inside a transaction.

1. COMMIT wanted
2. If there are lots of foreign key checks pending, do either an area
foreign key check
(join result must be 0 rows), or a full table join.
3. If all foreign key checks are okay, complete the COMMIT operation.
4. If a foreign key check fails, go into the ROLLBACK NEEDED state.

Maybe Tom Lane meant the same.

set option delayed_foreign_keys=true;
BEGIN;
insert 1000 rows.
COMMIT;

Regards,
Marko Ristola

Christopher Kings-Lynne wrote:

>> My problem with this really is that in my database it is hard to
>> predict which inserts will be huge (and thus need FKs dissabled), so
>> I would have to code it around all inserts. Instead I can code my own
>> integirty logic and avoid using FKs all together.
>
>
> Just drop the fk and re-add it, until postgres gets more smarts.
>
> Chris
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org




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

От: "Dave Held"
Дата:
Сообщение: Re: speed of querry?
От: Alex Turner
Дата:
Сообщение: Re: Intel SRCS16 SATA raid?