Slow PostGIS line intersection even with indexes

Question

I have line data contained in different graphs. Querying data over multiple graphs and using st_intersects results in slow queries. All lines are represented as geometry(LineString).

I am querying multiple graphs together containing over 3 million lines, the table contains over 5 million lines.

Here is a sample query counting the data. This part of the query is used in more complex queries and is the biggest bottleneck. Following query filters around 63.000 lines from 3 mil.

select count(*) from graph_schema.graph_line as gline 
where gline.graph_id in (26, 27) 
and st_intersects(gline.line, st_geomfromtext('LINESTRING(1280 0, 0 720)'));

I have partial spatial indexes for each graph:

CREATE INDEX graph_line_line_idx<GRAPH_ID> ON graph_schema.graph_line USING GIST (line) WHERE graph_schema.graph_line.graph_id = <GRAPH_ID>;

And index on the foreign key of the graph_id

With this setup, I cannot make PostgreSQL use the spacial indexes when querying multiple graphs. The query takes 18 seconds.

Aggregate  (cost=1382969.26..1382969.27 rows=1 width=8) (actual time=18161.980..18161.980 rows=1 loops=1)
  ->  Seq Scan on graph_line gline  (cost=0.00..1381079.93 rows=755732 width=0) (actual time=35.923..18156.004 rows=63306 loops=1)
"        Filter: ((graph_id = ANY ('{26,27}'::integer[])) AND (line && '0102000000020000000000000000009440000000000000000000000000000000000000000000808640'::geometry) AND _st_intersects(line,     '0102000000020000000000000000009440000000000000000000000000000000000000000000808640'::geometry))"
        Rows Removed by Filter: 4898682
Planning time: 0.686 ms
Execution time: 18162.012 ms

So I tried to create an index for this specific query:

CREATE INDEX graph_line_line_geom_advanced ON graph_schema.graph_line USING GIST (line) WHERE graph_schema.graph_line.graph_id in (26, 27);

This makes PostgreSQL use the index, but the query is still slow - no real speedup.

Aggregate  (cost=782285.83..782285.84 rows=1 width=8) (actual time=17892.661..17892.661 rows=1 loops=1)
  ->  Bitmap Heap Scan on graph_line gline  (cost=106709.03..780375.43 rows=764159 width=0) (actual time=1109.125..17886.986 rows=63306 loops=1)
"        Recheck Cond: ((line && '0102000000020000000000000000009440000000000000000000000000000000000000000000808640'::geometry) AND (graph_id = ANY ('{26,27}'::integer[])))"
"        Filter: _st_intersects(line, '0102000000020000000000000000009440000000000000000000000000000000000000000000808640'::geometry)"
        Rows Removed by Filter: 3077146
        Heap Blocks: exact=44560
        ->  Bitmap Index Scan on graph_line_line_geom_advanced  (cost=0.00..106517.99 rows=2292477 width=0) (actual time=1098.754..1098.754 rows=3140452 loops=1)
              Index Cond: (line && '0102000000020000000000000000009440000000000000000000000000000000000000000000808640'::geometry)
Planning time: 0.693 ms
Execution time: 17893.234 ms

I also tried a different approach to query the data, but no difference.

select count(*) from graph_schema.graph_line as gline
join (SELECT st_geomfromtext('LINESTRING(1280 0, 0 720)') :: geometry AS line2) line2 on st_intersects(gline.line, line2)
where gline.graph_id in (26, 27);

Is there any way to greatly improve the query performance? Either by better usage of index, data representation or by preparing the data? Is the slow performance caused by st_geomfromtext? I should also mention that the intersecting line can be changed by the user - so I cannot effectively prepare the data in advance.

Answer 1

When dealing with a large amount of spatial data, it is advisable to tunr Postgresql for PostGIS. This can make a dramatic difference to performance and may be a contributing factor in your case.

Another performance enhancer that works alonside indexes is clustering. Eseentially, clustering moves features that are spatially near each other to be physically near each other on the disk (reducing I/O read/write time and disk thrashing).

Tuning Postgresql for spatial is more of an art than a science and depends a lot on your machine's CPU and RAM but I can give you some pointers:

• Boundless uses Postgis and here are their settings [a very good place to start!]
• See here for hints and tips on tuning Postgres and clustering.
• Here is OSM's take on tuning the performance (aimed at imporving data access for rendering web maps but the principles are good).