Optimisation of writing join table records

Question

The application is based on PostGIS and stores data using RGeo's simple_mercator_factory.

Polygon records are created and associations with points are static (i.e. don't need updating). To reduce overhead on postGIS calculations, it makes sense to populate a join table with the points belonging to a polygon and the conduct searches on an indexed join table using bTree (in lieu of rTree).

The problem is efficient creation of the join records. At present:

@line_string1 = RGeo::Geographic.simple_mercator_factory.line_string([@point_a, @point_b, @point_c, @point_d])
@points = Point.all
@points_in ||= []
@points.each do |point|
  this_point = point.lonlat
  @this_poly = RGeo::Geographic.simple_mercator_factory.polygon(@line_string1)
  if this_point.intersects?(@this_poly)
      @add_point = pointpolygon.new(:point_id => point.id, :polygon_id => @polygon.id)
      @add_point.save
  end
end

The query plan is acceptable

EXPLAIN for: SELECT "point".* FROM "points"
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on points  (cost=0.00..210.10 rows=8110 width=99)
(1 row)

However, the @add_point function is clocking between 14 and 16 ms. For a set of 83 records, we're looking a something like 1.6 seconds. But the totals do not match up:
Completed 302 Found in 7796.9ms (ActiveRecord: 358.5ms)
Running a separate method that does the same query plan (and times), without writing join records completes in
Completed 200 OK in 1317.5ms (Views: 49.8ms | ActiveRecord: 64.0ms)
Two questions arise. More mundanely, why has the total ballooned so much - I was expecting something like 3 seconds (1.6 + 1.3) - aside from development mode conditions?

But more to the point, is there a way to wrap off the writing of the join table records to a separate thread (after_update?) in a more efficient manner (considering 1000 records could to be written...)

Answer 1

As @Jakub correctly points out, a method for extracting all the valid points in one shot:

def valid_points
  Point.order("id").joins("INNER JOIN points ON points.id=#{id} AND st_contains(polygon.poly, points.lonlat)").all
end  

which then invoked by the controller

  @valid_points = @polygon.valid_points
  @valid_points.each do |point|
    @add_point = Pointpolygon.new(:point_id => point.id, :polygon_id => @polygon.id)
    @add_point.save
  end

yields much nicer response times. For test cases up to 1000 matches, the creation time per record creation is between 1.2 and 1.4 ms in development mode.