Re: Very slow joins

On Sat, Jul 25, 2009 at 8:45 AM, Sam Mason<sam@samason.me.uk> wrote:
> On Sat, Jul 25, 2009 at 02:36:19AM -0700, MS wrote:
>> I believe the update took so long because pgsql was checking if the
>> changes don't break the referential integrity.
>> So - problem solved, postgres good. ;) But isn't there a way to make
>> some bulk operations without having to drop indexes/FKs?
>
> I've never had the need to use this, but I believe this works using the
> "SET CONSTRAINTS" command[1]; e.g. I can do:
>
>  CREATE TABLE foo ( id INTEGER PRIMARY KEY );
>  CREATE TABLE bar ( id INTEGER REFERENCES foo DEFERRABLE );
>
>  INSERT INTO foo VALUES (1);
>  INSERT INTO bar VALUES (1);
>
> the following will now fail:
>
>  BEGIN;
>  INSERT INTO bar VALUES (2);
>  INSERT INTO foo VALUES (2);
>  COMMIT;
>
> but the following is OK:
>
>  BEGIN;
>  SET CONSTRAINTS bar_id_fkey DEFERRED;
>  INSERT INTO bar VALUES (2);
>  INSERT INTO foo VALUES (2);
>  COMMIT;
>
> Unfortunatly only foreign key constraints are affected by this setting,
> but I believe there are plans to extend this further.

You can also disable triggers completely:
begin;
alter table foo disable trigger all;
<do stuff>
alter table foo enable trigger all;
commit;

of course, if you do this the data is never checked at all, so you
have to be super careful with it....

merlin