On Sun, 2009-09-13 at 22:45 +0200, Frederik Ramm wrote:
> Hi,
>
> Jon Burgess wrote:
> > Try running the query below and compare the sizes returned for the
> > tables & indexes on the two databases.
>
> Hm, it seems I have overlooked the fact that I also updated osm2pgsql
> when I did the update. The old database was created with 0.66. But it
> already had integers in the node lat/lon columns. Funny thing is, your
> query does not list the nodes table although it definitely is there and
> has data:
>
> Postgres 8.4/PostGIS 1.4/osm2pgsql 0.67:
>
> relname | size | reltuples | relpages
> ------------------------------------+------------+-----------+----------
> planet_osm_ways_nodes | 18 GB | 455575744 | 2406878
> planet_osm_line | 7104 MB | 27410148 | 909316
> planet_osm_ways | 6364 MB | 32382898 | 814552
> planet_osm_line_index | 1607 MB | 27410148 | 205720
> planet_osm_polygon | 1247 MB | 4274538 | 159647
> planet_osm_roads | 800 MB | 2527240 | 102446
> planet_osm_point | 615 MB | 7963185 | 78728
> planet_osm_line_pkey | 587 MB | 27410148 | 75093
> planet_osm_point_index | 464 MB | 7963185 | 59423
> planet_osm_polygon_index | 260 MB | 4274538 | 33337
> planet_osm_point_pkey | 171 MB | 7963185 | 21825
> planet_osm_roads_index | 151 MB | 2527240 | 19373
> planet_osm_polygon_pkey | 92 MB | 4274538 | 11735
> planet_osm_rels | 84 MB | 171433 | 10727
> planet_osm_rels_parts | 67 MB | 2058555 | 8545
> planet_osm_roads_pkey | 54 MB | 2527240 | 6929
>
> Postgres 8.3/PostGIS 1.3.3/osm2pgsql 0.66:
>
> relname | size | reltuples | relpages
> -----------------------------------+------------+-----------+----------
> planet_osm_ways_nodes | 20 GB | 455575744 | 2577003
> planet_osm_line | 7105 MB | 27520596 | 909401
> planet_osm_ways | 6373 MB | 32382898 | 815762
> planet_osm_line_index | 1613 MB | 27520596 | 206460
> planet_osm_polygon | 1248 MB | 4323558 | 159700
> planet_osm_roads | 800 MB | 2525497 | 102457
> planet_osm_point | 615 MB | 7956908 | 78728
> planet_osm_line_pkey | 587 MB | 27520596 | 75093
> planet_osm_point_index | 464 MB | 7956908 | 59423
> planet_osm_polygon_index | 260 MB | 4323558 | 33337
> planet_osm_point_pkey | 171 MB | 7956908 | 21825
> planet_osm_roads_index | 151 MB | 2525497 | 19373
> planet_osm_polygon_pkey | 92 MB | 4323558 | 11735
> planet_osm_rels_parts | 85 MB | 2058555 | 10904
> planet_osm_rels | 84 MB | 171433 | 10727
> planet_osm_roads_pkey | 54 MB | 2525497 | 6929
>
>
> I double checked that lat/lon is stored as integers on both (36 bytes
> per record is the norm on both).
>
> I'll re-run the full import with the exact same osm2pgsql version to
> have perfect conditions for comparison. - Maybe osm2pgsql could create a
> table where it records its version number and possibly also information
> about the style file used, so one can track these things more easily.
>
> I'll report back in about 2000 minutes then ;-)
Do you have the _int.sql loaded?
Did you move the nodes to a different table space as part of the
performance tests?
This is what I get...
gis=> SELECT relname, pg_size_pretty(relpages::bigint * 8 *1024) AS
size, reltuples::bigint, relpages FROM pg_class pg where relname like
'planet_osm_%' ORDER BY relpages DESC;
relname | size | reltuples | relpages
--------------------------+---------+-----------+----------
planet_osm_ways_nodes | 21 GB | 32934624 | 2738983
planet_osm_nodes | 18 GB | 421706720 | 2333748
planet_osm_nodes_pkey | 9007 MB | 421706720 | 1152902
planet_osm_line | 8146 MB | 27697808 | 1042707
planet_osm_ways | 6814 MB | 32934624 | 872156
planet_osm_line_index | 1780 MB | 27703332 | 227880
planet_osm_polygon | 1363 MB | 4392675 | 174451
planet_osm_ways_pkey | 1178 MB | 32934624 | 150754
planet_osm_roads | 931 MB | 2553120 | 119107
planet_osm_line_pkey | 706 MB | 27697808 | 90312
planet_osm_point | 615 MB | 7949091 | 78727
planet_osm_point_index | 464 MB | 7949091 | 59434
planet_osm_polygon_index | 271 MB | 4392675 | 34681
planet_osm_point_pkey | 171 MB | 7949091 | 21825
planet_osm_ways_idx | 170 MB | 24837 | 21780
planet_osm_roads_index | 157 MB | 2553120 | 20132
planet_osm_rels_parts | 103 MB | 179291 | 13168
planet_osm_polygon_pkey | 97 MB | 4392675 | 12418
planet_osm_rels | 95 MB | 179291 | 12220
planet_osm_roads_pkey | 59 MB | 2553120 | 7556
planet_osm_rels_pkey | 4544 kB | 179291 | 568
planet_osm_rels_idx | 56 kB | 0 | 7
(22 rows)
Jon
_______________________________________________
dev mailing list
dev@openstreetmap.org
http://lists.openstreetmap.org/listinfo/dev
