0

I'm a programmer getting into the wonderful world of GIS. I just had a fun day optimizing queries (took one from 60s to 3.4s, and another from 38s to 1.2s). Below is the last query that I can't seem to optimize! (My white whale!)

my query is basically the following

SELECT child,st_asgeojson("child".wkb_geometry)
FROM child
WHERE ST_Contains(
(SELECT wkb_geometry FROM parent WHERE ogc_fid=xxx),
"child".wkb_geometry
) OR ST_Overlaps(
(SELECT wkb_geometry FROM parent WHERE ogc_fid=xxx),
"child".wkb_geometry
);

I understand that this OR is probably terrible, but truth is the query is terrible with and without it... (both pieces are individually terrible). It's currently at ~40s average. I'm fine with optimizations that work for one or the other piece of the query...(or both separately, or both together).

I just learned about indexes and the last 2 queries I optimized got better with basic btree indexes (and one multi-column index)...I assume I need a combination of an expression index and something else, but I don't know where to start thinking about the solution. I also don't know how to have a cross-table index or if that is necessary...for this query.

  • 1
    did you add a spatial (GIST) index on wkb_geometry for parent? – Ian Turton Apr 15 '18 at 12:34
1

The issue was that a SEQuential search was being done through child, I added a subquery to trigger an index search instead of sequential search and now the query is 0.6s on average. Wow!

My advice to anyone with this kind of problem would be to do an EXPLAIN ANALYZE on the query, and if either child or parent is a table with millions of rows and is doing a sequential search, try to modify the query and/or your indexes to have it do an index based search.

  • Yes, EXPLAIN is always your friend. You might want to try ST_Relate as a more efficient way of dealing with contains or overlaps. Even if it doesn't run faster, the SQL will be cleaner. – John Powell Apr 16 '18 at 12:12

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.