Slow queries on US test server

[lxbarth]

Ian Dees reports these slow queries from running the OSM-carto style on tile.osm.osuosl.org

I've been trying to figure out why the carto style rendering is taking so damn long on tile.osm.osuosl.org. I think it's mostly because it was catching up on osm2pgsql until very recently, but I did notice some particular slow queries that I thought I should send you to think about:

https://gist.github.com/4558b2dab2ecfb35a389#file-slow_query_a-sql

This guy tends to take a very long time and the query planner doesn't seem to be using any indexes:

https://gist.github.com/4558b2dab2ecfb35a389#file-query_plan_a-sql

Again, this could simply be osm2pgsql keeping the disks too busy to do anything else, but I thought I'd pass along more data.

[lxbarth]

I fear I just brought the server down again by using the comparison interface we set up last week. @iandees: were those the same queries that brought down the server now?

[iandees]

You don't really bring it down, it just gets too bogged down rendering tiles so aren't any app servers left to serve any more requests and appears down. (So I guess yea, you brought it down! 😃)

I'm trying to sift through the slow query log to see if they're always the same queries. It appears to me that the query planner is not taking advantage of the spatial index and is doing a table scan in most cases. It probably would help to add indexes on some of the columns that are used frequently (natural, highway, etc.).

[gravitystorm]

I suspect this query can be changed - I doubt that the ordering by either z-order or way_area are necessary if they are all just being used for flat-colour blue fills. But I'll need to check the rules carefully to see if anything has an order dependency.

Also from a brief look at the plans, it doesn't appear to be using the spatial index. Perhaps the building=null is making the planner think that a sequence scan to find all water polygons, then filtering by area, is best.

[springmeyer]

Right, looks like the spatial index is not working. Running the query locally the index does work, so perhaps the db needs vacuumed or something:

 Subquery Scan on water_areas  (cost=6779.12..6779.27 rows=10 width=681) (actual time=7.012..7.026 rows=15 loops=1)
   ->  Sort  (cost=6779.12..6779.15 rows=10 width=708) (actual time=6.960..6.962 rows=15 loops=1)
         Sort Key: planet_osm_polygon.z_order, planet_osm_polygon.way_area
         Sort Method: quicksort  Memory: 33kB
         ->  Bitmap Heap Scan on planet_osm_polygon  (cost=144.18..6778.96 rows=10 width=708) (actual time=1.749..6.893 rows=15 loops=1)
               Recheck Cond: (way && '010300002031BF0D00010000000500000000978A5F75460AC1C6377961AE415A4100978A5F75460AC1CA968A5F75465A4180B65E9D95AD09C1CA968A5F75465A4180B65E9D95AD09C1C6377961AE415A4100978A5F75460AC1C6377961AE415A41'::geometry)
               Filter: ((building IS NULL) AND ((waterway = ANY ('{dock,mill_pond,riverbank,canal}'::text[])) OR (landuse = ANY ('{reservoir,water,basin}'::text[])) OR ("natural" = ANY ('{lake,water,land,glacier,mud,bay}'::text[]))))
               ->  Bitmap Index Scan on planet_osm_polygon_index  (cost=0.00..144.18 rows=3712 width=0) (actual time=1.531..1.531 rows=4209 loops=1)
                     Index Cond: (way && '010300002031BF0D00010000000500000000978A5F75460AC1C6377961AE415A4100978A5F75460AC1CA968A5F75465A4180B65E9D95AD09C1CA968A5F75465A4180B65E9D95AD09C1C6377961AE415A4100978A5F75460AC1C6377961AE415A41'::geometry)
 Total runtime: 7.163 ms

[jburgess777]

I'm seeing something similar locally with sequential scans on the polygon table and lowish zoom levels. I don't think it is specific to the carto style. Unfortunately the polygon table is 20GB+ so the scans are being read from disk every time.

I tried to run the explain on Yevaud and discovered that it is using an extra index. I must have added this a long time ago, probably to help this exact issue. You might want to try creating this index:

CREATE INDEX water_areas_idx ON planet_osm_polygon USING gist (way) WHERE (((waterway IS NOT NULL) OR (landuse = ANY (ARRAY['reservoir'::text, 'water'::text, 'basin'::text]))) OR ("natural" IS NOT NULL));

While you are at it, this is another which helps the ferry line queries which are also problematic:

CREATE INDEX ferry_idx ON planet_osm_line USING gist (way) WHERE (route = 'ferry'::text);

These are the only additional indexes I currently have on Yevaud.

[springmeyer]

@iandees - would it be possible to try adding extra indexes above and see if that helps your server? Thanks @jburgess777 for the details!

[iandees]

I added the indexes that @jburgess777 mentioned and it seems to have helped a bit. That layer hasn't gotten any traffic in several days so I don't have a lot to compare it to, but I haven't seen it hit the slow query log yet.

[pnorman]

There is a known issue with the query planner that leads to postgres selecting sequential scans over index scans which has been fixed in 9.3. The standard workaround in earlier versions is to increase cpu_tuple_cost to 0.1.

@iandees I'm not saying it'll help here because I've only come up against it in non-GiST indexes, but has cpu_tuple_cost been adjusted on benzene?

[iandees]

@pnorman Nope, haven't adjusted cpu_tuple_cost on benzene.

[gravitystorm]

Closing this specific issue in favour of the follow-on at #207