[postgis-users] Trunk 2.0.0 seem slow on Linux 64 bit

[Nicklas Avén]

Hallo

First your question about index-handling of multi-geometries.
The index handles the whole geometry as one big so in your case where
the multi-parts are spread you will get a lot better performance if you
use st_dump to split the multi to several rows of single polygons and
build an index on that. 

I don't think there is anything changed in the index-handliong, but
others knows better.

It is quite difficult to say what the difference can be that makes the
slower query there is too many unknown.

Since your query is inserting the speed of writing to the database is a
part of it. 

First question is if the data is exactly the same? Is there the same
data in the same amount?
Is there working indexes both tables on both machines?

To reduce the number of unknown you could do something like this:
select count(*)
        from 
           public.table1 as a,
            public._sample_extent as b
        where 
          ST_Intersects(a.geom,b.geom)=true

Then you only test the st_intersects part and you have reduced the
output part of the query to just be an integer number representing the
number of intersections.

Or to test it in smaller parts you can do like this

select count(*)
        from 

(select * from 
           public.table1 as a,
            public._sample_extent as b
        where 
          ST_Intersects(a.geom,b.geom)=true
	   limit 100000
)c

then you can find a number of limit that gives you a more easy
comparable query time.


But the main question is if the datasets is identical.

/Nicklas





On Sun, 2010-08-22 at 10:50 +0200, Andrea Peri wrote:
> Hi,
> 
> I have a query which on a notebook with Window7 - 64 bit ,
> with postgres 8.4.4 32bit and Postgis 1.5.1 32 bit 
> work in about 10 minutes.
> 
> When I try the same query on a machine quite big then the notebook.
> And with 
> Linux RedHat 64 bit ,
> but using Postgres 8.4.4 compile from source (64bit)
> and Geos from trunk, and Postgis from trunk all compiled to 64 bit.
> 
> The same query sql run for a time really huge.
> (some hours ..)
> 
> The query is this
> INSERT INTO PUBLIC._TEST_CAMPIONAMENTO (id,idrtt,geom) (
>         select 
>             a.oid::integer,
>             a.id,
>             ST_Force_2D(ST_Multi(a.geom)) 
>         from 
>             public.table1 as a,
>             public._sample_extent as b
>         where 
>             ST_Intersects(a.geom,b.geom)=true
> )
> ;
> 
> The only think I can do is passing from the trunk version 2.0.0 to the
> 1.5.1 version,
> but this mean to reload the table "table1". Because this table is
> quite huge (more the 9.500.000 records with many vertex on each
> records)
> and reload it require a bit of time.
> And I have some doubt that the 2.0.0 trunk was more slow than 1.5.1.
> 
> Before try this I have a question to ask:
> 
> the table" _sample_extent " is one only record with a multipolygon
> having about 20 part disjoint each other, but not near each other.
> I don't know if the GIST index use only one unique extent of all the
> MultiPolygon or use more single extents one for each parts ?
> 
> And could be somethink is changed from 1.5.1 to 2.0.0 in the strategy
> of GIS index ?
> 
> Many thx,
> 
> Andrea
> 
> -- 
> -----------------
> Andrea Peri
> . . . . . . . . . 
> qwerty àèìòù
> -----------------
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users