On Thursday 06 November 2003 00:32, Martin Pokorny wrote:
>> d.vect roads was really slow, but.... * PostGIS-Server is not on
>> the same maschine G57 is running, but inside a 10Mbit-LAN *
>> PostGIS-Server is 500Mhz-server Maybe this is a bottleneck?
>
> No the problem is in GRASS, 1) GRASS uses random access (read line
> by line number), this is fast for native format but cannot be for
> RDBMS, as it must send query for each line and that is slow
> especially over network (send query and wait for respons for every
> line). I don't know how to solve this. 2) Currently, to read one
> line, GRASS performs more queries: - select type, NumPoints(),
> Dimension() - BEGIN (because binary cursors must be in transaction,
> IIRC) - DECLARE gcursor BINARY CURSOR FOR SELECT ASBINARY( "geom
> FROM table WHERE id = ...") - FETCH ALL in gcursor - CLOSE gcursor -
> COMMIT - SELECT field, cat FROM table WHERE id = ... this could be
> reduced, probably return to WKT and parse results in GRASS, so from
> 7 queries to 2, and PostGIS in GRASS will be faster in this ratio
> (7/2) (?). It was my stupid idea that binary cursor is be faster 
>
> I am considering to either remove PostGIS support at all or do not
> officially support in 5.8 (or how they call it).
I've been working on this on and off for a few weekends now, and, in a
prototype implementation, I've been able to speed up PostGIS use, at
least in some cases. My benchmark has been to use v.in.ogr to import
layers from a TIGER file directly to a postgres database (both
attributes and geometry). Before my mods, importing the "pip" layer
(points only) took about 24 minutes for 26303 points. I've been able
to get the time down to around 2 1/2 minutes with various
modifications to read_post.c, write_post.c, and open_post.c, together
with the addition of some PL/pgSQL functions, and small mods to
v.in.ogr/main.c.
Other results are below. For reference, importing the CompleteChain
layer alone previously took about 6 hours, now it's 11 minutes. The
other example, I never had the patience to complete a test, but it
would have been at least 6 hours, and now it's 25 minutes.
I plan on doing more testing, but I need some advice, too (I haven't
done much with GRASS programming before). One tactic I use to speed up
reading is to buffer the results of a single query that encompasses
many features at a time. However, I need to vary the size of the
buffer depending on whether access is largely sequential (such as when
the features are first imported) or random (such as when building
topology). My approach appears to require two, somewhat global
modifications: 1) an abstract buffer size (or "access pattern")
setting function for all db types, and 2) use of this function in
several modules, such as v.in.ogr and v.clean. One possibility is to
set the default buffer size assuming a random access pattern (i.e,
small), and explicitly set the larger buffer only in the v.in.*
modules, but there are others. I don't have a good picture of the
access patterns yet, so it's hard for me to decide. Ideas, clues or
hints, anyone?
It would be nice to compare also PostGIS contra native,
perferably over the network for both. I think that it is better
to do benchmarks without attributes (v.in.ogr -t)
What is your 'buffer'? Is it sequence of queries executed
as one transaction?
I think, that almost everything may be done as one transaction,
e.g. whole v.in.ogr.
PL/pgSQL is something what I want to avoid. PL/pgSQL means that another
step must be done before GRASS can be used with PostGIS.
What I realy worry about is, that once we use PL/pgSQL functions, it must be
compatible for ever.
Speed is not the only problem of PostGIS in GRASS!:
1) Vect_write_line, Vect_rewrite_line, Vect_delete_line are missing
for level 2.
BTW!, you can run v.in.ogr (with cleaning) or v.clean on PostGIS vectors?
It should be impossible!
2) No tests were done for simultaneous editing. There are certainly many
potential problems in this area.
Are there another reasons to use PostGIS in GRASS except multiuser simultaneous
write access? I don't think so.
Can you put your modifications somewhere on the Web?
As I said, I don't plan to spend any time on this before 5.7.0.
Radim