Spatial gist index in postgis - performance

Question

I have problem with gist index. I have table 'country' with 'geog' (geography,multipolygon) columny. I have also gist index on this column. This simple query with ST_CoveredBy() against table with 2 rows ( each 'geog' about 5MB) takes 13 s (the query result is correct):

select c."ID" from gis.country c where ST_CoveredBy(ST_GeogFromText('SRID=4326;POINT(8.4375 58.5791015625)'), c."geog") =true

When I droped the index, the query also took 13s.

What I've already did:

VACUUM ANALYZE gis.country ("geog")
maybe this is the problem: "Do not call with a GEOMETRYCOLLECTION as an argument" I have read (http://www.mail-archive.com/[email protected]/msg17780.html), that it's because overlapping polygons, but in country map doesn't exist overlapping polygons

EDIT

Query plan -

Index Scan using sindx_country_geography_col1 on country c  (cost=0.00..8.52 rows=1 width=4)
  Index Cond: ('0101000020E61000000000000000E0204000000000204A4D40'::geography && "geog")
  Filter: _st_covers("geog", '0101000020E61000000000000000E0204000000000204A4D40'::geography)

Please post the query plan. The index will hardly be used for a table with 2 rows. — Quassnoi
– Quassnoi, Commented May 21, 2012 at 20:57
Index Scan using sindx_country_geography_col1 on country c (cost=0.00..8.52 rows=1 width=4) Index Cond: ('0101000020E61000000000000000E0204000000000204A4D40'::geography && "geog") Filter: _st_covers("geog", '0101000020E61000000000000000E0204000000000204A4D40'::geography) — mareks.poland
– mareks.poland, Commented May 21, 2012 at 21:07

Mike T · Accepted Answer · 2012-05-21 23:11:49Z

2

You won't see any benefit of an index querying against a table with only two rows. The benefit of an index only shines if you have hundreds or more rows to query.

I'm going to guess that you have two very detailed country multipolygons. There are strategies to divide these into grids to improve performance. How you break up your countries into grids should be based on (1) the density of your areas of interest (where you are most likely to query), and (2) multipolygon complexity or density of vertices.

answered May 21, 2012 at 23:11

Mike T

44.4k18 gold badges166 silver badges213 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Twelfth Over a year ago

Mike has it right, index won't make a difference here...st_coveredby refers to another function which refers to postGIS contrib file written in C. It uses an iterative process to figure out what point is contained (raycrossing logic I believe). It makes the query sensitive to the number of points contained in the polygon for performance...large polygons perform significantly slower, and your 5mb polygons are pretty massive. Agree with Mike's solutions, but another possibility is to look into optimizing your postgres server or potentially giving it more hardware.

mareks.poland Over a year ago

I've splitted my country polygon and it works perfect now. Thanks for help.

Collectives™ on Stack Overflow

Spatial gist index in postgis - performance

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related