[OSM-dev] Postgres 8.4/PostGIS 1.4 performance

[Frederik Ramm]

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 ;-)

Bye
Frederik