Query execution time

Question

I am new to SQL. I am trying to execute some queries on a database using PostgreSQL with the PostGIS extension. I cannot understand why the execution of the following query takes about 5 mins

select st_buffer(l.geom, 0.015), st_buffer(s.geom, 0.015) 
from "road" as l, "building" as s

while the execution of these queries

1) select st_buffer(l.geom, 0.015) from "road" as l

2) select st_buffer(s.geom, 0.015) from "building" as s

take about 300 ms for each query.

I have noticed that the execution of the query below takes a long time too.

select st_buffer(l.geom, 0.015)
from "road" as l, "building" as s

Moreover, I want to execute the ST_Intersection between the two buffer resulting from the queries. Something like this:

select st_intersection(st_buffer(l.geom, 0.015), st_buffer(s.geom, 0.015))
from "road" as l, "building" as s

What is the correct way to do that?

• Vince: You've requested a Cartesian product between the two tables. Of course it will take a long time. The first rule of table joins is to use the JOIN keyword, instead of adding tables to the FROM list; this will force you to consider how you want the table to be joined (instead of relying on the WHERE clause to untangle the mess) -- As a bonus, it makes it more difficult to forget the join rule in the WHERE. Your buffer units are so tiny, I have to wonder if you're using degrees as units, in which case, you should be using the geography type (casting if necessary)
• etrimaille: Did you compare the number of features in your queries? You will notice that it's not a addition of road and building.
• etrimaille: If you want the addition of building and road in a single query, maybe you want to do a UNION: SELECT ST_BUFFER(l.geom, 0.015) FROM road AS l UNION SELECT ST_BUFFER(s.geom, 0.015) FROM building AS s ?

Answer 1

The way you have it set up is repeating the process for all possible pairings of roads and buildings, i.e. a cartesian product. If you just want to get the buffers of the two tables into a single table then use UNION ALL

SELECT
 ST_Buffer(l.geom, 0.015)
FROM
 "road" as l
UNION ALL
SELECT
 ST_Buffer(s.geom, 0.015) 
FROM
 "building" as s

In response to your clarified question, the way to intersect it can be done like so:

SELECT
 ST_Intersection(rb.geom, bb.geom) AS geom
FROM
 (SELECT ST_Buffer(geom, 0.015) AS geom FROM "road") AS rb
 (SELECT ST_Buffer(geom, 0.015) AS geom FROM "building") AS bb

However I like to put the subqueries into common table expressions because I believe it improves readability, especially when the subqueries become more complex

WITH road_buffer AS (
 SELECT
 ST_Buffer(geom, 0.015) AS geom
 FROM
 "road"
),
building_buffer AS (
 SELECT
 ST_Buffer(geom, 0.015) AS geom
 FROM
 "building"
)
SELECT
 ST_Intersection(rb.geom, bb.geom)
FROM
 road_buffer AS rb,
 building_buffer as bb

• Ricla: In the case I want to execute an ST_Intersection between the result of the two queries, what is the right way?
• Vince: @Ricla Please Edit the question to specify that you want the output to be an intersection, and clarify if you want a buffer of the intersection point, or the intersection of the buffers.
• wfgeo: @Ricla I have updated the answer.