0

I have a small query using postgresql 9.6.11 and postgis 2.3.1+dfsg-2

select ST_Transform(ST_SetSRID(ST_Point(671699.9999988,6585300.5001232),32633),3006);

that is slower than I like for where it is used in my system

running explain analyse yields this

explain analyse select ST_Transform(ST_SetSRID(ST_Point(671699.9999988,6585300.5001232),32633),3006);
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)
 Planning time: 0.593 ms
 Execution time: 0.015 ms
(3 rows)

where I can see that the execution is dominated by the query planning time.

I have failed to find a way to reduce this (was trying to use a plpgsql function in the hopes it would cache the query plan) so what is a good way to optimise this?

I have found out that the slow part is the call to ST_Transform by running the following

explain analyse select ST_SetSRID(ST_Point(671699.9999988,6585300.5001232),32633);
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)
 Planning time: 0.074 ms
 Execution time: 0.018 ms
(3 rows)

But have failed to get the optimiser to treat ST_Transform any better.

  • I don't think there is anything you can do about the planning time especially with simple queries like that. Did you try a more recent version? E.g. Postgres 11? I would ask this on the performance mailing list maybe the devs can shed some light on that. – a_horse_with_no_name Apr 16 at 9:52
  • @a_horse_with_no_name I have not tried a newer version yet (I probably will) but I did not see anything in the change-log that looked relevant – lijat Apr 16 at 9:54

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.