[OSM-dev] Postgres 8.4/PostGIS 1.4 performance

Jon Burgess jburgess777 at googlemail.com
Sun Sep 13 19:27:35 BST 2009


On Sun, 2009-09-13 at 19:56 +0200, Frederik Ramm wrote:
> Hi,
> 
>     I did a full (osm2pgsql) planet import on a standard Ubuntu Jaunty 
> system and it took 1794 minutes. Then I upgraded to Postgres 8.4 
> (backported from Karmic) plus PostGIS 1.4 (home-built package), and 
> re-tried the import: 2028 minutes (that's 13% performance loss).
> 
> Can anybody confirm this measurement, or even suggest what the reason 
> might be? I used the same postgres.conf for both databases. I noted that 
> Postgres 8.4 produced 25% more data in the pg_xlog directory than 8.3 
> did, could that hint at the reason for the difference?

Try running the query below and compare the sizes returned for the
tables & indexes on the two databases.

gis=> SELECT relname, pg_size_pretty(relpages::bigint * 8 *1024) AS
size, CASE WHEN relkind = 't' THEN (SELECT pgd.relname FROM pg_class pgd
WHERE pgd.relfilenode::text = SUBSTRING(pg.relname FROM 10)) ELSE
(SELECT pgc.relname FROM pg_class pgc WHERE pg.reltoastrelid =
pgc.relfilenode) END AS refrelname, relfilenode, relkind,
reltuples::bigint, relpages FROM pg_class pg ORDER BY relpages DESC;


The largest table is normally planet_osm_nodes. With the latest
osm2pgsql code & postgresql-8.3.7 the size of each row is typically 36
bytes (more if the node has tags):

gis=> select pg_column_size(planet_osm_nodes.*) from planet_osm_nodes
limit 10;
 pg_column_size
----------------
            109
             36
             36
             36
             36
             36
             36
             36
            101
             36
(10 rows)

This is with the latest osm2pgsql code which stores the node lat/long as
32 bit ints. This is a recent change, until about a month ago it was
storing them as doubles and needed 48 bytes per row.

	Jon






More information about the dev mailing list