headers
Martin Landa | 1 Apr 2009 16:26
Picon

performace question

Hi,

I am beginning with PostGIS and I wonder about performance, e.g.
having layer 'regions' = 'obce' (polygon) and boundary of CZ = 'cr'
(polygon).

SELECT count(obce.gid) FROM obce;
 count
-------
  6361
(1 row)

SELECT count(obce.gid) FROM obce,cr WHERE ST_Touches(obce.the_geom,
cr.the_geom);
 count
-------
   285
(1 row)

Time: 258271.730 ms

It seems to be quite long time, any hits about configuration of
PostgreSQL, query optimalization?

PostgreSQL 8.1.15,  1.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

Thanks in advance, Martin

--

-- 
Martin Landa <landa.martin gmail.com> * http://gama.fsv.cvut.cz/~landa
(Continue reading)

Permalink | Reply | 11 comments |
headers
Paul Ramsey | 1 Apr 2009 16:39
Picon
Gravatar

Re: performace question

You have spatial indexes on the tables I assume?

On 1-Apr-09, at 7:26 AM, Martin Landa <landa.martin <at> gmail.com> wrote:

> Hi,
>
> I am beginning with PostGIS and I wonder about performance, e.g.
> having layer 'regions' = 'obce' (polygon) and boundary of CZ = 'cr'
> (polygon).
>
> SELECT count(obce.gid) FROM obce;
> count
> -------
>  6361
> (1 row)
>
> SELECT count(obce.gid) FROM obce,cr WHERE ST_Touches(obce.the_geom,
> cr.the_geom);
> count
> -------
>   285
> (1 row)
>
> Time: 258271.730 ms
>
> It seems to be quite long time, any hits about configuration of
> PostgreSQL, query optimalization?
>
> PostgreSQL 8.1.15,  1.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
>
(Continue reading)

Permalink | Reply | 1 comment |
headers
Ivan Mincik | 1 Apr 2009 17:04
Picon

Re: performace question

On Wednesday 01 April 2009, Martin Landa wrote:
> Hi,
> 
> I am beginning with PostGIS and I wonder about performance, e.g.
> having layer 'regions' = 'obce' (polygon) and boundary of CZ = 'cr'
> (polygon).
> 
> SELECT count(obce.gid) FROM obce;
>  count
> -------
>   6361
> (1 row)
> 
> SELECT count(obce.gid) FROM obce,cr WHERE ST_Touches(obce.the_geom,
> cr.the_geom);
>  count
> -------
>    285
> (1 row)
> 
> Time: 258271.730 ms
> 
> It seems to be quite long time, any hits about configuration of
> PostgreSQL, query optimalization?
Have You tried to make VACUUM on that table ?
> 
> PostgreSQL 8.1.15,  1.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
> 
> Thanks in advance, Martin
>
(Continue reading)

Permalink | Reply | 8 comments |
headers
geniaux | 1 Apr 2009 17:20
Picon
Picon

Re: performace question

Selon Ivan Mincik <ivan.mincik <at> gmail.com>:

1) Create a spatial index
2) make VACUUM on that table.
3) use "SELECT count(obce.gid) FROM obce,cr WHERE  obce.the_geom && cr.the_geom
and ST_Touches(obce.the_geom,cr.the_geom);"

> On Wednesday 01 April 2009, Martin Landa wrote:
> > Hi,
> >
> > I am beginning with PostGIS and I wonder about performance, e.g.
> > having layer 'regions' = 'obce' (polygon) and boundary of CZ = 'cr'
> > (polygon).
> >
> > SELECT count(obce.gid) FROM obce;
> >  count
> > -------
> >   6361
> > (1 row)
> >
> > SELECT count(obce.gid) FROM obce,cr WHERE ST_Touches(obce.the_geom,
> > cr.the_geom);
> >  count
> > -------
> >    285
> > (1 row)
> >
> > Time: 258271.730 ms
> >
> > It seems to be quite long time, any hits about configuration of
(Continue reading)

Permalink | Reply | 6 comments |
headers
Martin Landa | 1 Apr 2009 18:37
Picon

Re: performace question

Hi,

2009/4/1 Paul Ramsey <pramsey <at> cleverelephant.ca>:
> You have spatial indexes on the tables I assume?

yes,

Indexes:
    "obce_pkey" PRIMARY KEY, btree (gid)
    "obce_the_geom_gist" gist (the_geom)
Check constraints:
    "enforce_dims_the_geom" CHECK (st_ndims(the_geom) = 2)
    "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) =
'MULTIPOLYGON'::text OR the_geom IS NULL)
    "enforce_srid_the_geom" CHECK (st_srid(the_geom) = 102067)

Martin

--

-- 
Martin Landa <landa.martin gmail.com> * http://gama.fsv.cvut.cz/~landa
Permalink | Reply | 0 comments |
headers
Martin Landa | 1 Apr 2009 18:43
Picon

Re: performace question

Hi,

2009/4/1 Ivan Mincik <ivan.mincik <at> gmail.com>:

[...]

>> It seems to be quite long time, any hits about configuration of
>> PostgreSQL, query optimalization?
> Have You tried to make VACUUM on that table ?

after vacuum still same...

Time: 258032.030 ms

M.

--

-- 
Martin Landa <landa.martin gmail.com> * http://gama.fsv.cvut.cz/~landa
Permalink | Reply | 0 comments |
headers
Martin Landa | 1 Apr 2009 18:50
Picon

Re: performace question

Hi,

2009/4/1  <geniaux <at> avignon.inra.fr>:
> 3) use "SELECT count(obce.gid) FROM obce,cr WHERE  obce.the_geom && cr.the_geom
> and ST_Touches(obce.the_geom,cr.the_geom);"

it will not help, all polygons from 'obce' are inside of 'cr' - I just
want to select those polygons from 'obce' (multipolygon) which share
with boundary of 'cr' (linestring) at least one point.

M.

--

-- 
Martin Landa <landa.martin gmail.com> * http://gama.fsv.cvut.cz/~landa
Permalink | Reply | 5 comments |
headers
Paul Ramsey | 1 Apr 2009 19:55
Favicon
Gravatar

Re: performace question

Can you share your data with me? I'm interested to profile and see
where the bottleneck actually is.

P.

On Wed, Apr 1, 2009 at 9:50 AM, Martin Landa <landa.martin <at> gmail.com> wrote:
> Hi,
>
> 2009/4/1  <geniaux <at> avignon.inra.fr>:
>> 3) use "SELECT count(obce.gid) FROM obce,cr WHERE  obce.the_geom && cr.the_geom
>> and ST_Touches(obce.the_geom,cr.the_geom);"
>
> it will not help, all polygons from 'obce' are inside of 'cr' - I just
> want to select those polygons from 'obce' (multipolygon) which share
> with boundary of 'cr' (linestring) at least one point.
>
> M.
>
> --
> Martin Landa <landa.martin gmail.com> * http://gama.fsv.cvut.cz/~landa
> _______________________________________________
> postgis-users mailing list
> postgis-users <at> postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
Permalink | Reply | 4 comments |
headers
Martin Landa | 1 Apr 2009 20:06
Picon

Re: performace question

Hi,

2009/4/1 Paul Ramsey <pramsey <at> opengeo.org>:
> Can you share your data with me? I'm interested to profile and see
> where the bottleneck actually is.

data are available at

http://josef.fsv.cvut.cz/~landa/postgis/obce_cr.sql.gz

before import define also local SRS

INSERT INTO spatial_ref_sys VALUES (102067, 'local', 102067,
'PROJCS["Krovak",GEOGCS["bessel",DATUM["unknown",
SPHEROID["Bessel_1841",6377397.155,299.1528128],
TOWGS84[570.8,85.7,462.8,4.998,1.587,5.261,3.56]],
PRIMEM["Greenwich",0],UNIT["degree",0.0174532925199433]],
PROJECTION["Krovak"],PARAMETER["latitude_of_center",0],
PARAMETER["longitude_of_center",0],PARAMETER["azimuth",0],
PARAMETER["pseudo_standard_parallel_1",0],PARAMETER["scale_factor",1],
PARAMETER["false_easting",0],PARAMETER["false_northing",0],UNIT["meter",1]]',
'+proj=krovak +a=6377397.155 +rf=299.1528128 +no_defs
+towgs84=570.8,85.7,462.8,4.998,1.587,5.261,3.56 +to_meter=1.0');

Thanks in advance, Martin

--

-- 
Martin Landa <landa.martin gmail.com> * http://gama.fsv.cvut.cz/~landa
Permalink | Reply | 3 comments |
headers
Paul Ramsey | 1 Apr 2009 21:03
Favicon
Gravatar

Re: performace question

OK, so the operation is spending all its time in GEOS, running the
Touches test. You have a since 2848-vertex linestring and 6361
candidate features. And you get back 285 touches. Fortunately, because
you are testing against a linestring, you have an alternative test,
since the interaction of a polygon boundary and linestring interior
counts as an intersection.

SELECT count(obce.gid) FROM obce,cr WHERE ST_Intersects(obce.the_geom,
cr.the_geom);

I found this to run 10 times as fast, because it can use the prepared
geometry optimization. It still returns 285 resultants.

Paul

On Wed, Apr 1, 2009 at 11:06 AM, Martin Landa <landa.martin <at> gmail.com> wrote:
> Hi,
>
> 2009/4/1 Paul Ramsey <pramsey <at> opengeo.org>:
>> Can you share your data with me? I'm interested to profile and see
>> where the bottleneck actually is.
>
> data are available at
>
> http://josef.fsv.cvut.cz/~landa/postgis/obce_cr.sql.gz
>
> before import define also local SRS
>
> INSERT INTO spatial_ref_sys VALUES (102067, 'local', 102067,
> 'PROJCS["Krovak",GEOGCS["bessel",DATUM["unknown",
(Continue reading)

Permalink | Reply | 2 comments |

Gmane