COPY into table too slow with index

From: "Rick Schumeyer" <rschumeyer(at)ieee(dot)org>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: COPY into table too slow with index
Date: 2005-12-01 17:58:12
Message-ID: 00b101c5f6a0$d0ffea40$0200a8c0@dell8200
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I'm running postgresql 8.1.0 with postgis 1.0.4 on a FC3 system, 3Ghz, 1 GB
memory.

I am using COPY to fill a table that contains one postgis geometry column.

With no geometry index, it takes about 45 seconds to COPY one file.

If I add a geometry index, this time degrades. It keeps getting worse as
more records are

added to the table. It was up to over three minutes per file on my most
recent test.

The problem is that each file contains about 5 - 10 minutes of data.
Eventually, I want to

add the data to the table in "real time". So the COPY needs to take less
time than

actually generating the data.

Here is the relevant section of my postgresql.conf.

# - Memory -

shared_buffers = 5000 # min 16 or max_connections*2, 8KB each

#temp_buffers = 1000 # min 100, 8KB each

#max_prepared_transactions = 5 # can be 0 or more

# note: increasing max_prepared_transactions costs ~600 bytes of shared
memory

# per transaction slot, plus lock space (see max_locks_per_transaction).

work_mem = 20000 # min 64, size in KB

maintenance_work_mem = 20000 # min 1024, size in KB

#max_stack_depth = 2048 # min 100, size in KB

Any suggestions for improvement?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Qingqing Zhou 2005-12-01 18:03:20 Re: filesystem performance with lots of files
Previous Message Jeremy Haile 2005-12-01 17:49:11 Insert performance slows down in large batch