I am constructing dynamic sql query with spatial filtering. It works, but I would like to speed it up - now it takes about 400msec for execute on ~ 5000 rows... I have created spatial indexes. My hypothesis are:

  1. remove double transformation in ST_Y, ST_X (calculates twice) but I have no idea to get rid of it;
  2. My regions are in fact may be located inside district (depends on user). So sometimes (when user choose the regions and the district that contains them) it again calculates twice - point in region and point in district - I tried to subselect it in with statement before main select but it only speed down the query.

My query is

        ST_Y(ST_Transform(geom, 4326)) as x, ST_X(ST_Transform(geom, 4326)) as y, 
        disable, enable, expected,
    FROM forms
    INNER JOIN LATERAL (select gid from region where (name = ANY(ARRAY['region1', 
                        'region2', 'region3', 'region4', 'region5']) or 
     district = ANY(ARRAY['district1', 'district2']))                           
                        and ST_Contains(geom, forms.geom)
                       ) foo on true    
        ST_Contains(ST_MakeEnvelope(25,51,28,61, 4326), st_transform(geom, 4326))
        r_type = ANY(ARRAY['type1', 'type2'])
    disable between '2021-05-01' and '2021-05-31';

Forms table is a point layer.

Explain analyse is

"Sort  (cost=5427.75..5427.75 rows=3 width=156) (actual time=4.035..4.036 rows=5 loops=1)"
"  Sort Method: quicksort  Memory: 26kB"
"  ->  Nested Loop  (cost=0.27..5427.72 rows=3 width=156) (actual time=0.993..4.022 rows=5 loops=1)"
"        ->  Seq Scan on region (cost=0.00..40.22 rows=17 width=7054) (actual time=0.044..0.293 rows=15 loops=1)"
"              Filter: (((name)::text = ANY ('{"region1", "region2", "region3", "region4", "region5"}'::text[])) OR ((district)::text = ANY ('{"district1", "district2"}'::text[])))"
"              Rows Removed by Filter: 96"
"        ->  Index Scan using forms_idx on forms (cost=0.27..308.08 rows=1 width=140) (actual time=0.157..0.170 rows=0 loops=15)"
"              Index Cond: (geom @ region.geom)"
"              Filter: ((disable >= '2021-05-01 00:00:00'::timestamp without time zone) AND (disable <= '2021-05-31 00:00:00'::timestamp without time zone) AND ((r_type)::text = ANY ('{type1, type2}'::text[])) AND st_contains(region.geom, geom) AND st_contains('0103000020E610000001000000050000000000000000003D400000000000804D400000000000003D400000000000804E400000000000003F400000000000804E400000000000003F400000000000804D400000000000003D400000000000804D40'::geometry, st_transform(geom, 4326)))"
"              Rows Removed by Filter: 19"
"Planning Time: 0.379 ms"
"Execution Time: 4.090 ms"

Maybe any ideas?

  • You've got a rather complex join running in under half a second (though the expalin asserts 4ms). How much time do you think reprojecting a point is taking?
    – Vince
    Jun 24, 2021 at 14:46
  • 3
    If all your queries are in 4326 why not store your geometries in 4326 to save all the transforming?
    – Ian Turton
    Jun 24, 2021 at 14:46
  • Is it correct to store data not in their own SRID? I mean that I insert data from provider in local SRID. But application uses 4326
    – Jane
    Jun 25, 2021 at 4:52
  • @Vince, reprojecting is the most expensive process in my case?
    – Jane
    Jun 25, 2021 at 4:54
  • A covering index does wonders, but if the only reason to retain the original projection is to retain the original projection, then you're just wasting time.
    – Vince
    Jun 25, 2021 at 4:58


Your Answer

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

Browse other questions tagged or ask your own question.