Skip site navigation (1) Skip section navigation (2)

Re: slow update of index during insert/copy

From: "Scott Carey" <scott(at)richrelevance(dot)com>
To: "Thomas Finneid" <tfinneid(at)student(dot)matnat(dot)uio(dot)no>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: slow update of index during insert/copy
Date: 2008-08-31 18:38:15
Message-ID: a1ec7d000808311138k76d5291bh64806a1cdef3b0e6@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
You may want to investigate pg_bulkload.

http://pgbulkload.projects.postgresql.org/

One major enhancement over COPY is that it does an index merge, rather than
modify the index one row at a time.
http://pgfoundry.org/docman/view.php/1000261/456/20060709_pg_bulkload.pdf



On Sun, Aug 31, 2008 at 6:32 AM, Thomas Finneid <
tfinneid(at)student(dot)matnat(dot)uio(dot)no> wrote:

> Hi
>
> I am working on a table which stores up to 125K rows per second and I find
> that the inserts are a little bit slow. The insert is in reality a COPY of a
> chunk of rows, up to 125K. A COPY og 25K rows, without an index, is fast
> enough, about 150ms. With the index, the insert takes about 500ms. The read
> though, is lightning fast, because of the index. It takes only 10ms to
> retrieve 1000 rows from a 15M row table. As the table grows to several
> billion rows, that might change though.
>
> I would like the insert, with an index, to be a lot faster than 500ms,
> preferrably closer to 150ms. Any advice on what to do?
> Additionally, I dont enough about pg configuring to be sure I have included
> all the important directives and given them proportional values, so any help
> on that as well would be appreciated.
>
> Here are the details:
>
> postgres 8.2.7 on latest kubuntu, running on dual Opteron quad cores, with
> 8GB memory and 8 sata disks on a raid controller (no raid config)
>
> table:
>
> create table v1
> (
>        id_s            integer,
>        id_f            integer,
>        id_st           integer,
>        id_t            integer,
>        value1          real,
>        value2          real,
>        value3          real,
>        value4          real,
>        value5          real,
>        ...
>        value20         real
> );
>
> create index idx_v1 on v1 (id_s, id_st, id_t);
>
> - insert is a COPY into the 5-8 first columns. the rest are unused so
>  far.
>
> postgres config:
>
> autovacuum = off
> checkpoint_segments = 96
> commit_delay = 5
> effective_cache_size = 128000
> fsync = on
> max_fsm_pages = 208000
> max_fsm_relations = 10000
> max_connections = 20
> shared_buffers = 128000
> wal_sync_method = fdatasync
> wal_buffers = 256
> work_mem = 512000
> maintenance_work_mem = 2000000
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

In response to

Responses

pgsql-performance by date

Next:From: Thomas FinneidDate: 2008-08-31 19:20:16
Subject: Re: slow update of index during insert/copy
Previous:From: Thomas FinneidDate: 2008-08-31 13:32:21
Subject: slow update of index during insert/copy

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group