Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Profile a tm2source for slow queries
#!/usr/bin/env python
import logging
from optparse import OptionParser
import os
import sys
import mercantile
import psycopg2
import yaml
SCALE_DENOMINATOR = [
559082264.028,
279541132.014,
139770566.007,
69885283.0036,
34942641.5018,
17471320.7509,
8735660.37545,
4367830.18772,
2183915.09386,
1091957.54693,
545978.773466,
272989.386733,
136494.693366,
68247.3466832,
34123.6733416,
17061.8366708,
8530.9183354,
4265.4591677,
2132.72958385,
]
def process_yaml(path, location, connection=None, min_zoom=0, max_zoom=14):
with open(path, "rb") as f:
yaml_doc = yaml.load(f)
results = {}
for layer in yaml_doc['Layer']:
if layer['Datasource']['type'] != 'postgis':
logging.debug("Skipping layer " + layer['id'])
continue
if not layer['id'] in results:
results[layer['id']] = {}
logging.debug("processing layer " + layer['id'])
query_template = "SELECT * FROM {table} WHERE {geometry_field} && !bbox!".format(**layer['Datasource'])
logging.debug("QUery template:\n%s" % query_template)
for zoom in range(min_zoom, max_zoom + 1):
tile = mercantile.tile(location[1], location[0], zoom)
tile_ul = mercantile.xy(*mercantile.ul(*tile))
tile_lr = mercantile.xy(*mercantile.ul(tile[0] + 1, tile[1] + 1, tile[2]))
query = query_template.replace("!scale_denominator!", str(SCALE_DENOMINATOR[zoom]))
query = query.replace("!bbox!", "ST_SetSRID('BOX3D(%f %f, %f %f)'::box3d, 900913)" %
(tile_ul[0], tile_ul[1], tile_lr[0], tile_lr[1]))
query = query.replace("!pixel_width!", str(abs(tile_lr[0] - tile_ul[0])/256))
query = query.replace("!pixel_height!", str(abs(tile_lr[1] - tile_ul[1])/256))
cursor = connection.cursor()
try:
cursor.execute("EXPLAIN ANALYZE " + query)
query_plan = [r[0] for r in cursor.fetchall()]
except Exception, e:
logging.error("Error executing query: %s" % e)
continue
results[layer['id']][zoom] = {
"tile": tile,
"query": query,
"query_plan": query_plan,
"execution_time": float(query_plan[-1].replace("Execution time: ", "").replace(" ms", "")),
"planning_time": float(query_plan[-2].replace("Planning time: ", "").replace(" ms", "")),
}
return results
def _main():
usage = "usage: %prog data.yml"
parser = OptionParser(usage=usage,
description="")
parser.add_option("-d", "--debug", action="store_true", dest="debug",
help="Turn on debug logging")
parser.add_option("-q", "--quiet", action="store_true", dest="quiet",
help="turn off all logging")
parser.add_option("-v", "--verbose", action="store_true", dest="verbose")
parser.add_option("-a", "--all-stats", action="store_true", dest="all_stats",
help="Print stats for all queries, not just slow queries")
parser.add_option("--database", dest="database", default="osm_planet")
parser.add_option("--host", dest="host", default="localhost")
parser.add_option("--user", dest="user")
parser.add_option("--location", dest="location", default="38.9373,-120.0585")
parser.add_option("--min-zoom", dest="min_zoom", type="int", default=0)
parser.add_option("--max-zoom", dest="max_zoom", type="int", default=14)
parser.add_option("--slow", dest="slow", type="int", default=5,
help="Threshold for a query to be considered slow, in ms")
(options, args) = parser.parse_args()
logging.basicConfig(level=logging.DEBUG if options.debug else
(logging.ERROR if options.quiet else logging.INFO))
location = [float(f) for f in options.location.split(",")]
if len(location) != 2:
logging.error("invalid coordinate")
sys.exit(-1)
if len(args) != 1:
logging.error("Error, expecting 1 argument")
sys.exit(-1)
try:
connection = psycopg2.connect("dbname='%s' user='%s' host='%s'" % (options.database, options.user, options.host))
except Exception, e:
logging.error("Error connecting to database: %s" % e)
sys.exit(-1)
results = process_yaml(args[0], location, connection=connection,
min_zoom=options.min_zoom, max_zoom=options.max_zoom)
for layer in results:
if options.all_stats:
print "\n\nlayer: " + layer
for zoom, q in results[layer].iteritems():
if options.all_stats:
execution_string = "%.3f" % q['execution_time']
if q['execution_time'] > options.slow:
execution_string = '\x1b[%sm%s\x1b[0m' % ("31", execution_string)
print "zoom: %i, planning time:%.3f ms, execution time:%s ms" % (zoom, q['planning_time'], execution_string)
if q['execution_time'] > options.slow:
if not options.all_stats:
execution_string = "%.3f" % q['execution_time']
print "layer: %s, zoom: %i, planning time:%.3f ms, execution time:%s ms" % \
(layer, zoom, q['planning_time'], execution_string)
print "slow query:\n" + q['query']
print "plan:\n" + "\n".join(q['query_plan'])
print "\n"
elif options.verbose:
print "query:\n" + q['query']
print "plan:\n" + "\n".join(q['query_plan'])
print "\n"
if __name__ == "__main__":
_main()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment