1

I just updated this question as I was able to fix the problem by running a vacuum full analyse on the tables. I tested vacuum alone and vacuum analyse before which did not solve the problem. Not sure what vacuum full analyse does but it fixed this kind of problem.

OP>>>

I have several large PostGIS tables with millions of records. Each record has a two geometry fields. A polygon geometry (Polygon, 3857) and a centroid of the polygon (Point, 4326). I also created a (Point, 3857) geometry for testing.

All geometry fields have a spatial index (GIST) and all tables are vacuumed.

I am trying to select records that are within a lat, lon bounding box which I create with ST_MakeEnvelop(). I test ST_Intersect as well as ST_DWithin with a buffer radius of 0. Testing a bounding box against point geometries does never use the spatial index. While testing bounding box against the polygon geometry does use a spatial index. This seems counter intuitive. As I am not even interested in displaying the geometries but only their centroids.

Is there any way to tweak my index or query to get points that are within / intersect a bounding box?

This query uses a spatial index:

SELECT lon, lat, pop
FROM cj_hx_16k
WHERE
  ST_DWithin(
    ST_MakeEnvelope(106.3, 26.4, 130.8, 35.7, 4326),
    geomcntr, 0);

This is the corresponding description for the explain plan.

Node Type = Seq Scan; Relation Name = cj_hx_16k; Alias = cj_hx_16k; Startup Cost = 0.0; Total Cost = 28343.77; Plan Rows = 1127; Plan Width = 23; Filter = ((geomcntr && '0103000020E610000001000000050000003333333333935A406666666666663A403333333333935A409A99999999D941409A999999995960409A99999999D941409A999999995960406666666666663A403333333333935A406666666666663A40'::geometry) AND ('0103000020E610000001000000050000003333333333935A406666666666663A403333333333935A409A99999999D941409A999999995960409A99999999D941409A999999995960406666666666663A403333333333935A406666666666663A40'::geometry && st_expand(geomcntr, '0'::double precision)) AND _st_dwithin('0103000020E610000001000000050000003333333333935A406666666666663A403333333333935A409A99999999D941409A999999995960409A99999999D941409A999999995960406666666666663A403333333333935A406666666666663A40'::geometry, geomcntr, '0'::double precision));

This query does use the spatial index on the geom field.

SELECT lon, lat, pop
FROM cj_hx_32k
WHERE
  ST_DWithin(
    ST_Transform(
        ST_MakeEnvelope(106.3, 26.4, 130.8, 35.7, 4326),
                 3857), geom, 0);

This is the corresponding explain plan:

Node Type = Bitmap Heap Scan; Relation Name = cj_hx_32k; Alias = cj_hx_32k; Startup Cost = 147.26; Total Cost = 2861.79; Plan Rows = 204; Plan Width = 24; Recheck Cond = (geom && '0103000020110F00000100000005000000EDE4E1BBF5916641444475CC81424741EDE4E1BBF59166418189CBFD963F50414311AAACA9C56B418189CBFD963F50414311AAACA9C56B41444475CC81424741EDE4E1BBF5916641444475CC81424741'::geometry); Filter = (('0103000020110F00000100000005000000EDE4E1BBF5916641444475CC81424741EDE4E1BBF59166418189CBFD963F50414311AAACA9C56B418189CBFD963F50414311AAACA9C56B41444475CC81424741EDE4E1BBF5916641444475CC81424741'::geometry && st_expand(geom, '0'::double precision)) AND _st_dwithin('0103000020110F00000100000005000000EDE4E1BBF5916641444475CC81424741EDE4E1BBF59166418189CBFD963F50414311AAACA9C56B418189CBFD963F50414311AAACA9C56B41444475CC81424741EDE4E1BBF5916641444475CC81424741'::geometry, geom, '0'::double precision));
Node Type = Bitmap Index Scan; Parent Relationship = Outer; Index Name = sidx_cj_hx_32k_geom; Startup Cost = 0.0; Total Cost = 147.21; Plan Rows = 3058; Plan Width = 0; Index Cond = (geom && '0103000020110F00000100000005000000EDE4E1BBF5916641444475CC81424741EDE4E1BBF59166418189CBFD963F50414311AAACA9C56B418189CBFD963F50414311AAACA9C56B41444475CC81424741EDE4E1BBF5916641444475CC81424741'::geometry);
Improve this question
5
  • 1
    this might be a weak hack, maybe buffer the point like .001 feet or something so small that its polygon but wont throw off the results because of how small of a buffer you applied
    – ziggy
    Oct 31, 2017 at 14:57
  • Do you mean the buffer distance in ST_DWithin()? I have tried that as well. In fact I always use a small buffer but just reduced the buffer as well as testing ST_Intersect in order to limit the unknowns. The only consistent fail seems to be the ST_MakeEnvelope() against Point geometries.
    – Dennis Bauszus
    Oct 31, 2017 at 14:59
  • ST_MakePolygon() fails as well. There is something about generated geometries which differs from existing geometries.
    – Dennis Bauszus
    Oct 31, 2017 at 15:30
  • 1
    Spatial index is storing the envelopes of the geometries and the envelope of a point is a point. Are you sure that making a query that is utilizing a spatial index would be any faster in your case? Do you experience that the query as it is now is slow? Is there a difference in the explain plan if you create a new table with just one geometry field for the centroids?
    – user30184
    Nov 1, 2017 at 7:19
  • The difference is vast. Query on selecting <10000 points out of 100,000,000 took about 10seconds without the spatial index in the explain plan and less than a second with the spatial index. I need polygons and points both with indices. The polygons are required for analytics and the points for presenting the data.
    – Dennis Bauszus
    Nov 1, 2017 at 13:11

1 Answer 1

Reset to default
1

what I meant in my comment above is apply a small buffer to the points, so when you go to intersect the points and the bounding box an index is used.

something like this:

with point_buff as(select st_buffer(geom,.001) geom,lon,lat,pop from cj_hx_32k)

SELECT lon, lat, pop
    FROM point_buff
    WHERE
      ST_intersects(
        ST_Transform(
            ST_MakeEnvelope(106.3, 26.4, 130.8, 35.7, 4326),
                     3857), geom);

ps* I dont know which SRID you are using for the points layer, but if its in feet then use a small measure like I did in my example. If you are using 3857 or 4326 maybe cast to geometry first, then buffer, then case back to whatever main SRID you are using

Improve this answer
3
  • 1
    That works. However adding a buffer to millions and millions of records will not be feasible. The performance is the same as checking against the existing polygon geometry which is simple. Does this mean that PostGIS cannot make use of spatial index on polygon <> point checks?
    – Dennis Bauszus
    Oct 31, 2017 at 16:00
  • hmm good point, what about if you force an index by using the bounding box intersect && operator before the st_intersects?
    – ziggy
    Oct 31, 2017 at 16:04
  • The problem got fixed by running vacuum full analyse on all tables. I tested vacuum analyse before which didn't do it. A colleague recommended vacuum full analyse and that did it.
    – Dennis Bauszus
    Oct 31, 2017 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.