[postgis-users] Performance question (how many years will this take?)

[Wood Brent]

--- Paul Ramsey <pramsey at refractions.net> wrote:
> Wood Brent wrote:
> 
> > select into...
> > ...
> > ...
> > where intersects(uniq_event.the_geom, chat_cells3.the_geom);
> 
> Is that really your where clause? You want this:
> 
>    where intersects(uniq_event.the_geom, chat_cells3.the_geom)
>    and uniq_event.the_geom && chat_cells3.the_geom
> 
> So that the spatial index cuts down on the number of comparisons that 
> have to be made with the expensive exact intersects() function.


I assume from this that _intersects_ does NOT do a pre-check with the GiST
bounding box before the actual polygon check?


Anyway, that appeared to run much faster, until it exited with out-of-memory
errors. Went well up into over 2G of swap being used.

What I have now done is add a 
    "where cell_id >= $START_ID and $CELL_ID < $END_ID"
into the sql, and wrap it up in a script which starts at cell_id of 0 and
increments in batches of 100 cells per iteration until it gets to the maximum
cell_id. (note: there is a unique index on cell_id which is an integer attr)


This seems faster & is running without the postmaster process swapping out.

The output table is getting up to 40,000 records per 100 cells so far, and is
only taking a couple of minutes at most per 100 cell batch. 


So I think this will work OK, it's about 30% done already, while I wrote this. 



Thanks,


   Brent Wood