I have an AWS lambda function deployed with a spatialite table. When queried, a point lookup is done on the table to find the locations in the table containing the point.

The first execution of the function I typically see the query execute taking 3.3 seconds.

point_in_polygon_query = 'SELECT ssc_code16, ssc_name16, ste_name16 FROM suburbs WHERE within(PointFromText(\'POINT({lon} {lat})\'),suburbs.GEOMETRY)'
cur.execute(point_in_polygon_query.format(lat=str(lat), lon=str(lon)))

And the fetchall taking 6.6 seconds. rows = cur.fetchall()

(Determined by using AWS X-Ray subsegments)

However any subsequent runs take about 0.11 seconds each for the query execution and fetchall.

Why is the first execution of my function taking so long (compared to subsequent executions)?

Note: Subsequent query optimisation has significantly improved response times but I still want to understand the why.

  • I think @radouxju did not guess right because SpatiaLite does not build spatial indexes automatically. Perhaps your AWS is just making a cold start serverless.com/blog/keep-your-lambdas-warm. It is also possible that if your SpatiaLite db is small it can fit totally into the cache memory on the server – user30184 Nov 12 '19 at 15:28
  • The spatial indexes were built, but due to my investigation, the query as stated does not make use of them, it should not be an AWS lambda cold start issue as that would have been revealed by AWS X-ray. – user3559247 Nov 12 '19 at 21:45
  • Then make a query that is using r-tree and compare the speed. – user30184 Nov 12 '19 at 22:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.