On Sun, 2009-09-13 at 19:56 +0200, Frederik Ramm wrote:
> Hi,
>
> I did a full (osm2pgsql) planet import on a standard Ubuntu Jaunty
> system and it took 1794 minutes. Then I upgraded to Postgres 8.4
> (backported from Karmic) plus PostGIS 1.4 (home-built package), and
> re-tried the import: 2028 minutes (that's 13% performance loss).
>
> Can anybody confirm this measurement, or even suggest what the reason
> might be? I used the same postgres.conf for both databases. I noted that
> Postgres 8.4 produced 25% more data in the pg_xlog directory than 8.3
> did, could that hint at the reason for the difference?
Try running the query below and compare the sizes returned for the
tables & indexes on the two databases.
gis=> SELECT relname, pg_size_pretty(relpages::bigint * 8 *1024) AS
size, CASE WHEN relkind = 't' THEN (SELECT pgd.relname FROM pg_class pgd
WHERE pgd.relfilenode::text = SUBSTRING(pg.relname FROM 10)) ELSE
(SELECT pgc.relname FROM pg_class pgc WHERE pg.reltoastrelid =
pgc.relfilenode) END AS refrelname, relfilenode, relkind,
reltuples::bigint, relpages FROM pg_class pg ORDER BY relpages DESC;
The largest table is normally planet_osm_nodes. With the latest
osm2pgsql code & postgresql-8.3.7 the size of each row is typically 36
bytes (more if the node has tags):
gis=> select pg_column_size(planet_osm_nodes.*) from planet_osm_nodes
limit 10;
pg_column_size
----------------
109
36
36
36
36
36
36
36
101
36
(10 rows)
This is with the latest osm2pgsql code which stores the node lat/long as
32 bit ints. This is a recent change, until about a month ago it was
storing them as doubles and needed 48 bytes per row.
Jon
_______________________________________________
dev mailing list
dev@openstreetmap.org
http://lists.openstreetmap.org/listinfo/dev
