My machine has been importing North America data for the past 16 days now, which to me seems unusually slow. The output currently shows:

...
CREATE INDEX
CREATE INDEX
CREATE INDEX

I checked what postgreSQL is doing by:

postgres=# select * from pg_stat_activity;
datid | datname  | procpid | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          | waiting |          current_query
-------+----------+---------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+---------+---------------------------------
12780 | postgres |   22028 |       10 | postgres | psql             |   |                 |          -1 | 2013-10-28 10:45:35.774566-07 | 2013-10-28 10:47:05.063072-07 | 2013-10-28 10:47:05.063072-07 | f       | select * from pg_stat_activity;
(1 row)

Does that mean the machine is still indexing data? When can I expect the importing to finish? Thanks!

EDIT: as one of the person answered below, I tried

nominatim=# \d place
                Table "public.place"
    Column    |          Type           | Modifiers
--------------+-------------------------+-----------
 osm_type     | character(1)            | not null
 osm_id       | bigint                  | not null
 class        | text                    | not null
 type         | text                    | not null
 name         | hstore                  |
 admin_level  | integer                 |
 housenumber  | text                    |
 street       | text                    |
 addr_place   | text                    |
 isin         | text                    |
 postcode     | text                    |
 country_code | character varying(2)    |
 extratags    | hstore                  |
 geometry     | geometry(Geometry,4326) | not null
Indexes:
    "idx_place_osm_unique" UNIQUE, btree (osm_id, osm_type, class, type)
    "place_id_idx" btree (osm_type, osm_id)
Triggers:
    place_before_delete BEFORE DELETE ON place FOR EACH ROW EXECUTE PROCEDURE pl
ace_delete()
    place_before_insert BEFORE INSERT ON place FOR EACH ROW EXECUTE PROCEDURE pl
ace_insert()

Does this mean my indexing is completed? If so, why does my pg_stat_activity still show recent timestamps?

asked 28 Oct '13, 18:13

baekacaek's gravatar image

baekacaek
176121317
accept rate: 0%

edited 29 Oct '13, 22:40


Index creation is the final step of the import and there is no explicit end-of-import message. If you don't see any activity in pg_stat_activity there is a good chance that you are done. The very last index to be created is idx_place_osm_unique on table place. If this index exists, the import is finished, feel free to abort the script.

permanent link

answered 28 Oct '13, 21:38

lonvia's gravatar image

lonvia
5.7k25381
accept rate: 41%

1

How can I check the last created index?

(28 Oct '13, 21:54) baekacaek
1

Check the indexes on place by login into the database with pgsql -d nominatim and then listing the table with \d place.

(28 Oct '13, 21:57) lonvia

If I am not confusing things, PostgreSQL will lump all the data and index creations into one transaction, therefore, logging into the database will not show anything until everything is done. In other words, no incomplete/partial data/indexes will be shown. (confirmed to be the case for the main APIDB database at least, I do not know for the nominatim operations)

(29 Oct '13, 08:32) MCPicoli

Thanks, I edited my question with an output. It's hard to tell if idx_place_osm_unique was actually created or not from that output.

(29 Oct '13, 22:42) baekacaek
1

You can see that idx_place_osm_unique is listed in the Indexes section in your output. That means that the index is there. You can try your installation now by setting up the website and running a few test queries.

(29 Oct '13, 23:06) lonvia
1

Thanks, turns out it was finished importing (or at least I think it's finished, judging by how I can query locations)

(30 Oct '13, 22:05) baekacaek
showing 5 of 6 show 1 more comments

Are the hard drives lights active? CPU usage is high (PostgreSQL CPU usage is equivalent of 100% of one core)? If so, data is still being indexed. Based on my previous experience loading the whole world file, using a not so fast computer (i7 3770k, 32GB RAM, 6 independent SATA drives, each containing a tablespace of part of the full schema), the process takes a few weeks to complete.

However, if there is no drive activity, maybe something wrong happened. Again, in my case, there was one time that my cat (in portuguese!) almost broke off one of the eSATA connectors. PostgreSQL did not crash, but drive speed plummeted to about 100kB/s. Had to interrupt the process, losing (in portuguese!) weeks of work, and restart.

So, in short, be very, very patient.

EDIT: Your "select * from pg_stat_activity" query is the only one showing in the query. If there was some index creation, there would be other lines in the result set. Repeat the query a few more times and see if anything else appears. If not, probably everything if fine, as noted by the other commenters.

permanent link

answered 28 Oct '13, 18:26

MCPicoli's gravatar image

MCPicoli
2.1k123046
accept rate: 24%

edited 30 Oct '13, 01:06

I don't understand

permanent link

answered 31 Oct '13, 04:32

hainam1610's gravatar image

hainam1610
111
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×614

question asked: 28 Oct '13, 18:13

question was seen: 9,681 times

last updated: 31 Oct '13, 04:32

powered by OSQA