Oops, I seem to have missed a '2' when I copied the output (I've corrected it below).
No, they are two identical pairs of conditions, actually I think all of the four conditions in variant 1 are equal which in that case means that they could all be replaced by one of them. The operation '&&' (overlaps) and the function 'intersects()' seems to do the same job, just that for some reason, 'intersects()' in my case doesn't use the gist-index. The performance (counted in time) differs by a factor 9 in my simple test.
/Peter
From: Alexandre Florio <aflorio@anonymised.com>
To: geoserver-devel@lists.sourceforge.net
Subject: Re: [Geoserver-devel] Non-optimized SQL-statements?
Date: Thu, 16 Sep 2004 09:52:53 -0300Aren'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))', 2400))
> # )
>
> 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?-------------------------------------------------------
This SF.Net email is sponsored by: YOU BE THE JUDGE. Be one of 170
Project Admins to receive an Apple iPod Mini FREE for your judgement on
who ports your project to Linux PPC the best. Sponsored by IBM.
Deadline: Sept. 24. Go here: http://sf.net/ppc_contest.php
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel
_________________________________________________________________
Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail