[OSM-dev] Postgres 8.4/PostGIS 1.4 performance
Frederik Ramm
frederik at remote.org
Sun Sep 13 22:59:05 BST 2009
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
More information about the dev
mailing list