[postgis-users] error in geometry operations

[Paul Ramsey]

I think this is just an issue of size, you have at least 200K rows on  
one side of the point, and depending on cross-matching, 5M on the far  
side... it's the size of the poly side which worries me.  If you have  
a gid on your poly table, try something like

SELECT  a.id, b.id
FROM    a, b
WHERE   a.gid < 100 AND a.the_geom && b.the_geom AND WITHIN 
(b.the_geom, a.the_geom)

And see if it's fast.  If it is, consider batching the process up as  
an iterative thing, rather than trying to do the whole thing in one go.

If you want to do it in one go, you may find that adding to your  
shared buffers, and increasing your work_mem quite substantially  
gives you a chance of succeeding.  If you haven't done any  
configuration tuning on your PostgreSQL, now is the time to do so.

<http://www.varlena.com/GeneralBits/Tidbits/perf.html>

On 20-Aug-06, at 3:32 PM, P Kishor wrote:

> On 8/20/06, Paul Ramsey <pramsey at refractions.net> wrote:
>> Versions of software?
>
> Postgres 8.1.4/PostGIS 1.1.3, WinXP SP2
>
>> Don't forget to run VACUUM ANALYZE after creating your indexes.
>
> Did that.
>
>> How much stuff in your tables? How many a's in b's?
> a couple of gigs of data...
> a: 5,115,110 rows (POINT)
> b: 210,763 rows (POLY)
>
>> What does the EXPLAIN output of this look like?
>
> rather long, but healthy... I am including below the last few lines
> (would be happy to send the whole shebang separately, if required).
>
> Limit  (cost=2.09..22311847.95 rows=1 width=20)
>  ->  Nested Loop  (cost=2.09..22311847.95 rows=1 width=20)
>        Join Filter: within("outer".the_geom, "inner".the_geom)
>        ->  Seq Scan on b  (cost=0.00..38507.71 rows=210571 width=2631)
>        ->  Bitmap Heap Scan on a  (cost=2.09..105.39 rows=26 width=29)
>              Filter: (a.the_geom && "outer".the_geom)
>              ->  Bitmap Index Scan on idx_a_the_geom
> (cost=0.00..2.09 rows=26 width=0)
>                    Index Cond: (a.the_geom && "outer".the_geom)
>
>
>> On 20-Aug-06, at 3:12 PM, P Kishor wrote:
>>
>> > -- Executing query:
>> > SELECT  a.id, b.id
>> > FROM    a, b
>> > WHERE   a.the_geom && b.the_geom AND WITHIN(b.the_geom, a.the_geom)
>> > LIMIT 10
>> >
>> > oh crap! row number -1 is out of range 0..-1
>> > Total query runtime: 360258 ms.
>> >
>> >
>> > any ideas, anyone?
>> >
>> > additionally, for a query like above, what all indexes are  
>> advised? I
>> > have the_geom in both tables indexed using GiST, fwiw.
>
>
> -- 
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Inst. for Env. Studies, UW-Madison http://www.ies.wisc.edu/
> Open Source Geospatial Foundation https://edu.osgeo.org/
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users