2

I'm having trouble optimizing this query, sometimes it will take around 10s to execute. I have indexed the columns samples.geom and jbb2016.geom_3021 so that shouldn't be a problem. I know this is surely a common question, but I have not found a solution to this slow query in my searchings.

Here's some info about my PostGIS installation: POSTGIS="2.3.0 r15146"

PostgreSQL version: PostgreSQL 9.6.1 on x86_64-apple-darwin14.5.0

The samples table contains about 400k rows the geometry is points, the jbb2016 contains about 1.25m rows the geometry there is some kind of multipolygon.

SELECT 
year, customer_id, COUNT(customer_id) AS samples, 
(SELECT name || ' ' || last_name FROM customers WHERE id = customer_id) as name
FROM samples
WHERE 
ST_Intersects(
    (SELECT geom_3021 FROM jbb2016 WHERE blockid = 62023246713), samples.geom
)
GROUP BY year, customer_id 
ORDER BY year DESC;
1
  • It is a common question, though never a common problem (because every query is different). Every question here has always resulted in a comment requesting the query plan (generated by EXPLAIN). Just on the face of it, your query is hyperinefficient due to subquery use.
    – Vince
    Commented Mar 14, 2017 at 12:18

1 Answer 1

4

ST_Intersects might not use the indexes because of the subquery inside. Better to use JOIN

SELECT 
year, customer_id, COUNT(customer_id) AS samples, 
(SELECT name || ' ' || last_name FROM customers WHERE id = customer_id) as name
FROM samples
JOIN (SELECT geom_3021 FROM jbb2016 WHERE blockid = 62023246713) jbb2016
ON ST_Intersects(jbb2016.geom_3021, samples.geom)
GROUP BY year, customer_id 
ORDER BY year DESC;
1
  • The query you provided ran in 1.2s compared to 8.3s. Thank you, I will keep the possibility to a JOIN in mind.
    – Simtrax
    Commented Mar 14, 2017 at 12:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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