[OSM-dev] Postgres 8.4/PostGIS 1.4 performance

[Frederik Ramm]

Hi,

    I think I made one more blunder when doing the comparison: I did one 
of the imports - the slower one actually! - with the -l flag, the other 
one without. (Both were with --slim.) But by now there are too many 
variables in the game and it is impossible to tell what is to blame for 
the longer running time. I'm now running a new import on postgres 8.3 
with osm2pgsql 0.67 and the -l switch on so that I have two test cases 
which are identical except in the Postgres/PostGIS version.

Jon Burgess wrote:
> Do you have the _int.sql loaded?

Yes, in both versions; however the _int that comes with PostGIS 1.4 
seems to be slightly modified, with new paramters added to 
ginint4_queryextract and ginint4_consistent. So it is not impossible 
that it performs worse.

> Did you move the nodes to a different table space as part of the
> performance tests?

No, *those* performance tests were done on a different machine (with 
identical hardware). This is a pristine installation.

> 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
[...]
> (22 rows)

Something must be definitely broken here - my ways_nodes table lists 455 
million entries, yours only 32 million. As if the size counters were 
mixed up in some way, and all my nodes counted towards my ways_nodes 
number or something.

I too get 22 rows but the node tables seem to be empty:

[...]
  planet_osm_nodes_pkey    | 8192 bytes |         0 |        1
  planet_osm_ways_idx      | 8192 bytes |         0 |        1
  planet_osm_ways_pkey     | 8192 bytes |         0 |        1
  planet_osm_rels_idx      | 8192 bytes |         0 |        1
  planet_osm_rels_pkey     | 8192 bytes |         0 |        1
  planet_osm_nodes         | 0 bytes    |         0 |        0

Still, the data is there:

osm=# select max(id) from planet_osm_nodes;
     max
-----------
  480684210
(1 row)

osm=# select * from planet_osm_nodes limit 10;
  id |    lat    |    lon    |                 tags
----+-----------+-----------+---------------------------------------
   2 | 501359444 |  83013034 | {place,village,name,Wiesbaden-Naurod}
   3 | 515284576 |  -1486064 |
   4 | 515220583 |  -1457854 |
   5 | 515288506 |  -1464925 |
   6 | 515288619 |  -1465242 |
   7 | 515287437 |  -1487777 |
   8 | 515296860 |  -1464353 |
   9 | 515284843 |  -1486444 |
  10 | 515289383 |  -1413791 |
  11 |  40840331 | 735129514 | {place,village,name,Embudu}

I would say something is broken in my Postgres installation but these 
results are exactly the same whether I start up the 8.3 or 8.4 version.

Selecting "reltablespace" from the pg_class table yields 0 for all 
planet_osm tables.

Bye
Frederik