Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Slow queries on US test server #16

Closed
lxbarth opened this issue Dec 15, 2012 · 10 comments
Closed

Slow queries on US test server #16

lxbarth opened this issue Dec 15, 2012 · 10 comments

Comments

@lxbarth
Copy link

lxbarth commented Dec 15, 2012

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
Copy link
Author

lxbarth commented Dec 15, 2012

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
Copy link

iandees commented Dec 15, 2012

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
Copy link
Owner

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
Copy link
Contributor

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
Copy link

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
Copy link
Contributor

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

@iandees
Copy link

iandees commented Dec 30, 2012

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
Copy link
Collaborator

pnorman commented May 24, 2013

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
Copy link

iandees commented May 24, 2013

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

@gravitystorm
Copy link
Owner

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

6 participants