Map calls are full of lazy-loading badness

[gravitystorm]

You can get 60k individual node select statements, if large ways extend beyond the bounding box. Needs investigating (with bullet) and optimising.

[gravitystorm]

This is hard to fix, because the has_many relations that we want to eager load have procs on them. There's an answer on stackoverflow that suggests that it's therefore not possible.

http://stackoverflow.com/questions/6684521/rails-including-associations-with-dynamic-conditions

One way forward may be to change the way_nodes and relation_members tables, so that they use object ids directly instead of osm_ids. Then they wouldn't need the project_id column, and the has_many definitions become radically simplified.

[pnorman]

Do we even want object IDs at all? I suggested removing them in an email message. They are not necessary from a SQL standpoint.

I wonder if perhaps SQL views are an option? Some view that makes it look like a normal pgsnapshot database where you only see the project-specific stuff.

[gravitystorm]

Yes, we want the object ids - I don't want to descend into the madness of using the composite primary keys plugin. Rails code is much easier to write and understand when the models have primary key ids.

But I do think the joining tables should be using these ids, rather than the osm_id + project_id constraints.

[pnorman]

I'm in favour of simplifying code, I just want you to be certain that it's not making it more complex elsewhere.

This will necessitate new code to load into the project tables as the SQL I provided will no longer work. A browser-based method won't work either as the .osm files I'm dealing with are 25G each. It still should be possible to write the SQL to do the move from pgsnapshot to project tables but it's definitely going to be more complicated and slower.

Part of me is reluctant about deviating any farther than necessary from the normal pgsnapshot schema + project_id and getting rid of the OSM node IDs from way_nodes is a pretty substantial deviation.

You're still going to need a project_id column for the way_id, sequence_id lookups. They're "pk_way_nodes" PRIMARY KEY, btree (way_id, sequence_id) in my instance and needed to get a list of all of the nodes of the way.