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:
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?