12

I imported the data from openstreetmap using osm2pgsql and copied it another table structure. If I made a query for containing rows using st_intersects, st_contain or someone else, postgis don't use the gist-INDEX. Always a seq-scan will be made. If I filter the rows first using the &&-operator (bbox of geometries), postgis uses the gist-index for the postgis functions. I don't understand why, where is the problem and how can I change this.

Here I've created a simplified table of the original (you can also use a planet_osm_polygon for queries):

CREATE TABLE osm_addr2 (osm_id bigint, geometry geometry);
CREATE INDEX osm_addr2_geom ON osm_addr2 USING gist (geometry);

Here I created a very simple example query:

EXPLAIN ANALYSE SELECT * FROM osm_addr2 AS addr 
WHERE -- addr.geometry && (SELECT geometry FROM osm_addr2 WHERE osm_id=-332537) AND 
  st_intersects(addr.geometry, (SELECT geometry FROM osm_addr2 WHERE osm_id=-332537));

Using st_intersects only, I have the following result (first line only):

Seq Scan on osm_addr2 addr (cost=24.50..336.10 rows=387 width=40) (actual time=0.001..0.001 rows=0 loops=1)

If I uncomment the &&-operator the index will be used (first line only):

Index Scan using osm_addr2_geom on osm_addr2 addr (cost=49.15..57.41 rows=1 width=40) (actual time=0.031..0.031 rows=0 loops=1)

I'm using postgresql 9.3 with postgis 2.1, see postgis_full_version():

POSTGIS="2.1.4 r12966" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23 September 2009" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.8.0" LIBJSON="UNKNOWN" RASTER

I can reproduce this on Ubuntu 14.04 and Debian 7. I hope anyone can help because using the intersection operator will inflate my queries.

1

5 Answers 5

11

I have found that rearranging the query so that the sub-query is at the same level as the initial select, essentially a Cartesian product, but then using the where clause to restrict the records read, will cause the indexes to be used and avoid a full table scan.

SELECT * 
FROM 
   osm_addr2 AS addr, 
   (SELECT geometry FROM osm_addr2 WHERE osm_id=-332537) as addr2
WHERE st_intersects(addr.geometry, addr2.geometry);

EDIT: thanks to MikeT for the link to the relevant docs and to Jakub for the term function inlining.

EDIT 2: I now find it more elegant to use CTE queries for this kind of problem, as they are easier to read than subqueries, and have the same effect as far as making the spatial index get utilized for the spatial intersection.

WITH addr2(geometry) AS 
     (SELECT geometry FROM osm_addr2 WHERE osm_id=-332537) 
   SELECT addr.* 
     FROM 
        osm_addr2 addr, addr2
    WHERE ST_Intersects(addr.geometry, addr2.geometry);
8
  • 1
    Thanks a lot. This is the fasted query in compare to the orther. Here are my query times on real data: my query using seq.: 7 seconds my query using index and &&-op: 24 ms your query: 15 ms Jan 20, 2015 at 18:47
  • Does it fix the explain too? Jan 20, 2015 at 18:48
  • The query now uses all indexes: pastebin.com/HrrMudEA Jan 20, 2015 at 19:25
  • The way this statement is structured, the planner rewrites the subquery into the main query. Compare the last two SQL queries in the manual, which is the same as your answer to mine. They are the same to the planner.
    – Mike T
    Jan 21, 2015 at 3:52
  • It's function inlining, I posted my view on the cause of it below. Jan 21, 2015 at 18:37
9

PostgreSQL doesn't use indexes for functions, it uses indexes for operators only. What happens is function inlining. ST_INTERSECTS is defined as:

CREATE OR REPLACE FUNCTION ST_Intersects(geom1 geometry, geom2 geometry)
RETURNS boolean
AS 'SELECT $1 && $2 AND _ST_Intersects($1,$2)'
LANGUAGE 'sql' IMMUTABLE;

And so the query gets rewritten to use the index to check the bounding boxes and narrow down the result and then check again. My best guess is that with a subquery it doesn't happen (probably because at that point the result of subquery is not known).

1
  • 1
    Good explanation Jan 22, 2015 at 6:19
4

Yes, ST_Intersects uses a spatial index

See this answer for a good explanation.

For your query

The right way, is to rewrite your query like this

SELECT *
FROM osm_addr2 AS a1
WHERE EXISTS (
  SELECT 1
  FROM osm_addr2 AS a2
  WHERE ST_Intersects(a1.geometry, a2.geometry)
  AND a2.osm_id = -332537
);

That's using the SQL-92 syntax. If you want a super awesome index on it. Use a compound index on osm_id and geometry.

CREATE INDEX osm_addr2_geom ON osm_addr2
  USING gist (osm_id, geometry);
3

Note that planners have difficulty with subqueries, and your example can be rewritten without subqueries. A flattened query should look like this:

SELECT A.*
FROM osm_addr2 AS addr, osm_addr2 AS POI
WHERE POI.osm_id=-332537 AND ST_Intersects(addr.geometry, POI.geometry);

There's a relevant example in the manual (last two SQL examples), where a subquery is collapsed into a main query by the planner, which is the same example if you look at @John's answer.

2
  • 1
    I've tried the query, and returns the same query plan like johns, see: pastebin.com/HrrMudEA Jan 21, 2015 at 17:51
  • Cool, looks like the query planner did indeed rewrite the query, similar to the example in the manual.
    – Mike T
    Jan 21, 2015 at 20:02
-1

Judging by the cost and quantity of rows estimated to be returned, the spatial index wasn't necessary. At least the optimizer didn't think it was and chose a seq scan instead. Is it running slow? How may rows do you expect back? How many rows are in the table altogether? Sorry if I overlooked that in your question but sometimes a full scan is faster.

Check this out. It might help explain what is going on. https://sdeintercept.wordpress.com/2014/12/18/postgresql-geodatabase-query-performance/

1
  • No, this behavior holds for millions of rows in my experience, if the query is written as in the OP's question, with a select inside the ST_Intersects, even if in this instance, a full table scan wouldn't be too problematic performance wise. Jan 21, 2015 at 9:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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