Hi,
I noticed also that find_extent is sent every time and for each PostGIS layer
when they need refreshing, not only when a new layer is is added into a
project. Because find_extent leads always to full scan I suppose this must make
the major part of the database load.
I believe unnecessary large queries can be choked by setting scale limit with
Change Styles tool, but this can be done just after the new layer has beed
added and it can be too late then.
I cannot test the effect of using estimated_extent with OpenJUMP but only
directly with SQL. I am just an user, not a developer nor programmer.
-Jukka-
-----Alkuperäinen viesti-----
Lähettäjä: [EMAIL PROTECTED] puolesta: Michaël Michaud
Lähetetty: su 6.4.2008 20:36
Vastaanottaja: OpenJump develop and use
Aihe: Re: [JPP-Devel] PostGIS driver improvements
Hi Jukka,
I never noticed a "select find_extent( 'table', 'geom' )" was used
Maybe this is to be able to do a "full extent" from jump ? - bad idea if
you have a 10M rows table ;-(
In the code, the author mentionned :
// Use find_extent - sometimes estimated_extent was returning null
and I saw in postgis history that there has been some bug fixes with
estimated_extent
Did you try OpenJUMP after having replaced find_extent by estimated_extent ?
Anyway, this will not solve the second point : OpenJUMP cannot load a
10M features layer, and using datastore plugin with a large database
often lead to an OOM.
I think limiting the number of returned objects maybe done in the query,
adding a limit statement to the sql where clause
To solve the problem in a more definitive way, one should only load a
pointer and an envelope from the database and add a cache (without a
good cache, I think access time would kill jump performance).
Such a work is still to be done...
Michaël
Jukka Rahkonen a écrit :
>Hi,
>
>I made my first large PostGIS table with nearly ten million rows. Untill this
>I
>had thought that OpenJUMP works very well with PostGIS but now I am not sure
>anymore. What I say is about the native PostGIS driver, I have not tried the
>others with this large table.
>
>I believe that the first problem I notised actually makes all big PostGIS
>tables
> unusable slow and I am not sure if it really needs to be so.
>OpenJUMP seems to start PostGIS query by sending:
>SELECT AsBinary(find_extent( 'table', 'geom' ))
>
>I could not find function find_extent from PostGIS manual but I suppose it is
>doing the same as
>SELECT extent(geom)from table;
>This will mean full scan to geometry table every time than PostGIS layer is
>accessed. Index does not help with this function and it takes several minutes
>to
>run the query with my 10 million row table. The final query returning the
>geometries takes just seconds. I wonder why these exact extents are queried?
>It
>looks like coming from here (taken from CVN.txt):
>2006-05-08 16:20 jaquino
> * com/vividsolutions/jump/datastore/postgis/PostgisDSMetadata.java:
> Use find_extent for PostGIS extents
>
>Alternative query
>SELECT estimated_extent('table','geom');
>is returning the result immediately, but it is not accurate. I do not know for
>what OpenJUMP is using and needing the exact extents of the database layer, or
>if estimated extents could be used instead.
>
>Another problem is that with a big database table it is too simple to send
>queries which will leed to memory running out and a total jam. All that is
>needed is to zoom out too far. Perhaps situation could be done more safe for
>the
>user by adding an option to limit the number or returned features? This option
>should be selectable in the Add datastore layer and Run datastore query
>dialogs,
>I believe it would need just adding LIMIT max_number to PostGIS queries. I
>understand this would be dangerous as well if the user does not realise that
>the
>query did not give a full set of data. So I am not if this is a good idea of
>all. Perhaps there could be a warning message in case of
>max_features=returned_features?
>
>Regards,
>
>-Jukka Rahkonen-
>
>
>-------------------------------------------------------------------------
>This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
>Register now and save $200. Hurry, offer ends at 11:59 p.m.,
>Monday, April 7! Use priority code J8TLD2.
>http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
>_______________________________________________
>Jump-pilot-devel mailing list
>Jump-pilot-devel@lists.sourceforge.net
>https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
>
>
>
>
-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
Register now and save $200. Hurry, offer ends at 11:59 p.m.,
Monday, April 7! Use priority code J8TLD2.
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
_______________________________________________
Jump-pilot-devel mailing list
Jump-pilot-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
Register now and save $200. Hurry, offer ends at 11:59 p.m.,
Monday, April 7! Use priority code J8TLD2.
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
_______________________________________________
Jump-pilot-devel mailing list
Jump-pilot-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
