current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 0 down vote favorite

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?

share|improve this question
1  
Where are x1, y1 coming from in this query. What does explain show? –  John Barça Nov 10 '14 at 16:20

1 Answer 1

up vote 0 down vote

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

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.