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/postgis-users@postgis.refractions.net/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, May 21 '12 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, May 21 '12 at 21:07

score 2 · Accepted Answer · answered May 21 '12 at 23:11

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 '12 at 23:11

Mike T

41,085
18
152
203

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. – Twelfth May 22 '12 at 18:28
I've splitted my country polygon and it works perfect now. Thanks for help. – mareks.poland May 22 '12 at 22:13

Spatial gist index in postgis - performance

1 Answers1