2

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.

  • Have you tuned Postgresql for Postgis or are you using it 'out-of-the-box'? Also have tyou clustered your data? – MappaGnosis Nov 16 '18 at 11:35
  • I did not tune my postgres in any way - at least I do not think so. Can you please provide some links or docs on how to do that? I do not know how to cluster using partial indexes - is there a way? – Novotmike Nov 16 '18 at 11:43
  • 2
    This comes up all the time. It is because ST_GeomFromText appears inside ST_Intersects(a.geom, b.geom) so the index is not used properly. You do not want a bitmap index scan in this context. Put the st_geomfromtext in a subquery or a cte. Something like WITH line(geom) AS (SELECT st_geomfromtext('LINESTRING(1280 0, 0 720)'))) select count(*) from graph_schema.graph_line gline, line l where gline.graph_id in (26, 27) and st_intersects(gline.line, l.geom) – John Powell Nov 16 '18 at 11:59
  • 1
    Also, do you have an index on graph_id? Any in not always the most performant way to do array search. You might be better off with one of the gin index op classes, see, postgresql.org/docs/9.4/… – John Powell Nov 16 '18 at 12:04
  • @JohnPowell Thanks four your advice. I have already tried the subquery and CTE approach. It does not help very much. At this point, it seems it does not even matter if I have an index on graph_id or not. Both result in same query plan - for the graph_id condition. – Novotmike Nov 16 '18 at 12:32
3

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).
  • I will look into it. I have played around with my queries and am at the point where the actual aggregation (count or group by) takes longest. Do you have any suggestions on how to improve the performance of those operations? – Novotmike Nov 19 '18 at 16:21

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.