[OSM-dev] osm2pgsql - incomplete import (missing indexing), possible to add indexes manually?

[Nick Whitelegg]

Sorry - I've just re-read the log and indexes were added on planet_osm_line ! Not sure how I missed that !


However I'm guessing it might be that as the import didn't complete fully, the database might have been left in a sub-optimal state, is this the case or can I safely use it without further optimisation?

Thanks,

Nick


________________________________
From: Nick Whitelegg <nick.whitelegg at solent.ac.uk>
Sent: 19 March 2019 17:20:29
To: dev at openstreetmap.org
Subject: [OSM-dev] osm2pgsql - incomplete import (missing indexing), possible to add indexes manually?



Hi,


I've attempted to import all highways and selected POIs in Europe into an osm2pgsql database.


It has mostly worked (in the sense that the data I want is in the DB), but failed towards the end due to insufficient disc space during the process of indexing (see below):


The planet_osm_line and planet_osm_point have been populated successfully, however it appears that indexes were never added on planet_osm_line (they were added on planet_osm_point). Consequently line queries are slow and point queries faster.


Extract from my log:


WARNING:  there is no transaction in progress
Sorting data and creating indexes for planet_osm_point
Stopping table: planet_osm_nodes
Stopped table: planet_osm_nodes in 0s
Stopping table: planet_osm_ways
Building index on table: planet_osm_ways
Stopping table: planet_osm_rels
Building index on table: planet_osm_rels
Sorting data and creating indexes for planet_osm_line
Sorting data and creating indexes for planet_osm_polygon
Sorting data and creating indexes for planet_osm_roads
Stopped table: planet_osm_rels in 0s
node cache: stored: 263295984(54.84%), storage efficiency: 50.22% (dense blocks: 933, sparse nodes: 258322433), hit rate: 56.39%
Osm2pgsql failed due to ERROR: CREATE TABLE planet_osm_point_tmp  AS
  SELECT * FROM planet_osm_point
    WHERE ST_IsValid(way)
    ORDER BY ST_GeoHash(ST_Transform(ST_Envelope(way),4326),10)
    COLLATE "C" failed: ERROR:  could not write block 10861 of temporary file: No space left on device

After this I deleted all the tables I don't need e.g. planet_osm_roads and planet_osm_ways, meaning I now have 52 GB free (out of 80).

I don't want to use a bigger server if I can possibly help it as I only need the disc space for the import process, not for its day-to-day running.


At this stage, I presume I can add an index manually, but what should I add the index on?

Do I need the temp tables that I deleted?


Thanks,

Nick


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20190319/0f0846d5/attachment.html>