Re: Loading speed

On Wed, 2003-10-22 at 03:25, Cath Lawrence wrote:
> Now the REFERENCES bits build triggers, and in each case it refers to
> the primary key of the table.

It needs to refer to a UNIQUE column, not necessarily the primary key.

>  This is always indexed, yes? When I
> construct the table it says  CREATE TABLE / PRIMARY KEY will create
> implicit index 'amino_acid_pkey' for table 'amino_acid' - this IS an
> index on the column I named as the PRIMARY KEY, not on some oid, isn't
> id?

Yes

> With my simple-minded pygresql script, I have one insert statement per
> atom, and of course it crawls very feebly. Perhaps there's an
> insert_many method I haven't found yet (the docs suck, I will keep
> looking).

Use transactions to group them 1000 at a time; that will greatly reduce
the overhead on each insertion.

BEGIN;
INSERT...
INSERT...
[1000 times]
END;

> Or perhaps I should write them out to a text file and use COPY?

That is the fastest way to do bulk importing.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "But what things were gain to me, those I counted loss
      for Christ."                 Philippians 3:7