R: RE: [postgis-users] Performance question (how many years willthistake?)

andrea.aime at aliceposta.it andrea.aime at aliceposta.it
Tue May 11 00:50:49 PDT 2004


Greg,
thank you for the prompt answer :-)
Now, if I understand correctly they are sudviding a big quey into a set of smaller ones that,
put togheter, perform exactly the same work... 
if this optimizes performance, then there is something wrong with the way postgres 
performs the query... I mean, usually the fastest way is to issue a query that can 
work completely in the database kernel without the need of interacting with the client...

Best regards
Andrea Aime

> 
> Da: gsw at globexplorer.com
> Data: May 11, 2004 8:46:07 AM
> A: PostGIS Users Discussion <postgis-users at postgis.refractions.net>, postgis-users at postgis.refractions.
net
> Ogg: RE: [postgis-users] Performance question (how many years	willthistake?)
> 
> Andrea --
> 
> Because the cell_id column has an index on it the optimizer in the postgres database will probably 
try to use it reduce the total number of disk accesses. The "&&" operator referred to in an earlier 
post will have the same effect with the spatial index on the columns it refers to.
> 
> The range in this SQL ($START_ID - $END_ID) will allow for the selection of a very small number 
of rows at a time, compared with the total number of rows. Again, reduces disk I/O as postgres does 
not have to ask for as many possible rows as viable answers to the query.
> 
> The $CELL_ID reference I don't understand ... seems like it might be either a variable with the 
value of cell_id or a typographic error.
> 
> Greg Williamson
> DBA
> GlobeXplorer LLC
> 
> -----Original Message-----
> From:	Andrea Aime [mailto:andrea.aime at aliceposta.it]
> Sent:	Mon 5/10/2004 11:30 PM
> To:	postgis-users at postgis.refractions.net
> Cc:	
> Subject:	Re: [postgis-users] Performance question (how many years willthistake?)
> Alle 04:04, martedì 11 maggio 2004, Wood Brent ha scritto:
> 
> ...
> 
> > 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)
> 
> Sorry, but I've never seen that sintax... what's this and why it improves the
> overlay process?
> "where cell_id >= $START_ID and $CELL_ID < $END_ID"
> 
> Best regards
> Andrea Aime
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 




More information about the postgis-users mailing list