[postgis-users] Fast access to PostGIS from c/c++

[Mark Cave-Ayland]

On Thu, 2006-12-07 at 11:22 +0100, Boehm, Andreas wrote:
> Hi Mark,
> 
> > Can you post your test cases for both MakePoint() and 
> > GeometryFromText()? Over how many points are you testing? 
> 
> there are 3 different test cases
> 1) Inserting with executing "GeometryFromText()"
> 2) Inserting with executing "PREPARE insert_pt ..." and "EXECUTE" as you
> have proposed
> 3) Inserting using libpqxx's prepared functionality
> 
> Case 1 and 2 are similar. No. 3 is the one witch takes about 60 % more
> time.
> I've made tests with 10,000, 100,000 and - for case 1 - with 1,000,000
> points.
> Until now I've left the database configuration untouched.
> 
> I'll post the complete source. Thanks a lot!
> Andi


Hi Andi,

IANAC++P (I Am Not A C++ Programmer) but the following tips should help:


1) You mention that you haven't changed the database configuration. The
best thing you can do is set shared_buffers/effective_cache_size as
mentioned in my previous email in this thread to Martin - otherwise
PostgreSQL *will* swap excessively to disk. This is the single most
important thing you can do to obtain better performance.

2) In InsertPointsFromTxt() and InsertPointsMakePt(), you are building
all 10,000 points into a single string and submitting. I suspect you
would be better off submitting each individual execStr with t->exec()
rather than concatenating all 10,000 into one long string.

3) Don't create any indexes on the table/VACUUM ANALYZE until after you
have inserted all your points, i.e. insert the points as soon as you
have created the table. Otherwise you waste I/O manipulating the index
rather than inserting your data.

4) As a note, rand() can be slow on some platforms.

5) Instead of executing your SQL statements directly over the network,
try and append them into one large text file. You can then run this into
psql using the -f parameter on the server itself to determine whether
any speed problems are related to network latency.


As a rough guide to performance you should be seeing, for me it was
possible to get over 10,000 points per second inserted into a table on
old 1.1GHz PIII hardware with a single 36GB SCSI disk executing
pre-generated text files with psql -f.

Finally, make sure you are using the latest and greatest
PostgreSQL/PostGIS - at the very minimum you should be aiming for
PostgreSQL 8.1 and PostGIS 1.1, preferably the latest releases of each
to ensure you have all the latest bugfixes. There is often a speed gain
of between 10-20% just by upgrading to the latest PostgreSQL version.


HTH,

Mark.