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 '21 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 '21 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 '21 at 4:52
  • @Vince, reprojecting is the most expensive process in my case?
    – Jane
    Jun 25 '21 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 '21 at 4:58

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.