Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a database with osm data imported with osmosis tool. I want to search for the nearest POIs from a given point. For this purpose, I perform this query:

select id from nodes
where geom='0101000020E6100000546C713216920DC065767176C62C4540'

After this I would like to search tags for the selected node in node_tags table but my problem is that the database has 31 million rows, and the query takes more than 30 seconds. I have this columns in node tables:

  • id, version, user_id, tstamp, changeset_id, geom

I run the following sentence to ensure index in "geom" column:

CREATE INDEX idx_nodes_geom
ON nodes USING gist (geom );

But it doesn't work. Query takes more than 30 seconds.

I have the following configuration in PostgreSQL database:

  • Shared buffers: 768 MB
  • Work_mem: 32MB
  • maintenance_work_mem: 256 MB
  • wal_buffers: 2 MB
  • checkpoint_segments: 12
  • random_page_cost: 2.0
  • seq_page_cost: 1.0

Any idea to improve it?

share|improve this question
What Server is Postgres on? How much RAM? 4-16GB would ideal. Indexing… – Mapperz Nov 14 '12 at 21:10
User has cloned this post here: – Craig Ringer Nov 14 '12 at 23:30
BTW, you should really show explain analyze output when asking performance questions. Paste it to and link to it in the question. – Craig Ringer Nov 14 '12 at 23:32
up vote 6 down vote accepted

When creating a spatial index on a table it is important to run "vacuum analyze <table>" after that.

For finding nearest points you can use operator <-> introduced in PostGIS 2.0. It actually gives you the distance between two points. More info can be found here:

  nodes.geom <-> 'SRID=4326;POINT(60.220 24.930)'::geometry
share|improve this answer
Thank you very much! This was very helpful for me, now I get results in 1 second aprox.!! – Iñigo Nov 14 '12 at 21:43

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.