CODEWITHSUNDEEP
postgresqlindexinggispostgisspatial
mareks.poland
mareks.poland

Reputation: 233

Spatial gist index in postgis - performance

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:

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)

Upvotes: 2

Views: 2334

Answers (1)

Mike T
Mike T

Reputation: 43642

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.

Upvotes: 2

Related Questions