0

I have this rather basic query that is very slow for reasons I can't figure out:

SELECT s.id 
FROM segments s
WHERE
    ST_DWithin(
        s.geom::GEOGRAPHY,
        ST_Envelope((SELECT ST_COLLECT(s2.geom) FROM segments s2 WHERE s2.id IN (407820025,  407820024,  407817407,  407817408,  407816908,  407816909,  407817413,  407817414,  407817409,  407817410,  407817405,  407817406,  407816905,  407816907,  407817412,  407817411,  407816906,  407816904,  407816764,  407816765)))::GEOGRAPHY,
        30
    );

                                                                                                                           QUERY PLAN                                                                                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on segments s  (cost=55.58..48476381.06 rows=7444984 width=4)
   Filter: st_dwithin((geom)::geography, (st_astext(st_envelope($0)))::geography, '30'::double precision)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=55.57..55.58 rows=1 width=32)
           ->  Index Scan using segments_pkey on segments s2  (cost=0.44..55.52 rows=20 width=113)
                 Index Cond: (id = ANY ('{407820025,407820024,407817407,407817408,407816908,407816909,407817413,407817414,407817409,407817410,407817405,407817406,407816905,407816907,407817412,407817411,407816906,407816904,407816764,407816765}'::integer[]))

Where I'm really confused is that the ST_Envelope with the subquery is very fast by itself

SELECT ST_Envelope((SELECT ST_COLLECT(geom) FROM segments WHERE id IN (407820025,  407820024,  407817407,  407817408,  407816908,  407816909,  407817413,  407817414,  407817409,  407817410,  407817405,  407817406,  407816905,  407816907,  407817412,  407817411,  407816906,  407816904,  407816764,  407816765)))::GEOGRAPHY;

                                                                                                                           QUERY PLAN                                                                                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=55.58..55.60 rows=1 width=32)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=55.57..55.58 rows=1 width=32)
           ->  Index Scan using segments_pkey on segments  (cost=0.44..55.52 rows=20 width=113)
                 Index Cond: (id = ANY ('{407820025,407820024,407817407,407817408,407816908,407816909,407817413,407817414,407817409,407817410,407817405,407817406,407816905,407816907,407817412,407817411,407816906,407816904,407816764,407816765}'::integer[]))

And so is the main query if I plug the result of the ST_Envelope

SELECT id 
FROM segments
WHERE
    st_dwithin(
        geom::geography,
        '0103000020E61000000100000005000000C87B6E0D8FB85EC04BFD8462B9C34640C87B6E0D8FB85EC0929B35C16DC44640BBF8DDA6F2B75EC0929B35C16DC44640BBF8DDA6F2B75EC04BFD8462B9C34640C87B6E0D8FB85EC04BFD8462B9C34640'::GEOGRAPHY,
        30
    );

                                                                                                                                                                                                                                                                                QUERY PLAN                                                                                                                                                                                                                                                                                
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using segments_geom_geo_idx on segments  (cost=0.42..4.82 rows=1 width=4)
   Index Cond: ((geom)::geography && '0103000020E61000000100000005000000C87B6E0D8FB85EC04BFD8462B9C34640C87B6E0D8FB85EC0929B35C16DC44640BBF8DDA6F2B75EC0929B35C16DC44640BBF8DDA6F2B75EC04BFD8462B9C34640C87B6E0D8FB85EC04BFD8462B9C34640'::geography)
   Filter: (('0103000020E61000000100000005000000C87B6E0D8FB85EC04BFD8462B9C34640C87B6E0D8FB85EC0929B35C16DC44640BBF8DDA6F2B75EC0929B35C16DC44640BBF8DDA6F2B75EC04BFD8462B9C34640C87B6E0D8FB85EC04BFD8462B9C34640'::geography && _st_expand((geom)::geography, '30'::double precision)) AND _st_dwithin((geom)::geography, '0103000020E61000000100000005000000C87B6E0D8FB85EC04BFD8462B9C34640C87B6E0D8FB85EC0929B35C16DC44640BBF8DDA6F2B75EC0929B35C16DC44640BBF8DDA6F2B75EC04BFD8462B9C34640C87B6E0D8FB85EC04BFD8462B9C34640'::geography, '30'::double precision, true))

Shouldn't Postgres compute the ST_Envelope once and then use it for the WHERE condition, effectively doing what I did manually? I also don't get why no index is used to do the Filter in the original query.

I tried putting the subquery in a CTE but that didn't solve the issue.

2
  • What version of PostgreSQL and postgis are you using?
    – jjanes
    Jun 17, 2021 at 20:32
  • PostgreSQL 10.16 and PostGIS 2.4.9
    – Jukurrpa
    Jun 18, 2021 at 7:54

1 Answer 1

3

The reason is that with the constant geometry, the planner knows the value and estimates one result row, which makes an index scan a good strategy.

With the original query, the planner doesn't know the value, because it is only determined at execution time, so it guesses that there will be 7444984 result rows.

I would write two queries: one that calculates the geometry and one that uses the result as constant.

6
  • While that makes sense, I'm confused as to why the planner can't use the result of the subquery plan to optimize the main one. Is there any way to force it to use an index scan and avoid having two write two separate queries?
    – Jukurrpa
    Jun 18, 2021 at 8:12
  • Because it does not know the result value before query execution starts. You could try to SET LOCAL enable_seqscan = off; in the transaction that runs the query, but that is an ugly hack. Jun 18, 2021 at 8:22
  • What I'm confused about is why the planner can't plan in two steps, first planning the subquery, executing it, and then using the result to plan the main query. Basically doing the two separate queries method automatically. As for the workaround, it doesn't seem great indeed, I guess I'll just run two separate queries. Thank you for your help.
    – Jukurrpa
    Jun 18, 2021 at 8:48
  • 2
    Query planning and execution need not happen at the same time. For example, if you use a prepared statement, then it might be prepared now and executed an hour in the future. The data can have changed in that time. Essentially, planning and execution are two strictly separate steps that cannot interleave. Of course it would be conceivable that the optimizer plans certain alternatives and the executor decides which ones to use, but that is not done in PostgreSQL. It would also have a negative impact on query planning time. Jun 18, 2021 at 9:38
  • Makes sense, thank you for the explanations.
    – Jukurrpa
    Jun 18, 2021 at 14: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.