On 22/02/2010 21:00, River Tarnell wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Peter Körner: >> After an ANALYZE it does an Index Scan, but this is not really faster: > > I've replaced the partial index with a standard index on pending, for > both planet_osm_ways and planet_osm_rel. This seems to improve the > query speed: > > osm_mapnik=# explain analyze select id from planet_osm_ways where pending; > QUERY PLAN > - > ------------------------------------------------------------------------------------------------------------------------------------------ > Index Scan using planet_osm_ways_pending on planet_osm_ways > (cost=0.00..12.84 rows=1 width=4) (actual time=0.248..0.248 rows=0 loops=1) > Index Cond: (pending = true) > Filter: pending > Total runtime: 0.263 ms > (4 rows) > > Does this improve the diff import time at all?
Looking a little closer at the timing of log statements appearing, it appears as if the the pending calls are no longer the problem. Never the less, talking to Peter and Jon Burgess on irc last night, it appears as if we are still seeing some general performance issues. At least on some queries it seems as if there is up to a factor of 10 difference under some conditions in comparison between ptolemy and yevaud. As an example the query "explain analyze select count(*) from planet_osm_roads where route='ferry';" took 55 seconds on yevaud compare to 150 seconds on ptolemy. In the cache hot case, i.e. running the statement immediately again, the time on yevaud went down to 8 seconds, whereas for ptolemy it still stayed at 105 seconds. So there appears to be a much smaller effect of caching. There were other queries that showed similar results too. One potential large difference between the two setups is the 500+ extra columns introduced by the name:* and wikipedia:* tags in the style. But at least the raw table sizes don't show that much difference. Tables on Ptolemy are about 10-20% larger in size. The planet_osm_roads table used in the query above for example is 1.3Gb compared to 1.1Gb in size. Jon wanted to post the postgres config of yevaud at some point to see if there are any of the tunings done on yevaud that might help performance on ptolemy. Kai P.S. Peter, is there a reason you dropped the maxInterval number in the osmosis configuration down to 15 minutes? It can probably stay larger, so that in case the diff import ever falls way behind it can catch up in larger steps. Also, it is probably possible to drop the interval of calling osmosis down to one minute now if you wish. > > - river. > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.10 (HP-UX) > > iEYEARECAAYFAkuC8IAACgkQIXd7fCuc5vIXuwCdHd1UgVjXPqilZgqMe7cnHOZw > NJgAniq9ioIuI3lNp6mABESHFmp2R/ps > =MMch > -----END PGP SIGNATURE----- > > _______________________________________________ > Maps-l mailing list > [email protected] > https://lists.wikimedia.org/mailman/listinfo/maps-l _______________________________________________ Maps-l mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/maps-l
