6

I'm querying database entries using a bounding box with ST_MakeEnvelope and intersecting that with a coordinates column (type GEOMETRY), consisting of simple POINT(lng, lat) entries. I've created a GiST index on the coordinates column, and clustered the table on the index - which sped up the queries significantly. The table has about 20M+ rows, and will grow to hundreds of millions of rows in the future.

The current query looks something like this - i changed some naming to be more generic. The lngMin/latMin/lngMax/latMax/offset/limit values are filled in with JS on the Node.js server:

SELECT id, ST_X(coordinates) as longitude, ST_Y(coordinates) as latitude
FROM table
WHERE (table.type = 'something'
  AND ST_MakeEnvelope(lngMin, latMin, lngMax, latMax, 4326) ~ geometry(coordinates))
OFFSET offset
LIMIT limit;

Query performance is somewhat acceptable (limit=10000, offset=0):

Limit  (cost=302.64..21222.41 rows=10000 width=44) (actual time=731.236..750.139 rows=10000 loops=1)
   ->  Bitmap Heap Scan on table  (cost=302.64..44236.25 rows=21001 width=44) (actual time=731.236..749.501 rows=10000 loops=1)
         Recheck Cond: ('0103000020E6100000010000000500000098DD938785DA1040C7293A92CB0F494098DD938785DA1040C7293A92CB6F49407D3F355EBA891240C7293A92CB6F49407D3F355EBA891240C7293A92CB0F494098DD938785DA1040C7293A92CB0F4940'::geometry ~ coordinates)
         Filter: (type = 'something'::text)
         Heap Blocks: exact=198
         ->  Bitmap Index Scan on table_gix  (cost=0.00..301.59 rows=21001 width=0) (actual time=699.359..699.359 rows=6634519 loops=1)
               Index Cond: ('0103000020E6100000010000000500000098DD938785DA1040C7293A92CB0F494098DD938785DA1040C7293A92CB6F49407D3F355EBA891240C7293A92CB6F49407D3F355EBA891240C7293A92CB0F494098DD938785DA1040C7293A92CB0F4940'::geometry ~ coordinates)
 Planning time: 0.107 ms
 Execution time: 750.604 ms

I want to order the results based on created_at (desc), so that i can batch request all coordinates, returning newest entries first. (first request: offset=0, second request: offset=limit+offset, etc..)
But by simply adding ORDER BY created_at DESC, the query becomes really slow (with e.g. limit 1000).

Limit  (cost=36591.17..36969.67 rows=1000 width=44) (actual time=3662.597..3664.364 rows=1000 loops=1)
   ->  Result  (cost=36591.17..44540.05 rows=21001 width=44) (actual time=3662.595..3664.291 rows=1000 loops=1)
         ->  Sort  (cost=36591.17..36601.67 rows=21001 width=44) (actual time=3662.562..3662.642 rows=1000 loops=1)
               Sort Key: created_at DESC
               Sort Method: top-N heapsort  Memory: 127kB
               ->  Bitmap Heap Scan on table (cost=302.64..36360.88 rows=21001 width=44) (actual time=924.050..2823.598 rows=6634433 loops=1)
                     Recheck Cond: ('0103000020E6100000010000000500000098DD938785DA1040C7293A92CB0F494098DD938785DA1040C7293A92CB6F49407D3F355EBA891240C7293A92CB6F49407D3F355EBA891240C7293A92CB0F494098DD938785DA1040C7293A92CB0F4940'::geometry ~ coordinates)
                     Filter: (type = 'something'::text)
                     Heap Blocks: exact=122911
                     ->  Bitmap Index Scan on table_gix  (cost=0.00..301.59 rows=21001 width=0) (actual time=890.864..890.864 rows=6634433 loops=1)
                           Index Cond: ('0103000020E6100000010000000500000098DD938785DA1040C7293A92CB0F494098DD938785DA1040C7293A92CB6F49407D3F355EBA891240C7293A92CB6F49407D3F355EBA891240C7293A92CB0F494098DD938785DA1040C7293A92CB0F4940'::geometry ~ coordinates)
 Planning time: 0.196 ms
 Execution time: 3664.934 ms

I've tried creating a multicolumn GiST index using:

CREATE INDEX table_coordinates_and_created_at_gix 
ON table 
USING GIST (coordinates, created_at);  

I've VACUUM and ANALYZE'd the table after creating the indexes, but it didn't help.

How can I speed up the queries?

I am new to Postgres/PostGIS. I'm also not sure if i should VACUUM the DB once in a while.
I have not had the chance to dive deeply into the documentation.


Using ST_Intersects query performance improves quite a bit (advice by John Barça!). This is great for batch requesting all results in the bounding box. It doesn't use the coordinates index though.
No update on improving querying with ORDER BY yet.

Query:

SELECT id, ST_X(coordinates) as longitude, ST_Y(coordinates) as latitude
FROM table 
WHERE (table.type = 'something' 
  AND ST_Intersects(ST_MakeEnvelope(4.2134, 50.1234, 4.6345, 50.8734, 4326), table.coordinates::geography)) 
LIMIT 10000 
OFFSET 10000;

Plan:

Limit  (cost=13233.27..25466.54 rows=10000 width=44) (actual time=61.063..118.786 rows=10000 loops=1)
   ->  Gather  (cost=1000.00..1713707.82 rows=1400041 width=44) (actual time=0.386..117.567 rows=20000 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Parallel Seq Scan on table  (cost=0.00..1356521.10 rows=823554 width=44) (actual time=0.577..110.212 rows=10000 loops=2)
               Filter: ((type = 'something'::text) AND ('0103000020E6100000010000000500000098DD938785DA1040C7293A92CB0F494098DD938785DA1040C7293A92CB6F49407D3F355EBA891240C7293A92CB6F49407D3F355EBA891240C7293A92CB0F494098DD938785DA1040C7293A92CB0F4940'::geography && (coordinates)::geography) AND (_st_distance('0103000020E6100000010000000500000098DD938785DA1040C7293A92CB0F494098DD938785DA1040C7293A92CB6F49407D3F355EBA891240C7293A92CB6F49407D3F355EBA891240C7293A92CB0F494098DD938785DA1040C7293A92CB0F4940'::geography, (coordinates)::geography, '0'::double precision, false) < '1e-05'::double precision))
 Planning time: 0.177 ms
 Execution time: 120.591 ms

Using WHERE (table.type = 'something' AND ST_MakeEnvelope(4.2134, 50.1234, 4.6345, 50.8734, 4326) && table.coordinates::geography) instead of ST_Intersects gives better performance. I'm not sure which is the better convention. Also the planner is using Seq Scan now instead of an Index Scan.

Plan:

Limit  (cost=4837.07..9674.14 rows=10000 width=44) (actual time=24.995..50.306 rows=10000 loops=1)
   ->  Seq Scan on table  (cost=0.00..2031628.90 rows=4200123 width=44) (actual time=0.027..49.091 rows=20000 loops=1)
         Filter: ((type = 'something'::text) AND ('0103000020E6100000010000000500000098DD938785DA1040C7293A92CB0F494098DD938785DA1040C7293A92CB6F49407D3F355EBA891240C7293A92CB6F49407D3F355EBA891240C7293A92CB0F494098DD938785DA1040C7293A92CB0F4940'::geography && (coordinates)::geography))
 Planning time: 0.117 ms
 Execution time: 50.687 ms
  • But for the order by the the program hast to sort all data within the envelope before it can apply the LIMIT. The index is of no help there. Postgres already uses a fast sorting meachanism but depending on the amount of objects within the envelope it will take some time. – Matte Mar 30 '17 at 17:53
  • You migth want to look at brin indexes, which work well for time-based search. A gist on the geometry plus brin on time is likely to work better than a combined gist column, I would think. However, I could be talking rubbish -- Brin indexes are fairly new and you would need to test. It is also possible that a Brin index using a time column and a geohash might help, as this essentially collapses the spatial part into 1D rather than 2D. – John Powell Mar 30 '17 at 18:24
  • 2
    It is also likely you will get a speed up using the gist index using ST_Intersects, because at the moment it is showing a bitmap index scan, rather than an index scan involving &&, which is what you would normally see for a spatial query. You coordinates are a geometry, rather than single points? – John Powell Mar 30 '17 at 18:31
  • Thanks for your replies! My coordinates are simple points (e.g. POINT(4.12123, 50.124213)) I'll have a look at brin indexes and ST_Intersects tomorrow – woudsma Mar 30 '17 at 18:53
  • For clarification: is type always 'something' or does this change with each query? Are the values in type equally distributed or not? How many distinct values are in the column type? If the type you are interested in is always 'something', you could try to utilize partial indexes, see for example here. Note, that PostgreSQL states to have some significant performance boosts. – yorkie Sep 21 '18 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.