ST_Overlaps and performance

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

ST_Overlaps and performance

DrYSG
I have a table with 21 Million Rows. The Geometry field is a simple rectangular bounding box (Polygon with 4 corners).

Users will be doing executing queries to find all overlapping rows to a user supplied rectangle. I have been finding that my naive query of:

SELECT *
FROM portal.metadata as cat
WHERE ST_Overlaps(cat.location,ST_GeomFromText('POLYGON((-70.9433962 41.5384615,-72.5283019 41.5384615,-72.5283019 41.1538462,-70.9433962 41.1538462,-70.9433962 41.5384615))', 4326));

Can take up to 40 seconds on a cold location. However, an ST_Contains query of a point is only about 8 seconds. Yes, location is indexed using GIST.

CREATE INDEX catalog_location_idx
  ON portal.catalog
  USING gist
  (location );

Now, it first stuck me as odd that it was taking more than 4 times the point query to discover overlap. Then I thought about it, and I can see cases where there ST_Overlaps cannot assume certain geometries.

I am now considering doing a WHERE clause that checks for ST_Contains for both the Top-Left and Bottom-Right corners of the QueryRectangle since I know that all bboxes in the DB are rectangular.

But I thought I would see what those with knowledge of the internals of the spatial functions have to say.

Ideas?

Reply | Threaded
Open this post in threaded view
|

Re: ST_Overlaps and performance

Stephen Woodbridge
Try using st_dwith(cat.location, other_geom, 0.0)
This will include polygons that touch in addition to overlaps so you
might want to add "and st_overlaps(...)" to the above condition.

-Steve W

On 3/7/2012 12:26 PM, DrYSG wrote:

> I have a table with 21 Million Rows. The Geometry field is a simple
> rectangular bounding box (Polygon with 4 corners).
>
> Users will be doing executing queries to find all overlapping rows to a user
> supplied rectangle. I have been finding that my naive query of:
>
> SELECT *
> FROM portal.metadata as cat
> WHERE ST_Overlaps(cat.location,ST_GeomFromText('POLYGON((-70.9433962
> 41.5384615,-72.5283019 41.5384615,-72.5283019 41.1538462,-70.9433962
> 41.1538462,-70.9433962 41.5384615))', 4326));
>
> Can take up to 40 seconds on a cold location. However, an ST_Contains query
> of a point is only about 8 seconds. Yes, location is indexed using GIST.
>
> CREATE INDEX catalog_location_idx
>    ON portal.catalog
>    USING gist
>    (location );
>
> Now, it first stuck me as odd that it was taking more than 4 times the point
> query to discover overlap. Then I thought about it, and I can see cases
> where there ST_Overlaps cannot assume certain geometries.
>
> I am now considering doing a WHERE clause that checks for ST_Contains for
> both the Top-Left and Bottom-Right corners of the QueryRectangle since I
> know that all bboxes in the DB are rectangular.
>
> But I thought I would see what those with knowledge of the internals of the
> spatial functions have to say.
>
> Ideas?
>
>
>
> --
> View this message in context: http://postgis.17.n6.nabble.com/ST-Overlaps-and-performance-tp4555465p4555465.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: ST_Overlaps and performance

Nicolas Ribot-2
In reply to this post by DrYSG
Hi,

Correct me if i'm wrong, but as your input geometries are bbox
rectangle, you could juste use
an bbox filter to see if rectangles overlaps:

SELECT *
FROM portal.metadata as cat
WHERE cat.location && 'SRID=4326;POLYGON((-70.9433962
41.5384615,-72.5283019 41.5384615,-72.5283019 41.1538462,-70.9433962
41.1538462,-70.9433962 41.5384615))'::geometry;

Nicolas



On 7 March 2012 18:26, DrYSG <[hidden email]> wrote:

> I have a table with 21 Million Rows. The Geometry field is a simple
> rectangular bounding box (Polygon with 4 corners).
>
> Users will be doing executing queries to find all overlapping rows to a user
> supplied rectangle. I have been finding that my naive query of:
>
> SELECT *
> FROM portal.metadata as cat
> WHERE ST_Overlaps(cat.location,ST_GeomFromText('POLYGON((-70.9433962
> 41.5384615,-72.5283019 41.5384615,-72.5283019 41.1538462,-70.9433962
> 41.1538462,-70.9433962 41.5384615))', 4326));
>
> Can take up to 40 seconds on a cold location. However, an ST_Contains query
> of a point is only about 8 seconds. Yes, location is indexed using GIST.
>
> CREATE INDEX catalog_location_idx
>  ON portal.catalog
>  USING gist
>  (location );
>
> Now, it first stuck me as odd that it was taking more than 4 times the point
> query to discover overlap. Then I thought about it, and I can see cases
> where there ST_Overlaps cannot assume certain geometries.
>
> I am now considering doing a WHERE clause that checks for ST_Contains for
> both the Top-Left and Bottom-Right corners of the QueryRectangle since I
> know that all bboxes in the DB are rectangular.
>
> But I thought I would see what those with knowledge of the internals of the
> spatial functions have to say.
>
> Ideas?
>
>
>
> --
> View this message in context: http://postgis.17.n6.nabble.com/ST-Overlaps-and-performance-tp4555465p4555465.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: ST_Overlaps and performance

Stephen Woodbridge
Oh! good catch that would work unless the the bbox polygons can be
rotated. Again this will collect the touches case so if you need a true
overlaps then add the and clause.

-Steve W

On 3/7/2012 12:34 PM, Nicolas Ribot wrote:

> Hi,
>
> Correct me if i'm wrong, but as your input geometries are bbox
> rectangle, you could juste use
> an bbox filter to see if rectangles overlaps:
>
> SELECT *
> FROM portal.metadata as cat
> WHERE cat.location&&  'SRID=4326;POLYGON((-70.9433962
> 41.5384615,-72.5283019 41.5384615,-72.5283019 41.1538462,-70.9433962
> 41.1538462,-70.9433962 41.5384615))'::geometry;
>
> Nicolas
>
>
>
> On 7 March 2012 18:26, DrYSG<[hidden email]>  wrote:
>> I have a table with 21 Million Rows. The Geometry field is a simple
>> rectangular bounding box (Polygon with 4 corners).
>>
>> Users will be doing executing queries to find all overlapping rows to a user
>> supplied rectangle. I have been finding that my naive query of:
>>
>> SELECT *
>> FROM portal.metadata as cat
>> WHERE ST_Overlaps(cat.location,ST_GeomFromText('POLYGON((-70.9433962
>> 41.5384615,-72.5283019 41.5384615,-72.5283019 41.1538462,-70.9433962
>> 41.1538462,-70.9433962 41.5384615))', 4326));
>>
>> Can take up to 40 seconds on a cold location. However, an ST_Contains query
>> of a point is only about 8 seconds. Yes, location is indexed using GIST.
>>
>> CREATE INDEX catalog_location_idx
>>   ON portal.catalog
>>   USING gist
>>   (location );
>>
>> Now, it first stuck me as odd that it was taking more than 4 times the point
>> query to discover overlap. Then I thought about it, and I can see cases
>> where there ST_Overlaps cannot assume certain geometries.
>>
>> I am now considering doing a WHERE clause that checks for ST_Contains for
>> both the Top-Left and Bottom-Right corners of the QueryRectangle since I
>> know that all bboxes in the DB are rectangular.
>>
>> But I thought I would see what those with knowledge of the internals of the
>> spatial functions have to say.
>>
>> Ideas?
>>
>>
>>
>> --
>> View this message in context: http://postgis.17.n6.nabble.com/ST-Overlaps-and-performance-tp4555465p4555465.html
>> Sent from the PostGIS - User mailing list archive at Nabble.com.
>> _______________________________________________
>> postgis-users mailing list
>> [hidden email]
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: ST_Overlaps and performance

Basques, Bob (CI-StPaul)
In reply to this post by DrYSG

If the polygons are always going to be level (as indicated below) and non-rotated, you might be better off just using a regular Tabular select (and non-spatial geom) and use the minx,miny,max,may numbers directly.


if (x > minx && x < max && y > miny && y < maxy) then true. . . .


Maybe build a view of the bounding corners as numeric columns and try some simple queries to test. . . .  Sometimes the spatial stuff is just that much overhead for certain queries.


bobb



>>> DrYSG <[hidden email]> wrote:

I have a table with 21 Million Rows. The Geometry field is a simple
rectangular bounding box (Polygon with 4 corners).

Users will be doing executing queries to find all overlapping rows to a user
supplied rectangle. I have been finding that my naive query of:

SELECT *
FROM portal.metadata as cat
WHERE ST_Overlaps(cat.location,ST_GeomFromText('POLYGON((-70.9433962
41.5384615,-72.5283019 41.5384615,-72.5283019 41.1538462,-70.9433962
41.1538462,-70.9433962 41.5384615))', 4326));

Can take up to 40 seconds on a cold location. However, an ST_Contains query
of a point is only about 8 seconds. Yes, location is indexed using GIST.

CREATE INDEX catalog_location_idx
  ON portal.catalog
  USING gist
  (location );

Now, it first stuck me as odd that it was taking more than 4 times the point
query to discover overlap. Then I thought about it, and I can see cases
where there ST_Overlaps cannot assume certain geometries.

I am now considering doing a WHERE clause that checks for ST_Contains for
both the Top-Left and Bottom-Right corners of the QueryRectangle since I
know that all bboxes in the DB are rectangular.

But I thought I would see what those with knowledge of the internals of the
spatial functions have to say.

Ideas?



--
View this message in context: http://postgis.17.n6.nabble.com/ST-Overlaps-and-performance-tp4555465p4555465.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: ST_Overlaps and performance

Stephen Woodbridge
This makes indexing somewhat problematic. And if you try this in SQL
'&&' is NOT the same as 'AND', it is a spatial comparison.

-Steve

On 3/7/2012 12:36 PM, Bob Basques wrote:

> If the polygons are always going to be level (as indicated below) and
> non-rotated, you might be better off just using a regular Tabular select
> (and non-spatial geom) and use the minx,miny,max,may numbers directly.
>
>
> if (x > minx && x < max && y > miny && y < maxy) then true. . . .
>
>
> Maybe build a view of the bounding corners as numeric columns and try
> some simple queries to test. . . . Sometimes the spatial stuff is just
> that much overhead for certain queries.
>
>
> bobb
>
>
>
>  >>> DrYSG <[hidden email]> wrote:
>
> I have a table with 21 Million Rows. The Geometry field is a simple
> rectangular bounding box (Polygon with 4 corners).
>
> Users will be doing executing queries to find all overlapping rows to a user
> supplied rectangle. I have been finding that my naive query of:
>
> SELECT *
> FROM portal.metadata as cat
> WHERE ST_Overlaps(cat.location,ST_GeomFromText('POLYGON((-70.9433962
> 41.5384615,-72.5283019 41.5384615,-72.5283019 41.1538462,-70.9433962
> 41.1538462,-70.9433962 41.5384615))', 4326));
>
> Can take up to 40 seconds on a cold location. However, an ST_Contains query
> of a point is only about 8 seconds. Yes, location is indexed using GIST.
>
> CREATE INDEX catalog_location_idx
> ON portal.catalog
> USING gist
> (location );
>
> Now, it first stuck me as odd that it was taking more than 4 times the point
> query to discover overlap. Then I thought about it, and I can see cases
> where there ST_Overlaps cannot assume certain geometries.
>
> I am now considering doing a WHERE clause that checks for ST_Contains for
> both the Top-Left and Bottom-Right corners of the QueryRectangle since I
> know that all bboxes in the DB are rectangular.
>
> But I thought I would see what those with knowledge of the internals of the
> spatial functions have to say.
>
> Ideas?
>
>
>
> --
> View this message in context:
> http://postgis.17.n6.nabble.com/ST-Overlaps-and-performance-tp4555465p4555465.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: ST_Overlaps and performance

Puneet Kishor
In reply to this post by Basques, Bob (CI-StPaul)

On Mar 7, 2012, at 11:36 AM, Bob Basques wrote:

> if (x > minx && x < max && y > miny && y < maxy) then true. .



This is precisely the kind of problem that GiST solves. The above query is not likely to be better than a spatial query.




--
Puneet Kishor
_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: ST_Overlaps and performance

Basques, Bob (CI-StPaul)


I'll wait for the proof I think.  Just now starting to work with POSTGIS, and historically, large datasets have worked faster (for me) for this type of query when worked against a tabular numeric column.  If speed of return is the primary constraint . . .


I'll gladly jump on the wagon that this is not so with POSTGIS.   I don't have a 21 million record dataset to test with however.  :c)


bobb




>>> "Mr. Puneet Kishor" <[hidden email]> wrote:


On Mar 7, 2012, at 11:36 AM, Bob Basques wrote:

> if (x > minx && x < max && y > miny && y < maxy) then true. .



This is precisely the kind of problem that GiST solves. The above query is not likely to be better than a spatial query.




--
Puneet Kishor
_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: ST_Overlaps and performance

Basques, Bob (CI-StPaul)
In reply to this post by Stephen Woodbridge

That's my PERL heritage sneaking in, sorry.


bobb




>>> Stephen Woodbridge <[hidden email]> wrote:

This makes indexing somewhat problematic. And if you try this in SQL
'&&' is NOT the same as 'AND', it is a spatial comparison.

-Steve

On 3/7/2012 12:36 PM, Bob Basques wrote:


> If the polygons are always going to be level (as indicated below) and
> non-rotated, you might be better off just using a regular Tabular select
> (and non-spatial geom) and use the minx,miny,max,may numbers directly.
>
>
> if (x > minx && x < max && y > miny && y < maxy) then true. . . .
>
>
> Maybe build a view of the bounding corners as numeric columns and try
> some simple queries to test. . . . Sometimes the spatial stuff is just
> that much overhead for certain queries.
>
>
> bobb
>
>
>
>  >>> DrYSG <[hidden email]> wrote:
>
> I have a table with 21 Million Rows. The Geometry field is a simple
> rectangular bounding box (Polygon with 4 corners).
>
> Users will be doing executing queries to find all overlapping rows to a user
> supplied rectangle. I have been finding that my naive query of:
>
> SELECT *
> FROM portal.metadata as cat
> WHERE ST_Overlaps(cat.location,ST_GeomFromText('POLYGON((-70.9433962
> 41.5384615,-72.5283019 41.5384615,-72.5283019 41.1538462,-70.9433962
> 41.1538462,-70.9433962 41.5384615))', 4326));
>
> Can take up to 40 seconds on a cold location. However, an ST_Contains query
> of a point is only about 8 seconds. Yes, location is indexed using GIST.
>
> CREATE INDEX catalog_location_idx
> ON portal.catalog
> USING gist
> (location );
>
> Now, it first stuck me as odd that it was taking more than 4 times the point
> query to discover overlap. Then I thought about it, and I can see cases
> where there ST_Overlaps cannot assume certain geometries.
>
> I am now considering doing a WHERE clause that checks for ST_Contains for
> both the Top-Left and Bottom-Right corners of the QueryRectangle since I
> know that all bboxes in the DB are rectangular.
>
> But I thought I would see what those with knowledge of the internals of the
> spatial functions have to say.
>
> Ideas?
>
>
>
> --
> View this message in context:
> http://postgis.17.n6.nabble.com/ST-Overlaps-and-performance-tp4555465p4555465.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: ST_Overlaps and performance

DrYSG
Not a great improvement in performance using:

SELECT *
FROM portal.metadata as cat
WHERE cat.location && 'SRID=4326;POLYGON((-70.9433962
41.5384615,-72.5283019 41.5384615,-72.5283019 41.1538462,-70.9433962
41.1538462,-70.9433962 41.5384615))'::geometry;


13:27:44  [SELECT - 2862 row(s), 37.360 secs]  Result set fetched
... 1 statement(s) executed, 2862 row(s) affected, exec/fetch time: 37.360/0.073 sec  [1 successful, 0 warnings, 0 errors]

Reply | Threaded
Open this post in threaded view
|

Re: ST_Overlaps and performance

DrYSG
However, a cold query to a completely new location using this syntax is turning out to be much better.

SELECT *
FROM portal.catalog as cat
WHERE ST_Contains(cat.location,'SRID=4326;POINT(70.9433962 41.5384615)'::geometry) OR
      ST_Contains(cat.location,'SRID=4326;POINT(71.9433962 40.5384615)'::geometry)  ;

 13:38:41  [SELECT - 27 row(s), 4.160 secs]  Result set fetched
... 1 statement(s) executed, 27 row(s) affected, exec/fetch time: 4.160/0.000 sec  [1 successful, 0 warnings, 0 errors]

I don't have to be fussy about the exact size of the QueryRectangle, it is coming from a user drawing on a map.

However, the bbox's of the DB rectangles are aligned to lon/lat coordinates.

Reply | Threaded
Open this post in threaded view
|

Re: ST_Overlaps and performance

Stephen Woodbridge
What does EXPLAIN <query>; report back?


On 3/7/2012 1:41 PM, DrYSG wrote:

> However, a cold query to a completely new location using this syntax is
> turning out to be much better.
>
> SELECT *
> FROM portal.catalog as cat
> WHERE ST_Contains(cat.location,'SRID=4326;POINT(70.9433962
> 41.5384615)'::geometry) OR
>        ST_Contains(cat.location,'SRID=4326;POINT(71.9433962
> 40.5384615)'::geometry)  ;
>
>   13:38:41  [SELECT - 27 row(s), 4.160 secs]  Result set fetched
> ... 1 statement(s) executed, 27 row(s) affected, exec/fetch time:
> 4.160/0.000 sec  [1 successful, 0 warnings, 0 errors]
>
> I don't have to be fussy about the exact size of the QueryRectangle, it is
> coming from a user drawing on a map.
>
> However, the bbox's of the DB rectangles are aligned to lon/lat coordinates.
>
>
>
> --
> View this message in context: http://postgis.17.n6.nabble.com/ST-Overlaps-and-performance-tp4555465p4555690.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: ST_Overlaps and performance

DrYSG
The explain query for your query is:

"Index Scan using loc_idx on metadata cat  (cost=0.00..1050.88 rows=253 width=845)"

> SELECT *
> FROM portal.catalog as cat
> WHERE ST_Contains(cat.location,'SRID=4326;POINT(70.9433962
> 41.5384615)'::geometry) OR
>        ST_Contains(cat.location,'SRID=4326;POINT(71.9433962
> 40.5384615)'::geometry)  ;
Reply | Threaded
Open this post in threaded view
|

Re: ST_Overlaps and performance

DrYSG
explain analyze:

"Bitmap Heap Scan on catalog cat  (cost=57.58..6059.77 rows=1532 width=843) (actual time=2182.439..12578.026 rows=2649 loops=1)"

"  Recheck Cond: (location && '0103000020E61000000100000005000000EC8A749A60BC51C0DA53724EECC444408ABAC5B2CF2152C0DA53724EECC444408ABAC5B2CF2152C093CE763BB1934440EC8A749A60BC51C093CE763BB1934440EC8A749A60BC51C0DA53724EECC44440'::geometry)"

"  ->  Bitmap Index Scan on catalog_location_idx  (cost=0.00..57.19 rows=1532 width=0) (actual time=2181.640..2181.640 rows=2665 loops=1)"

"        Index Cond: (location && '0103000020E61000000100000005000000EC8A749A60BC51C0DA53724EECC444408ABAC5B2CF2152C0DA53724EECC444408ABAC5B2CF2152C093CE763BB1934440EC8A749A60BC51C093CE763BB1934440EC8A749A60BC51C0DA53724EECC44440'::geometry)"

"Total runtime: 12578.939 ms"
Reply | Threaded
Open this post in threaded view
|

Re: ST_Overlaps and performance

Stephen Woodbridge
In reply to this post by DrYSG
Explain looks good.

Have you done anything to tune postgresql to use more memory or are you
using the default parameters that came with the install. postgresql by
default is configured to use minimal memory. If its the default, then I
would google for "tuning postgresql" and look at shared_buffers
specifically, there are probably others that can be opened up also but
it really depends on the amount of memory you have, the OS, what other
applications are running and using memory concurrently, etc.

-Steve

On 3/7/2012 1:50 PM, DrYSG wrote:

> The explain query for your query is:
>
> "Index Scan using loc_idx on metadata cat  (cost=0.00..1050.88 rows=253
> width=845)"
>
>> SELECT *
>> FROM portal.catalog as cat
>> WHERE ST_Contains(cat.location,'SRID=4326;POINT(70.9433962
>> 41.5384615)'::geometry) OR
>>         ST_Contains(cat.location,'SRID=4326;POINT(71.9433962
>> 40.5384615)'::geometry)  ;
>
>
> --
> View this message in context: http://postgis.17.n6.nabble.com/ST-Overlaps-and-performance-tp4555465p4555707.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: ST_Overlaps and performance

DrYSG
Nothing else important is running on the machine.

24GB physical memory. PostGreSQL 9.1

Shared_Buffers Value=32MB   Current Value=4096

However I am running on a windows 7 machine and the advise on tuning says:

Note that on Windows (and on PostgreSQL versions before 8.1), large values for shared_buffers aren't as effective, and you may find better results keeping it relatively low and using the OS cache more instead. On Windows the useful range is 64MB to 512MB, and for earlier than 8.1 versions the effective upper limit is near shared_buffers=50000 (just under 400MB--older versions before 8.2 don't allow using MB values for their settings, you specify this parameter in 8K blocks).

Recommendations?

Reply | Threaded
Open this post in threaded view
|

Re: ST_Overlaps and performance

Nicklas Avén
In reply to this post by DrYSG
Hallo

If your query below is not the fastest of the different approaches I
think you are going into a classical trap.

It is probably notthe query that takes a long time but the output of the
result.

Find out by not returning all the resulting geometries but instaed just
count them.

Instead of using "SELECT *" use "SELSECT count(*)"
and compare the run-times.

the calculation with the && operator (the index-based bbox selecton) is
done behind the scenes when you use ST_Contains so it makes no sense
that ST_Contains should be faster in ay other way that it returns fewer
resulting polygons.


HTH
Nicklas


On Wed, 2012-03-07 at 10:30 -0800, DrYSG wrote:

> Not a great improvement in performance using:
>
> SELECT *
> FROM portal.metadata as cat
> WHERE cat.location && 'SRID=4326;POLYGON((-70.9433962
> 41.5384615,-72.5283019 41.5384615,-72.5283019 41.1538462,-70.9433962
> 41.1538462,-70.9433962 41.5384615))'::geometry;
>
>
> 13:27:44  [SELECT - 2862 row(s), 37.360 secs]  Result set fetched
> ... 1 statement(s) executed, 2862 row(s) affected, exec/fetch time:
> 37.360/0.073 sec  [1 successful, 0 warnings, 0 errors]
>
>
>
> --
> View this message in context: http://postgis.17.n6.nabble.com/ST-Overlaps-and-performance-tp4555465p4555662.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>


_______________________________________________
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users