Hi Thomas, could you run the (long running) queries with explain/explain analyze and post the results on the wiki ?
Clue: EXPLAIN SELECT ...; or EXPLAIN ANALYZE SELECT ...; The information given by explain are quite useful to optimize queries. Thanks a lot for this actions. 2010/2/27 Thomas Petazzoni <[email protected]>: > Hi Vincent, Jean-Christophe, Guilhem and Sébastien, > > [ This message is CC'ed to the development mailing list of MapOSMatic, > which is why I'm writing to you in english. Please keep the list in > the CC list even if you're not subscribed. I'll moderate your > messages manually. Thanks! ] > > I'm writing to you concerning a PostgreSQL/PostGIS request optimization > problem we have in MapOSMatic (http://www.maposmatic.org). The request > in question is the one that gives us the list of the streets and the > squares they intersect for a given city. > > Currently, for a city like Toulouse, the request takes 4 minutes and 39 > seconds, but our experiment show that it could be optimized down to 3.2 > seconds. But we don't know exactly how to generalize our experiments of > optimization. > > Inside of writing a long e-mail, I've written a detailed explanation of > the request, the problem and the analysis we've done so far at > http://wiki.maposmatic.org/doku.php?id=dev:request_optimization. > > If you have the time, could you have a look and tell us what we are > doing wrong ? Solving this problem would probably allow us to limit the > load on the server and to generate maps for larger cities. Of course, > if you need details about the request or the environment, don't > hesitate. > > Thanks a lot for your help, > > Thomas > -- > Thomas Petazzoni http://thomas.enix.org > Promouvoir et défendre le Logiciel Libre http://www.april.org > Logiciels Libres à Toulouse http://www.toulibre.org > -- Jean-Christophe Arnu
