[OSM-dev] Postgres 8.4/PostGIS 1.4 performance

[Jon Burgess]

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