[Geoserver-devel] Non-optimized SQL-statements?

Hi folks,

First of all, thanks for a great product!
I'm using GeoServer in my masters thesis together with Deegree and I'm having a lot of fun.

I have a question about how geoserver is building the sql-statements.
When I perform a GetMap-request on a PostGIS layer called 'byggnader', geoserver seems to fire the following statement:

# SELECT gid, AsText(force_2d("the_geom")), "shape_area"
# FROM "byggnader"
# WHERE (
# "the_geom" && GeometryFromText('POLYGON ((1332700 6174900, 1332700 6181900, 1339700 6181900, 1339700 6174900, 1332700 6174900))', 2400)
# AND
# intersects("the_geom", GeometryFromText('POLYGON ((1332700 6174900, 1332700 6181900, 1339700 6181900, 1339700 6174900, 1332700 6174900))', 2400))
# AND
# "the_geom" && GeometryFromText('POLYGON ((1332700 6174900, 1332700 6181900, 1339700 6181900, 1339700 6174900, 1332700 6174900))',2400)
# AND
# intersects("the_geom", GeometryFromText('POLYGON ((1332700 6174900, 1332700 6181900, 1339700 6181900, 1339700 6174900, 1332700 6174900))', 400))
# )

When I evaluate the statement in psql using 'EXPLAIN ANALYZE' I get the following result:

# Index Scan using byggnader_gist_index on byggnader (cost=0.00..652.08 rows=18 width=312) (actual time=0.000..16574.000 rows=9452 loops=1)
# Index Cond: (
# (the_geom && 'SRID=2400;POLYGON((1332700 6174900,1332700 6181900,1339700 6181900,1339700 6174900,1332700 6174900))'::geometry)
# AND
# (the_geom && 'SRID=2400;POLYGON((1332700 6174900,1332700 6181900,1339700 6181900,1339700 6174900,1332700 6174900))'::geometry)
# )
# Filter: (
# intersects(the_geom, 'SRID=2400;POLYGON((1332700 6174900,1332700 6181900,1339700 6181900,1339700 6174900,1332700 6174900))'::geometry)
# AND
# intersects(the_geom, 'SRID=400;POLYGON((1332700 6174900,1332700 6181900,1339700 6181900,1339700 6174900,1332700 6174900))'::geometry))
# Total runtime: 17326.000 ms

From my point of view it seems as if the following statement would be sufficient:

# SELECT gid, AsText(force_2d("the_geom")), "shape_area"
# FROM "byggnader"
# WHERE (
# "the_geom" && GeometryFromText('POLYGON ((1332700 6174900, 1332700 6181900, 1339700 6181900, 1339700 6174900, 1332700 6174900))', 2400)
# )

...which results in:

# Index Scan using byggnader_gist_index on byggnader (cost=0.00..32893.03 rows=8534 width=312) (actual time=0.000..2033.000 rows=9452 loops=1)
# Index Cond: (
# the_geom && 'SRID=2400;POLYGON((1332700 6174900,1332700 6181900,1339700 6181900,1339700 6174900,1332700 6174900))'::geometry
# )
# Total runtime: 2123.000 ms

Am I totally wrong or is something happening here that maybe could be improved?

Best regards

Peter Segerstedt

_________________________________________________________________
Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail

    Aren't the queries different?

    In the first query you are intersecting with 2 geometries (SRID=2400 and 400), and in the second one, just with 1 geometry (SRID=2400).

-- Alexandre

Em Thu, 16 Sep 2004 13:57:38 +0200
"Peter Segerstedt" <petersegerstedt@anonymised.com> escreveu:

# SELECT gid, AsText(force_2d("the_geom")), "shape_area"
# FROM "byggnader"
# WHERE (
# "the_geom" && GeometryFromText('POLYGON ((1332700 6174900, 1332700
6181900, 1339700 6181900, 1339700 6174900, 1332700 6174900))', 2400)
# AND
# intersects("the_geom", GeometryFromText('POLYGON ((1332700 6174900,
1332700 6181900, 1339700 6181900, 1339700 6174900, 1332700 6174900))',
2400))
# AND
# "the_geom" && GeometryFromText('POLYGON ((1332700 6174900, 1332700
6181900, 1339700 6181900, 1339700 6174900, 1332700 6174900))',2400)
# AND
# intersects("the_geom", GeometryFromText('POLYGON ((1332700 6174900,
1332700 6181900, 1339700 6181900, 1339700 6174900, 1332700 6174900))', 400))
# )

When I evaluate the statement in psql using 'EXPLAIN ANALYZE' I get the
following result:

# Index Scan using byggnader_gist_index on byggnader (cost=0.00..652.08
rows=18 width=312) (actual time=0.000..16574.000 rows=9452 loops=1)
# Index Cond: (
# (the_geom && 'SRID=2400;POLYGON((1332700 6174900,1332700
6181900,1339700 6181900,1339700 6174900,1332700 6174900))'::geometry)
# AND
# (the_geom && 'SRID=2400;POLYGON((1332700 6174900,1332700
6181900,1339700 6181900,1339700 6174900,1332700 6174900))'::geometry)
# )
# Filter: (
# intersects(the_geom, 'SRID=2400;POLYGON((1332700 6174900,1332700
6181900,1339700 6181900,1339700 6174900,1332700 6174900))'::geometry)
# AND
# intersects(the_geom, 'SRID=400;POLYGON((1332700 6174900,1332700
6181900,1339700 6181900,1339700 6174900,1332700 6174900))'::geometry))
# Total runtime: 17326.000 ms

From my point of view it seems as if the following statement would be
sufficient:

# SELECT gid, AsText(force_2d("the_geom")), "shape_area"
# FROM "byggnader"
# WHERE (
# "the_geom" && GeometryFromText('POLYGON ((1332700 6174900, 1332700
6181900, 1339700 6181900, 1339700 6174900, 1332700 6174900))', 2400)
# )

...which results in:

# Index Scan using byggnader_gist_index on byggnader (cost=0.00..32893.03
rows=8534 width=312) (actual time=0.000..2033.000 rows=9452 loops=1)
# Index Cond: (
# the_geom && 'SRID=2400;POLYGON((1332700 6174900,1332700
6181900,1339700 6181900,1339700 6174900,1332700 6174900))'::geometry
# )
# Total runtime: 2123.000 ms

Am I totally wrong or is something happening here that maybe could be
improved?