0

I have a query which creates an input to pgRouting pgr_drivingDistance function:

CREATE TEMP TABLE tmp_edge AS                   
SELECT 
    e."Id" as id,
    e."Source" as source,
    e."Target" as target,
    e."Length" / (1000*LEAST("Speed", "SpeedMin")/60) as cost
FROM    "Edge" e,
        "SpeedLimit" sl
WHERE   sl."VehicleKindId" = 1
        AND e.the_geom && 
            ST_MakeEnvelope(
                x1-(1000*GREATEST("Speed", "SpeedMax")/60)*13, 
                y1-(1000*GREATEST("Speed", "SpeedMax")/60)*13, 
                x1+(1000*GREATEST("Speed", "SpeedMax")/60)*13,
                y1+(1000*GREATEST("Speed", "SpeedMax")/60)*13, 3857)
        AND sl."RoadCategoryId" = e."CategoryId";

In the WHERE clause I calculate the same thing several times to get bounding box coordinates.

I tried to put calculations into FROM part and use alias for calculated column, but then whole execution time increases twice.

Edge table is quite large (1 milion) and SpeedLimit is several dozen record.

Is there any way to enhance this query?

1ac0
  • 2,875
  • 3
  • 33
  • 47
Marcin
  • 123
  • 4

1 Answers1

0

It is recommended way to join tables using JOIN syntax. And then later restrict given set wit WHERE. What is ST_MakeEnvelope? You can use Index on expression in PostgreSQL ;)
Expression indexes in PostgreSQL

Since you are using expressions you might benefit from them.
And you might use Explain analyize to notice your bottlenecks in the query

Mladen Uzelac
  • 1,173
  • 1
  • 10
  • 14
  • I read there's no performance gain when joining using `JOIN`. ST_MarkeEnvelope is PostGIS function to create rectange of specific extent. The columns used for joining are indexed. – Marcin Nov 10 '14 at 10:52
  • I was talking about expression indexes, since you have a few expressions in your query. `JOIN` adds to clarity of the query. :) – Mladen Uzelac Nov 10 '14 at 11:50