[postgis-users] COPY too slow when geometry column is indexed

[strk at refractions.net]

It might be useful drawing a graph of your timings putting
time spent in the Y axis and number of records in table
in the X axis, then send it to pgsql-hackers for analisys.

The only reason I can think of for this being a postgis-specific
problem is big leaking in index code, but I don't think we
still leak (1.0.4 fixed a leak and was tested with milions
of records).

Also, do you delete records as well ? Deleted keys would
stick in the index until a vacuum full...

--strk;

On Thu, Dec 01, 2005 at 01:01:12PM -0500, Rick Schumeyer wrote:
> 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.  If I let
> 
> it run long enough, the time degrades to 7 or 8 minutes.
> 
>  
> 
> 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?
> 
>  
> 

> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users


-- 

 /"\    ASCII Ribbon Campaign
 \ /    Respect for open standards
  X     No HTML/RTF in email
 / \    No M$ Word docs in email