Optimize a SQL

[dadiorchen]

Now the freetown case in greeestand is getting slow because of data growing, the main bottleneck is this SQL:

SELECT ST_AsBinary("estimated_geometric_location") AS geom,"count","id","latlon","type" FROM (
      /* sql case2 tile */
      WITH placeholder AS (SELECT 1)
      SELECT /* DISTINCT ON(trees.id) */
      'case2 tile' AS log,
      estimated_geometric_location,
      St_asgeojson(estimated_geometric_location) latlon,
      'point' AS type,
       trees.id, 
       trees.lat, 
       trees.lon,
      NULL AS zoom_to,
      1 as count
      FROM trees 
      
      WHERE active = true 
      
      
      
      ORDER BY ID DESC
    ) as cdbq WHERE "estimated_geometric_location" && ST_MakeEnvelope(-13.26813697814941,8.405045448875502,-13.25835227966309,8.41472493360892,4326)

The report from PostgreSQL:

1200114	treetracker	mapnik	159.223.114.136	
SELECT ST_AsBinary("estimated_geometric_location") AS geom FROM ( /* sql case2 tile */ WITH placeholder AS (SELECT 1) SELECT /* DISTINCT ON(trees.id) */ 'case2 tile' AS log, estimated_geometric_location, St_asgeojson(estimated_geometric_location) latlon, 'point' AS type, trees.id, trees.lat, trees.lon, NULL AS zoom_to, 1 as count FROM trees WHERE active = true ORDER BY ID DESC ) as cdbq WHERE "estimated_geometric_location" && ST_MakeEnvelope(-13.26813697814942,8.421347599601582,-13.25835227966308,8.431026676777542,4326)
2546.4846ms	 Terminate
1195828	treetracker	mapnik	159.223.114.136	
SELECT ST_AsBinary("estimated_geometric_location") AS geom,"count","id","latlon","type" FROM ( /* sql case2 tile */ WITH placeholder AS (SELECT 1) SELECT /* DISTINCT ON(trees.id) */ 'case2 tile' AS log, estimated_geometric_location, St_asgeojson(estimated_geometric_location) latlon, 'point' AS type, trees.id, trees.lat, trees.lon, NULL AS zoom_to, 1 as count FROM trees WHERE active = true ORDER BY ID DESC ) as cdbq WHERE "estimated_geometric_location" && ST_MakeEnvelope(-13.26813697814941,8.410479575365791,-13.25835227966309,8.420158924331879,4326)
2538.0881ms	 Terminate
1195609	treetracker	mapnik	159.223.114.136	
SELECT ST_AsBinary("estimated_geometric_location") AS geom FROM ( /* sql case2 tile */ WITH placeholder AS (SELECT 1) SELECT /* DISTINCT ON(trees.id) */ 'case2 tile' AS log, estimated_geometric_location, St_asgeojson(estimated_geometric_location) latlon, 'point' AS type, trees.id, trees.lat, trees.lon, NULL AS zoom_to, 1 as count FROM trees WHERE active = true ORDER BY ID DESC ) as cdbq WHERE "estimated_geometric_location" && ST_MakeEnvelope(-13.27363014221192,8.421347599601582,-13.26384544372558,8.431026676777542,4326)

Copy	2537.49ms	 Terminate

If we can speed up this SQL to some extent, then it would be the best win for the current stage.