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.